Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
692 views
in Technique[技术] by (71.8m points)

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

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...