CODEWITHSUNDEEP
postgresqldatabase-performancepostgresql-10sql-tuning
Wandré Veloso
Wandré Veloso

Reputation: 61

Use COPY FROM command in PostgreSQL to insert in multiple tables

I'm trying to use the performance of COPY FROM command in PostgreSQL to get all data of 1 table of a CSV file (CSV -> table1) and I need to insert other data, but, in a new table. I will need of a primary key of first table to put as a foreign key in second table. Example: I need to insert 1,000,000 of names in table1 and 500,000 of names in table2, but, all names in table2 reference to 1 tuple in table1.

CREATE TABLE table1 (
   table1Id bigserial  NOT NULL,
   Name varchar(100)  NULL,
   CONSTRAINT table1Id PRIMARY KEY (table1Id)
);
CREATE TABLE table2 (
   table2Id bigserial  NOT NULL,
   Other_name varchar(100)  NOT NULL
   table1_table1Id int8  NOT NULL,
   CONSTRAINT table2_pk PRIMARY KEY (table2Id)
);

Upvotes: 3

Views: 2265

Answers (1)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2791

Command COPY does not allow table manipulations while copying data (such as look up to other table for fetching proper foreign keys to insert). To insert into table2 ids for corresponding rows from table1 you need to drop NOT NULL constraint for that field, COPY data and then UPDATE that fields separately.

Assuming table1 and table2 tables can be joined by table1.Name = table2.Other_name, the code is:

Before COPY:

ALTER TABLE table2 ALTER COLUMN table1_table1Id DROP NOT NULL;

After COPY:

UPDATE table2 SET table2.table1_table1Id = table1.table1Id
FROM table1
WHERE table1.Name = table2.Other_name;
ALTER TABLE table2 ALTER COLUMN table1_table1Id SET NOT NULL;

Upvotes: 1

Related Questions