Import CSV Data into Postgres DB with two tables which depend on each other-CodePudding

Hei,

i have a question about the best practice here. I have a Golang Project which uses as Postgres Database and specific Migrations. The Database has many tables and some depend on each other (Table A has FK to Table B, Table B has FK to Table A). My "problem" is now that i have to import data from CSV files, which i do with the COPY ... FROM ... WITH Command. Each CSV file contains the Data for a specific table.

If i try to use the copy command i get the error: "insert or update on table "b" violates foreign key constraint". Thats right, because in table a is no data right now. And cause of the FKs the problem happens on both sides.

So what is the best way to import the data?

Thanks :)

CodePudding user response：

Possible solution approach:

1.) create the table without the FK constraints

2.) load the data into tables

3.) add the FK constraints to tables with ALTER TABLE:

Example:

ALTER TABLE table_name
    ADD CONSTRAINT constraint_name constraint_definition;

CodePudding user response：

You can defer deferrable constraints until the end of a transaction:

create table a (id serial primary key, b_id bigint);
create table b (id serial primary key, a_id bigint references a(id) deferrable);
alter table a 
   add constraint fk_b_id foreign key (b_id) references b(id) deferrable;

begin transaction;
SET CONSTRAINTS ALL DEFERRED;
--your `COPY...FROM...WITH` goes here
insert into b values (1,1);--without deferring constraints it fails here
insert into a values (1,1);
commit;

Problem is, you have to make sure your foreign key constraints are deferrable in the first place - by default they are not, so set constraints all deferred; won't affect them.

You can do this dynamically for the time of your import (online demo):

CREATE TEMP TABLE queries_to_make_constraints_deferrable AS
SELECT format('alter table %I.%I alter constraint %I deferrable',
          v.schemaname, v.tablename, con.conname) as query
FROM pg_catalog.pg_constraint con
    INNER JOIN pg_catalog.pg_class rel     ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
    INNER JOIN (VALUES 
        ('public','table1'),--list your tables here
        ('public','some_other_table'),
        ('public','a'),
        ('public','b')) v(schemaname,tablename)
               ON nsp.nspname = v.schemaname AND rel.relname=v.tablename
WHERE con.contype='f'           --foreign keys
AND con.condeferrable is False; --non-deferrable

do $$
declare rec record;
begin 
  for rec in select query from queries_to_make_constraints_deferrable
  loop execute rec.query;
  end loop;
end $$ ;

Carry out your import in a transaction with deferred constraints, then undo your alterations by replacing deferrable with not deferrable:

begin transaction;
SET CONSTRAINTS ALL DEFERRED;
--your `COPY...FROM...WITH` goes here
insert into b values (1,1);--without deferring constraints it fails here
insert into a values (1,1);
commit;


do $$
declare rec record;
begin 
  for rec in select query from queries_to_make_constraints_deferrable
  loop execute replace(rec.query,'deferrable','not deferrable');
  end loop;
end $$ ;

As already stated, an alternative would be to set up your schema without these constraints and add them after importing the data. That might require you to find and separate them from their table definitions, which again calls for a similar dynamic sql.