Ok, I tried to be clever and I wrote code to avoid inserting duplicate data. The calling function has a try-catch to recover from this, but I am curious as to why it failed: INSERT INTO mdx_lib.acache_mdx_logic_address_validation ( address, postal_code, address_id ) SELECT '306 station 22 1 2 st' AS address, '29482' AS postal_code, 100165016 AS address_id WHERE NOT EXISTS ( SELECT 1 FROM mdx_lib.acache_mdx_logic_address_validation WHERE address = '306 station 22 1 2 st' AND postal_code = '29482' ) Exec status=PGRES_FATAL_ERROR error=ERROR: duplicate key value violates unique constraint "uq_acache_mdx_logic_address_validation_idx" DETAIL: Key (address, postal_code)=(306 station 22 1 2 st, 29482) already exists. The client insists that this process is the only one running, so if he’s right no other process could be inserting a row with the same data between the SELECT … NOT EXISTS and the actual INSERT operation. This particular code works as expected right now (SELECT returns 0 rows, therefore no rows INSERTed). Should this have worked? Carlo |
