Copy a few of the columns of a csv file into a table

Question

I have a CSV file with 10 columns. After creating a PostgreSQL table with 4 columns, I want to copy some of 10 columns into the table.

the columns of my CSV table are like:

x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 

the columns of my PostgreSQL table should be like:

x2 x5 x7 x10 

Answer 1

If it is an ad hoc task

Create a temporary table with all the columns in the input file

create temporary table t (x1 integer, ... , x10 text) 

Copy from the file into it:

copy t (x1, ... , x10) from '/path/to/my_file' with (format csv) 

Now insert into the definitive table from the temp:

insert into my_table (x2, x5, x7, x10) select x2, x5, x7, x10 from t 

And drop it:

drop table t 

If it is a frequent task

Use the file_fdw extension. As superuser:

create extension file_fdw;  create server my_csv foreign data wrapper file_fdw;  create foreign table my_csv (     x1 integer,     x2 text,     x3 text ) server my_csv options (filename '/tmp/my_csv.csv', format 'csv' ) ; 

Grant select permission on the table to the user who will read it:

grant select on table my_csv to the_read_user; 

Then whenever necessary read directly from the csv file as if it were a table:

insert into my_table (x2) select x2 from my_csv where x1 = 2