更新时间:2023-01-30 20:40:02
创建一个包含输入文件中所有列的临时表
Create a temporary table with all the columns in the input file
create temporary table t (x1 integer, ... , x10 text)
从文件复制到
copy t (x1, ... , x10)
from '/path/to/my_file'
with (format csv)
现在从temp插入到确定表中:
Now insert into the definitive table from the temp:
insert into my_table (x2, x5, x7, x10)
select x2, x5, x7, x10
from t
并将其删除:
drop table t
使用 file_fdw
extension 。作为超级用户:
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;
然后随时从csv文件中直接读取,就像它是一个表:
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