Postgres inserts with ON CONSTRAINT DO UPSET, and FOR loops to avoid violating constraints
If you need to insert a record, and in case of conflict (e.g. UNIQUE key) you can solve it by updating a value, you can use the `ON CONSTRAINT constraint_name DO UPDATE SET column=<expression>`, see the documentation.
Note that this syntax
- does not allow to read a value from a subquery for <expression>
- does not allow to perform multiple times in case the constraint is violated more than once.
A more generic approach is running a pre-query to get the data you need, and avoid violating the constraint.
In case the query is part of a loop, you can use a Postgres FOR … IN inside a DECLARE.
Below you can see an example that iterates the records in a table, then runs an AGGREGATE query on another table, and uses its results as a parameter to perform an insert inside the record. This might be useful for example to create corresponding records into another table in case a INSERT INTO … SELECT FROM … cannot solve the problem with a single query.
DO $$ DECLARE
FOR yourRecord IN (SELECT * from yourTable) LOOP
SELECT MAX(col1)+1 INTO nextAvailableValue
WHERE col2 = yourRecord.id;
INSERT INTO other_table(
) VALUES (
You can run the above as a liquibase migration query as well.