PostgreSQL input bulk data While Skipping Relation Error

Question

Im doing a bulk insert to PostgreSQL using PG_Admin tools, the table field anamnesa_id contain ForeignKey relation to other table pasien_anamnesa,

Is there a way to ignore or skip (which is not exist in other table) the error while inserting all the query? because deleting the error query 1 by 1 is just impossible for this many data (25.000 records),

i've trying:

INSERT INTO "pasien_item" ("id", "anamnesa_id") VALUES (1, 2) ON CONFLICT ON CONSTRAINT pasien_item_pkey DO NOTHING;

resulting error:

ERROR:  insert or update on table "pasien_item" violates foreign key constraint "pasien_item_anamnesa_id_dc66b31b_fk_pasien_anamnesa_id"
DETAIL:  Key (anamnesa_id)=(2) is not present in table "pasien_anamnesa".
SQL state: 23503

from that error i also tried:

INSERT INTO "pasien_item" ("id", "anamnesa_id") VALUES (1, 2) ON CONFLICT ON CONSTRAINT pasien_item_anamnesa_id_dc66b31b_fk_pasien_anamnesa_id DO NOTHING;

resulting error:

ERROR:  constraint in ON CONFLICT clause has no associated index
SQL state: 42809

score 1 · Accepted Answer · 2020-11-17 07:43:07Z

1

ON CONFLICT can only deal with unique constraints, not foreign key or check constraints.

You need to rewrite your query to use a SELECT that only returns the rows where the foreign keys exist:

INSERT INTO pasien_item(id, anamnesa_id) 
select v.id, v.anamnesa_id
from (
   VALUES (1, 2), ...
) v(id, anamnesa_id) 
WHERE EXISTS (select *
              from pasien_anamnesa pa
              where pa.anamnesa_id = v.anamnesa_id)
ON CONFLICT ON CONSTRAINT pasien_item_pkey DO NOTHING;

Online example

edited Nov 17, 2020 at 7:43

answered Nov 17, 2020 at 7:03

user330315

Sign up to request clarification or add additional context in comments.

9 Comments

Diand Over a year ago

ERROR:  syntax error at or near "v" LINE 2: .1, 2) v(id, anam...                        ^ SQL state: 42601 Character: 315

user330315 Over a year ago

Well, obviously you need to replace the ... part with the other rows you have

Diand Over a year ago

yeah i already did:

INSERT INTO "pasien_item" ("id", "anamnesa_id", "DIAGNOSA") select v.id, v.anamnesa_id, v.DIAGNOSA from (VALUES (38410, 11732, 'OK') v(id, anamnesa_id, DIAGNOSA) WHERE NOT EXISTS (select * from pasien_anamnesa pa where pa.anamnesa_id = v.anamnesa_id) ON CONFLICT ON CONSTRAINT pasien_item_pkey DO NOTHING;

user330315 Over a year ago

It should have been EXISTS not NOT EXISTS but the syntax works: dbfiddle.uk/…

Diand Over a year ago

ahh, i miss one closing bracket, now it worked! thanks man!

|

Collectives™ on Stack Overflow

PostgreSQL input bulk data While Skipping Relation Error

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related