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 Postgres construct ON CONSTRAINT constraint_name DO UPDATE SET column=<expression>

See the documentation.

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
    yourRecord RECORD;
    nextAvailableValue bigint;
BEGIN
    FOR yourRecord IN (SELECT * from yourTable) LOOP
        SELECT MAX(col1)+1 INTO nextAvailableValue
            FROM table_with_values
            WHERE col2 = yourRecord.id;

        INSERT INTO other_table(
            id,
            col3
        ) VALUES (
           yourRecord.id,
           nextAvailableValue
        );
    END LOOP;
END $$;

You can run the above as a Liquibase migration query as well.

Note that this syntax

• does not allow reading a value from a subquery inside <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.