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

Question

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 :)

Bad data model, chicken or egg problem, what came first? Fix your model — Frank Heikens, Nov 15 '22 at 11:04
Actually its not from me. This one was created and delivered by an external company. — J. Archer, Nov 15 '22 at 11:11
You need to create the tables without FK, load the data, and create at the end the FK (alter table ...) — Adelino Silva, Nov 15 '22 at 11:26

score 0 · Accepted Answer · answered Nov 15 '22 at 11:34

0

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;

answered Nov 15 '22 at 11:34

Adelino Silva

577
3
16

Thanks i will do. But its still bad database design right? – J. Archer Nov 15 '22 at 12:22
Having foreing keys is usually a good database design, you'd be surprised how many production databases go completely without any foreign keys, which leads to all kinds of problems. And if it's the initial load of the database (i.e. all/most tables are empty) then it's fine afaik a good way to do it. If there is already a lot of data and you're adding to the database, then I probably would do it using some ETL tool and in correct order. Meaning first the main tables, then the tables depending on those first tables etc. – BdR Nov 15 '22 at 12:50
Sure are Foreign Keys a great thing. Thats not what i meant. I meant its a bad Database Design to create dependencies between two tables with foreign keys. A depends on B, B depends on A. – J. Archer Nov 15 '22 at 12:59
It's called a [circular/cyclic reference/foreign key](https://stackoverflow.com/q/1006917/5298879). It's a pretty polarising topic – Zegarek Nov 15 '22 at 13:04

Zegarek · Answer 2 · 2022-11-15T13:16:06.453

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.

Anyway thanks for the reply. I will use the alternative cause i must not change / modify this great structure. — J. Archer, Nov 15 '22 at 12:26
@J.Archer If the structure is big enough, this method might actually be easier. I was playing around trying to do this dynamically and succeeded - see the updated answer. With the alternative you're still changing/modifying the structure these constraints are an integral part of. It also doesn't answer how to do it dynamically - in most cases you'd have to manually pick out, remove from ddl/drop from db, then redefine them. — Zegarek, Nov 15 '22 at 13:00

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

2 Answers2