在PostgreSQL中，如何使用COPY命令插入数据? [英] In PostgreSQL, how to insert data with COPY command?

问题描述

在PostgreSQL数据库中运行1个项目NodeJ时遇到问题. 尝试使用COPY命令在pgAdmin中插入数据时出现错误.

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin;

Bons Voeux  blonde  9.5 Brasserie Dupont    250 130 generic.png

此数据在 要点:

此错误:

ERROR: syntax error at or near "Bons"
SQL state: 42601
Character: 1967

解决方案

COPY tbl FROM STDIN;

pgAdmin不支持

.
因为Postgres将数据作为SQL代码获取，所以您会收到一个普通的语法错误.

四个 可能的解决方案:

1..改为使用多行INSERT:

INSERT INTO beer(name, tags, alcohol, brewery, id, brewery_id, image)
VALUES 
  ('Bons Voeux', 'blonde', 9.5, 'Brasserie Dupont', 250, 130, 'generic.png')
, ('Boerke Blond', 'blonde', 6.8, 'Brouwerij Angerik', 233, 287 'generic.png')
;

请注意，不同的(SQL)语法用于字符串或数字文字.

您可以使用--inserts pg_dump生成数据>.相关:

• 从PostgreSQL表作为INSERT SQL脚本

2.或使用psql在命令行上调用脚本.作为系统用户postgres:

 psql -f beer.sql -U my_login_role -d db_name 
 

如果默认值可以，则可以省略

数据库(-d)和登录角色("User"为-U).语法示例:

• 创建Postgres数据库使用带有[模板]，[编码]，[所有者]和.sql文件的批处理文件

确保默认text格式具有数据结束标记(\.). (您已经知道了.)手册:

数据结尾可以用仅包含一行的一行来表示 反斜杠句点(\.).数据结束标记在以下情况下不是必需的 从文件中读取，因为文件末尾的效果非常好；它 仅在使用以下方式将数据复制到客户端应用程序或从客户端应用程序复制数据时才需要 3.0之前的客户端协议.

3.或将数据移动到服务器上的 的单独文件中，说"beer_data.csv"并在其中使用COPY .. FROM 'filename'您的脚本:

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image)
FROM '/path/to/beer_data.csv';

无论哪种方式工作.但是，您需要超级用户特权. 手册:

[...] COPY仅允许数据库超级用户命名文件或命令 或被授予默认角色之一的用户 pg_read_server_files，pg_write_server_files或 pg_execute_server_program，因为它允许读取或写入任何文件 或运行服务器有权访问的程序.

(pg_read_server_files，pg_write_server_files和pg_execute_server_program是Postgres 11中的新功能.)

4.或使用如何使用Postgres中CSV文件中的值更新所选行?

如何在带有pgadmin4的postgresql中使用\ copy

I have problem when run 1 project NodeJs with PostgreSQL database. I have error when trying to insert data in pgAdmin using the COPY command.

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin;

Bons Voeux  blonde  9.5 Brasserie Dupont    250 130 generic.png

This data in gist:

This error:

ERROR: syntax error at or near "Bons"
SQL state: 42601
Character: 1967

解决方案

COPY tbl FROM STDIN;

is not supported by pgAdmin.
You get a plain syntax error because Postgres gets the data as SQL code.

Four possible solutions:

1. Use a multi-row INSERT instead:

INSERT INTO beer(name, tags, alcohol, brewery, id, brewery_id, image)
VALUES 
  ('Bons Voeux', 'blonde', 9.5, 'Brasserie Dupont', 250, 130, 'generic.png')
, ('Boerke Blond', 'blonde', 6.8, 'Brouwerij Angerik', 233, 287 'generic.png')
;

Note the different (SQL) syntax for values as string or numeric literals.

You can generate the data with pg_dump using --inserts. Related:

• Export specific rows from a PostgreSQL table as INSERT SQL script

2. Or call your script on the command line using psql. As system user postgres:

psql -f beer.sql -U my_login_role -d db_name 

Database (-d) and login role (-U for "User") can be omitted if defaults are ok. Syntax examples:

• Create Postgres database using batch file with [template],[encoding],[owner] and a .sql file

Be sure there is an end-of-data marker (\.) for default text format. (You have that.) The manual:

End of data can be represented by a single line containing just backslash-period (\.). An end-of-data marker is not necessary when reading from a file, since the end of file serves perfectly well; it is needed only when copying data to or from client applications using pre-3.0 client protocol.

3. Or move your data to a separate file on the server, say 'beer_data.csv' and use COPY .. FROM 'filename' in your script:

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image)
FROM '/path/to/beer_data.csv';

Which works either way. You need superuser privileges, though. The manual:

[...] COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.

(pg_read_server_files, pg_write_server_files and pg_execute_server_program are new in Postgres 11.)

4. Or read a file local to the client with the psql meta-command \copy. See:

• How to update selected rows with values from a CSV file in Postgres?
• How to use \copy in postgresql with pgadmin4

这篇关于在PostgreSQL中，如何使用COPY命令插入数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！