3

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)
);
Wandré Veloso
  • 61
  • 2
  • 8

1 Answers1

1

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;
Ilya Konyukhov
  • 2,666
  • 1
  • 12
  • 21
  • Hi @Ilya Konyukhov, thanks for the answer. You give me an ideia (create this field with the same value in this two tables and after update). Thanks a lot! – Wandré Veloso Oct 13 '18 at 07:58