Postgres COPY FROM csv file-没有这样的文件或目录 [英] Postgres COPY FROM csv file- No such file or directory

问题描述

我正在尝试将一个（相当大的）.txt文件导入PostgreSQL 9.1中的表格地名。我在我的服务器的/〜目录中，在该目录中放置了一个名为US.txt的文件。我将 search_path 变量设置为geochat，即我正在使用的数据库的名称。然后我输入此查询：

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_path variable to geochat, the name of the database I'm working in. I then enter this query:

COPY geonames
FROM 'US.txt',
DELIMITER E'\t',
NULL 'NULL');

然后我收到此错误：

ERROR: could not open file "US.txt" for reading: No such file or directory.

我是否必须输入 \ i US.txt 或类似的东西，或者它应该从目前的工作目录中得到它？

Do I have to type in \i US.txt or something similar first, or should it just get it from the present working directory?

推荐答案

一些误解：

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.

我将search_path变量设置为geochat，我正在使用的数据库名称

I set the search_path variable to geochat, the name of the database I'm working in

search_path 与数据库名称无关。它适用于当前数据库中的 schemas 。你可能需要重置它。

The search_path has nothing to do with the name of the database. It's for schemas inside the current database. You probably need to reset this.

3.

你需要使用绝对路径为您的文件。正如在此手册中所述：

3.
You are required to use the absolute path for your file. As documented in the manual here:

filename

输入或输出文件的绝对路径名。

The absolute path name of the input or output file.

4。

DELIMITER：只是噪音。

4.
DELIMITER: just noise.

默认为文本格式的制表符

The default is a tab character in text format

5.

NULL：使用实际字符串相当罕见'NULL'表示 NULL 值。你确定吗？

5.
NULL: It's rather uncommon to use the actual string 'NULL' for a NULL value. Are you sure?

默认值是 \ N （反斜杠-N）文本格式，以及CSV格式的未加引号的空字符串。

The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format.

我的猜测（重置 search_path后 - 或者您对表名进行模式限定）：

My guess (after resetting search_path - or you schema-qualify the table name):

COPY geonames FROM '/path/to/file/US.txt';

这篇关于Postgres COPY FROM csv file-没有这样的文件或目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！