postgresql Postgres 从 csv 文件复制 - 没有这样的文件或目录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16618299/
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
Postgres COPY FROM csv file- No such file or directory
提问by nerdenator
I'm trying to import a (rather large) .txt file into a table geonames in PostgreSQL 9.1. I'm in the /~ directory of my server, with a file named US.txt placed in that directory. I set the search_pathvariable to geochat, the name of the database I'm working in. I then enter this query:
我正在尝试将一个（相当大的）.txt 文件导入到 PostgreSQL 9.1 中的表 geonames 中。我在服务器的 /~ 目录中，在该目录中放置了一个名为 US.txt 的文件。我将search_path变量设置为 geochat，即我正在使用的数据库的名称。然后我输入以下查询：
COPY geonames
FROM 'US.txt',
DELIMITER E'\t',
NULL 'NULL');
I then receive this error:
然后我收到此错误：
ERROR: could not open file "US.txt" for reading: No such file or directory.
Do I have to type in \i US.txtor something similar first, or should it just get it from the present working directory?
我必须先输入\i US.txt或类似的东西，还是应该从当前的工作目录中获取它？
采纳答案by Erwin Brandstetter
A couple of misconceptions:
几个误解：
1.
1.
I'm in the /~ directory of my server
我在服务器的 /~ 目录中
There is no directory /~. It's either /(root directory) or ~(home directory of current user). It's also irrelevant to the problem.
没有目录/~。它是/（根目录）或~（当前用户的主目录）。它也与问题无关。
2.
2.
I set the search_path variable to geochat, the name of the database I'm working in
我将 search_path 变量设置为 geochat，我正在使用的数据库的名称
The search_pathhas nothing to do with the name of the database. It's for schemasinside the current database. You probably need to reset this.
在search_path没有任何与数据库的名称。它用于当前数据库中的模式。您可能需要重置它。
3.
You are required to use the absolute pathfor your file. As documented in the manual here:
3.
您需要为您的文件使用绝对路径。如此处的手册中所述：
filenameThe absolute path name of the input or output file.
filename输入或输出文件的绝对路径名。
4.
DELIMITER: just noise.
4.
分隔符：只是噪音。
The default is a tab character in text format
默认为文本格式的制表符
5.
NULL: It's rather uncommon to use the actual string 'NULL'for a NULLvalue. Are you sure?
5.
NULL：使用实际字符串'NULL'作为NULL值是相当罕见的。你确定吗？
The default is
\N(backslash-N) in text format, and an unquoted empty string in CSV format.
默认为
\N文本格式的 (backslash-N) 和 CSV 格式的未加引号的空字符串。
My guess (after resetting search_path- or you schema-qualify the table name):
我的猜测（重置后search_path- 或者您对表名进行架构限定）：
COPY geonames FROM '/path/to/file/US.txt';
回答by jvdw
Maybe a bit late, but hopefully useful:
也许有点晚，但希望有用：
Use \copy instead
使用 \copy 代替
https://wiki.postgresql.org/wiki/COPY
https://wiki.postgresql.org/wiki/COPY
jvdw
简书
回答by Masten SG
The paths are relative to the PostgreSQL server, not the psqlclient.
路径是相对于 PostgreSQL 服务器，而不是psql客户端。
Assuming you are running PostgreSQL 9.4, you can put US.txtin the directory /var/lib/postgresql/9.4/main/.
假设你正在运行的PostgreSQL 9.4，你可以把US.txt目录/var/lib/postgresql/9.4/main/。
回答by bodman
if you're running your COPY command from a script, you can have a step in the script that creates the COPY command with the correct absolute path.
如果您从脚本运行 COPY 命令，则可以在脚本中设置一个步骤，使用正确的绝对路径创建 COPY 命令。
MYPWD=$(pwd)
echo "COPY geonames FROM '$MYPWD/US.txt', DELIMITER E'\t';"
MYPWD=
you can then run this portion into a file and execute it
然后您可以将此部分运行到一个文件中并执行它
./step_to_create_COPY_with_abs_path.sh >COPY_abs_path.sql
psql -f COPY_abs_path.sql -d your_db_name
回答by What Would Be Cool
Another option is to pipe it in from stdin:
另一种选择是从标准输入管道输入：
cat US.txt | psql -c "copy geonames from STDIN WITH (FORMAT csv);"
