使用 STDIN 的 PostgreSQL 复制命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48294443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL copy command using STDIN
提问by Rajesh
I need to load the data from the CSV file to table in PostgreSQL. and I'm not a superuser to use the copy command. when i read few topics from the postgreSQL site I came to know abut the \copycommand using STDINand STDOUT.
我需要将数据从 CSV 文件加载到 PostgreSQL 中的表中。而且我不是使用复制命令的超级用户。当我从 postgreSQL 站点阅读一些主题时，我开始了解\copy使用STDINand的命令STDOUT。
I have tried with the same but getting errors. what actually I was trying is I have CSV file located in 'D:/test/test.csv' trying to load in tablename:test by using the below copy command
我已经尝试过相同但出现错误。实际上我在尝试的是我在“D:/test/test.csv”中有一个 CSV 文件，试图通过使用下面的复制命令加载 tablename:test
command: \copy test from stdin.
命令： \copy test from stdin.
what is exactly STDIN and where I have to assign the file path
And one more doubt do I need to run this command only in psql or i can run this in SQL workbench.
究竟什么是 STDIN 以及我必须在哪里分配文件路径
还有一个疑问，我是否只需要在 psql 中运行此命令，或者我可以在 SQL 工作台中运行它。
回答by Vao Tsun
1) stdin is standard input - means you have to paste (or type) the data
1) stdin 是标准输入 - 意味着您必须粘贴（或键入）数据
2) yes \copyis psqlmeta-command,not SQL, thus can be executed in psql only...
2) yes\copy是psql元命令，而不是 SQL，因此只能在 psql 中执行...
Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.
执行前端（客户端）复制。这是一个运行 SQL COPY 命令的操作，但不是服务器读取或写入指定的文件，而是 psql 读取或写入文件并在服务器和本地文件系统之间路由数据。这意味着文件可访问性和权限是本地用户的，而不是服务器的，并且不需要 SQL 超级用户权限。
also - you don't have to run from stdin, below should work as well:
也 - 你不必运行from stdin，下面也应该工作：
\copy test from 'D:/test/test.csv'
回答by Mabu Kloesen
The command COPY is useful for you when you bulk loading a large structured data into database. With my experience we have some thing be noticed here.
当您将大型结构化数据批量加载到数据库中时，命令 COPY 对您很有用。根据我的经验，我们在这里需要注意一些事情。
STDIN used when your command is after pipeline of other commands such as:
CAT xyz.csv | psql -U postgres -c "COPY test FROM STDIN"This command, as I know only run in commandline.You can load data from file with the syntax
COPY test FROM '/tmp/xyz.csv'This command can be run in both psql and pgAdmin, but please attention that the path to the file must be on the server or somewhere that server can reach, and also the privilege of the system user (which run database daemon) can access and read the file.
当您的命令在其他命令的管道之后使用时使用 STDIN，例如：
CAT xyz.csv | psql -U postgres -c "COPY test FROM STDIN"此命令，据我所知仅在命令行中运行。您可以使用以下语法从文件中加载数据
COPY test FROM '/tmp/xyz.csv'此命令可以在 psql 和 pgAdmin 中运行，但请注意文件的路径必须在服务器上或服务器可以到达的地方，以及系统用户的权限（运行数据库守护程序）可以访问和读取文件。
You can find more information here.
您可以在此处找到更多信息。
Hopefully this answer will help you.
希望这个答案能帮到你。
