在PostgreSQL中，如何使用COPY命令插入数据？

Question

我在运行一个使用PostgreSQL数据库的NodeJs项目时遇到了问题。

我尝试使用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

这些数据在gist中：

这个错误：

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

- user4646310

---

这种错误取决于您正在使用的数据库框架类型。 您应在问题中包含此详细信息。 - vitaly-t

@vitaly-t，我更新了gif文件的方法，创建了一个数据库并执行了beers.sql文件，但是出现了错误。 - user4646310

你似乎将问题标签错误地打上了“node.js”和“javascript”的标签，并不与你的问题相关。这就是我要求澄清的原因。 - vitaly-t

可能是重复的问题重复，或者您可以像这里所示的那样使用psql恢复包含从stdin复制的纯文本备份。链接 - Houari

动画GIF证明非常有用。 - Erwin Brandstetter

Answer 1

COPY tbl FROM <b>STDIN</b>;

不支持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) 语法。

你可以使用 pg_dump 和 --inserts 生成数据。请参考：

• 将特定行从 PostgreSQL 表导出为 INSERT SQL 脚本

2. 使用特权系统用户 psql

以系统用户 postgres 的身份，在命令行上调用你的脚本：psql。

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

数据库（-d）和登录角色（-U用于“用户”）如果默认值可以的话可以省略。语法示例：

• 使用批处理文件创建Postgres数据库，包括[模板]、[编码]、[所有者]和一个.sql文件

请确保对于默认的文本格式有一个数据结束标记（\。）。（你已经有了这个。）手册：

数据的结束可以由一行只包含反斜杠句点（\。）的单行表示。当从文件中读取时，不需要数据结束标记，因为文件的结尾完全可以胜任；只有在使用早于3.0版本的客户端协议复制数据到或从客户端应用程序时才需要。

3. 在具有特权的数据库角色上在数据库服务器上执行COPY操作

将您的数据移动到服务器上的一个单独文件中，例如'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. 在任何客户端上使用\copy

使用psql元命令\copy读取客户端本地文件。参见：

• 如何使用CSV文件中的值更新Postgres中的选定行？
• 如何在pgadmin4中使用\copy命令进行postgresql操作？

- Erwin Brandstetter

---

"pg_dump --inserts <database> > dump.sql" 对我来说很有用，谢谢！ - Ortwin Angermeier

使用命令 "psql beer -f beer.sql" 时，控制台会询问我：用户 -f 的密码是什么？我不理解... - Julien Baldy

@JulienBaldy：抱歉，我修复了psql调用。 - Erwin Brandstetter

第二步，成功了！ - Paras Patidar

Answer 2

第一步是在pgAdmin中创建名为belgianbeers的数据库。

然后打开命令提示符并运行psql -U postgres -d belgianbeers -a -f beers.sql

此命令行运行将更新数据库表。

注意：-U postgres = 指定用户名为postgres

- Claudio

Answer 3

我的决定：将SQL文件放在根目录，例如C:\。

例如，我的数据库名为cities，SQL文件名为cidade.sql。

在PostgreSQL目录中的BIN文件夹内打开cmd并键入：

psql -U postgres -d cities < C:\cidade.sql

psql 的参数如下：

• -U [user]：指定用户名。
• -d [database]：指定数据库名称。

你需要输入密码。请注意检查 psql 是否在环境变量 %PATH% 中。

- PatrickJD

---

感谢您的纠正，这是我第一次回答问题，不知道如何处理格式。 - PatrickJD