0

I'm expert at mssql but a beginner with PostgreSQL so all my assumptions are likely wrong.

Boss is asking me to get a 300 MB CSV file into PostgreSQL 12 (1/4 million rows and 100+ columns). The file has usernames in 20 foreign key columns that would need to be looked up and converted to id int values before getting inserted into a table. The COPY command doesn't seem to handle joining a csv to other tables before inserting. Am I going in a wrong direction? I want to test locally but ultimately am only allowed to give the CSV to a DBA for importing into a docker instance on a server. If only I could use pgAdmin and directly insert the rows!

btalada
  • 1
  • 2
    You will need to first import that into an intermediate (aka "staging") table, then use `insert into real_table ... select ... from staging_table join other_table on ...` to resolve the foreign keys –  Feb 17 '20 at 17:38
  • Thanks. The COPY into a staging table, then joining to foreign key tables for an insert/select solved my immediate problem. Since then I have switched to python and xmlrpc as a good way to move data to and from PostgreSQL servers. – btalada Jun 22 '20 at 15:11

0 Answers0