I don't want to synchronize A with B all the time, only upon request. Yes, if it is only in certain cases then inheritance would not work and in that case I advise against dynamic query approach.(still there are ways to achieve this using postgres procedural languages.also if you want to use triggers you can use them as well - by adding sync field for example firing trigger only when it is set). I thought I could make my query compliant with later changes to both tables, but dynamic sql seems to be too complicated than just specify all fields and forget about forward compatibility.yes, it will be complicated, but should be forward compatible with later columns being added or removed.You'll have to first do a query to get the column names from both tables, then match the column names and then write the dynamic sql to do the update based on the matching column names.That is at most one (if the number one is not in the result of the sub-select), otherwise zero. WITH new_values (id, field1, field2) as ( values (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ), upsert as ( update mytable m set field1 = nv.field1, field2 = nv.field2 FROM new_values nv WHERE = RETURNING m.* ) INSERT INTO mytable (id, field1, field2) SELECT id, field1, field2 FROM new_values WHERE NOT EXISTS (SELECT 1 FROM upsert up WHERE = new_values.id) @a_horse_with_no_name How do you exactly mean that the chance on race conditions is much smaller? What I meant with "much smaller" is that if several transactions to this (and commit the change! That's pretty much inherent to an upsert operation.When I execute this query concurrently with the same records I'm getting the error "duplicate key value violates unique constraint" 100% of the times until the query detects that the record has been inserted. ) the time span between the update and the insert is smaller as everything is just a single statement. You have to evaluate the function that generates the sequence before attempting the insert.I've now switched over to Postgre SQL and apparently this is not correct.
Just open the app, and select “Check For Updates…” from the “Postgres” menu.The INSERT will succeed only if row with "id=3" does not already exist.You can combine these two into a single string and run them both with a single SQL statement execute from your application.Running them together in a single transaction is highly recommended.This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently.
Search for updating postgresql:
You can always generate a pk violation by two independent INSERT statements. Since such sequences are designed to operate concurrently they're exempt from normal transaction semantics, but even if they weren't the generation is not called in a subtransaction and rolled back, it completes normally and commits with the rest of the operation. But if you're relying on serial / auto_increment being gapless you've already got bugs.