将一个 csv 文件的一些列复制到一个表中

小开

如果是临时任务

创建一个包含输入文件中所有列的临时表

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

将文件复制到其中:

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

现在，从临时表插入权威表:

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

放弃吧:

drop table t

如果这是一个频繁的任务

使用 file_fdw分机作为超级用户:

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 on table my_csv to the_read_user;

然后在必要的时候直接读取 csv 文件，就像读取一个表一样:

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

小开

将数据从电子表格(Excel 或 OpenOfficeCalc)加载到 postgreSQL:

将电子表格页面保存为 CSV 文件。首选的方法是在 OpenOfficeCalc 上打开电子表格并进行保存。在“导出到文本文件”窗口中选择“字符集为 Unicode (UTF8)”、“字段分隔符”和“文本分隔符”。将显示消息，说明只保存活动工作表。注意: 此文件必须保存在文件夹中，但不能保存在桌面上，并且必须以 UTF8格式保存(postgreSQL 默认为 UTF8编码)。如果保存在桌面上，postgreSQL 将给出“访问拒绝”消息，不会上传。

在 PostgreSQL 中，创建一个列数与电子表格相同的空表。

注意: 在每个列上，列名必须相同，数据类型必须相同。此外，请记住数据的长度，其中的字符随着足够的字段而变化。

然后在 postgreSQL 的 SQL 窗口中，输入以下代码:

从 E‘ C 复制“ ABC”。“ def”: tmp blabla.CSV’分隔符’，CSV HEADER;

注意: 这里 C: tmp 是保存 CSV 文件“ blabla”的文件夹。“ ABC”“ def”是在 postgreSQL 上创建的表，其中“ ABC”是 schema，“ def”是实际的表。然后按顶部的绿色按钮执行查询。当 CSV 表在每列的开头都有标题时，需要使用“ CSV HEADER”。

如果一切正常，将不会显示任何错误消息，并且 CSV 文件中的表数据将被加载到 postgreSQL 表中。但如果出现错误消息，请按以下步骤操作:

如果错误消息指出数据对于特定列来说太长，则增加列大小。这主要发生在字符和字符变化列上。然后再次运行“执行查询”命令。

如果错误消息显示数据类型与特定列不匹配，则更改 postgreSQL 表-column 上的数据类型以匹配 CSV 表中的数据类型。

在您的示例中，在创建 CSV 文件之后，删除不需要的列并与 postgre 表中的列匹配。

小开

您可以使用 COPY命令提供要填充的列，如下所示:

\copy your_table (x2,x5,x7,x10) FROM '/path/to/your-file.csv' DELIMITER ',' CSV;

下面是 COPY命令的 doc 。

小开

刚刚来到这里，寻找一个解决方案，只加载列的子集，但显然这是不可能的。因此，使用 awk (或 cut)将所需的列提取到一个新文件 new_file:

$ awk '{print $2, $5, $7, $10}' file > new_file

然后加载 new_file。你可以通过管道将输出直接传输到 psql:

$ cut -d \  -f 2,5,7,10 file |
psql -h host -U user -c "COPY table(col1,col2,col3,col4) FROM STDIN DELIMITER ' '" database

注意 COPY，而不是 \COPY。

更新:

正如注释中指出的那样，上述两个示例都不能处理数据中的引号分隔符。换行也是如此，因为 awk 或 cut不能识别 CSV。但是，可以使用 GNU awk 处理引用的分隔符。

这是一个三栏的文件:

$ cat file
1,"2,3",4

使用 GNU awk 的 FPAT变量，我们可以改变字段的顺序(或者得到它们的子集) ，即使引用的字段中有字段分隔符:

$ gawk 'BEGIN{FPAT="([^,]*)|(\"[^\"]+\")";OFS=","}{print $2,$1,$3}' file
"2,3",1,4

解释:

$ gawk '
BEGIN {                          # instead of field separator FS
FPAT="([^,]*)|(\"[^\"]+\")"  # ...  we define field pattern FPAT
OFS=","                      # output field separator OFS
}
{
print $2,$1,$3               # change field order
# print $2                   # or get a subset of fields
}' file

请注意，FPAT只是 GNU awk，对于其他 awk，它只是一个常规变量。

小开

你可以进一步采纳詹姆斯•布朗(James Brown)的建议，一句话说完:

$ awk -F ',' '{print $2","$5","$7","$10}' file | psql -d db -c "\copy MyTable from STDIN csv header"

小开

正如其他答案所指出的，可以指定要复制到 PG 表中的列。但是，如果没有在 CSV 中引用列名的选项，那么除了将列加载到具有不同顺序的表中之外，这种方法几乎没有什么实用性。

幸运的是，从 Postgres 9.3开始，不仅可以从文件或标准输入复制列，还可以使用 PROGRAM 从 shell 命令复制列:

程序

要执行的命令。在 COPYFROM 中，输入从命令的标准输出中读取，在 COPYTO 中，输出写入到命令的标准输入中。

请注意，该命令是由 shell 调用的，因此如果需要向来自不受信任源的 shell 命令传递任何参数，则必须小心地去掉或转义可能对 shell 具有特殊含义的任何特殊字符。出于安全原因，最好使用固定的命令字符串，或者至少避免在其中传递任何用户输入。

这正是我们期待已久的功能所需要的缺失部分。例如，我们可以将此选项与 cut(在基于 UNIX 的系统中)结合使用，按顺序选择某些列:

COPY my_table (x2, x5, x7, x10) FROM PROGRAM 'cut -d "," -f 2,5,7,10 /path/to/file.csv' WITH (FORMAT CSV, HEADER)

但是，cut在操作 CSV 时有几个限制: 它不能充分地操作字符串中包含逗号(或其他分隔符)的字符串，并且不允许按名称选择列。

还有一些其他的开放源码命令行工具可以更好地处理 CSV 文件，比如 < a href = “ https://github.com/wireserve/csvkit”rel = “ norefrer”> csvkit 或 译自: 美国《每日邮报》网站(https://github.com/johnkerl/miller)。下面是一个使用 miller按名称选择列的示例:

COPY my_table (x2, x5, x7, x10) FROM PROGRAM 'mlr --csv lf cut -f x2,x5,x7,x10 /path/to/file.csv' WITH (FORMAT CSV, HEADER)

小开

如果因此导入的行数对您来说并不重要，您还可以:

创建两个表:

T1(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10) : 包含 csv 文件的所有列
T2(x2 x5 x7 x10) : 根据需要

然后创造:

一个触发器函数，将所需的列插入到 t2中，然后返回 NULL，以防止在 t1中插入此行
调用此函数的 t1(每行前插入)触发器。

特别是对于较大的 csv 文件 BEFORE INSERT 触发器，也可以预先筛选出具有某些属性的行，还可以进行类型转换。

小开

将表复制到本地目录的一种快速方法是:

\copy (select * from table_name) to 'data.csv' CSV;