0

I am trying to insert a new record into a table, but only if another record of the same table meets some conditions. Sadly it does not work I alwys get the following error: ERROR: duplicate key value violates unique constraint "stackacos_pkey" Even though the id is not yet present in the db. Any idea how to avoid this issue ?

INSERT INTO stackacos (id, created_at, updated_at, owner_id, stack_id, admin_full, admin_create, admin_read, admin_update, admin_delete, "create", read, update, delete) 
SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57', 'test_user_222', 'KZROLZYQbSeHbMtZNFCpMlaKJyfaKFMdcbwGklCiUQBuIiZSptBSlVcuUGofnjMM', 'false', 'false', 'false', 'false', 'false', 'false', 'true', 'false', 'false' 
FROM stackacos 
WHERE stackacos.owner_id = 'test_user_1' AND
    stackacos.admin_full = true OR (
        stackacos.admin_create >= 'false' AND stackacos.admin_create >= 'false' AND
        stackacos.admin_read >= 'false' AND stackacos.admin_read >= 'true' AND
        stackacos.admin_update >= 'false' AND stackacos.admin_update >= 'false' AND
        stackacos.admin_delete >= 'false' AND stackacos.admin_delete >= 'false'
        ) RETURNING *
  • 1
    If the ID is truly a hard-coded value, have you tried running the SELECT statement by itself, to ensure that it only returns 1 row? If it returns multiple rows, it would be trying to insert multiple rows - all of which woul dhave the same ID. – RDFozz Sep 18 '18 at 15:25
  • Yeah it only returns 1 row – jjuser19jj Sep 18 '18 at 17:21
  • Postgres uses the name of constraints as ${table_name}_${column_name}_${constraint_name}. But there you have custom name. So can you SELECT pg_get_constraintdef(c.oid) FROM pg_constraint c WHERE conname = 'stackacos_pkey'? – Talgat Saribayev Sep 18 '18 at 23:45
0

use nextvalue(idstackacos_find_the_right_sequence_name ) instead of

OLD:

SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57'

NEW:

SELECT nextvalue(idstackacos_find_the_right_sequence_name) , '2018-09-18 14:34:57', '2018-09-18 14:34:57'

If it's not working please copy here the definition of the table and of the sequence.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.