将csv文件的几个列复制到表中 [英] Copy a few of the columns of a csv file into a table
问题描述
我有一个包含10列的CSV档案。在创建了一个包含4列的PostgreSQL表之后,我想将10列中的某些列复制到表中。
我的CSV表的列如下:
x1 x2 x3 x4 x5 x6 x7 x8 x9 x10
我的PostgreSQL表的列应该是:
x2 x5 x7 x10
如果是特别任务
创建一个包含输入文件中所有列的临时表
创建临时表t ,...,x10 text)
从文件复制到
copy t(x1,...,x10)
从'/ path / to / my_file'
with(format csv )
现在从temp插入到确定表中:
插入my_table(x2,x5,x7,x10)
选择x2,x5,x7,x10
从t
并将其删除:
drop table t
如果是频繁的任务
使用 file_fdw
扩展程序。作为超级用户:
创建扩展file_fdw;
创建服务器my_csv外部数据包装器file_fdw;
创建外表my_csv(
x1整数,
x2文本,
x3文本
)服务器my_csv
选项(filename'/ tmp /my_csv.csv',format'csv')
;
向表格授予对其进行读取的用户的选择权限:
grant select on table my_csv to the_read_user;
然后在必要时直接从csv文件中读取,就像它是一个表:
插入到my_table(x2)
select x2
从my_csv
其中x1 = 2
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
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
这篇关于将csv文件的几个列复制到表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!