24

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');

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.txt or something similar first, or should it just get it from the present working directory?

nerdenator
  • 1,265
  • 2
  • 18
  • 35

5 Answers5

18

Maybe a bit late, but hopefully useful:

Use \copy instead

https://wiki.postgresql.org/wiki/COPY

jvdw

jvdw
  • 181
  • 1
  • 2
15

A couple of misconceptions:

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.

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

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.
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: just noise.

The default is a tab character in text format

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

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

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

COPY geonames FROM '/path/to/file/US.txt';
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 8
    The manual you linked for point 3. actually states that a relative path is ok for input files: `An input file name can be an absolute or relative path` http://www.postgresql.org/docs/current/interactive/sql-copy.html Though it really doesn't seem to work with relative paths. – Risadinha May 08 '14 at 11:42
6

The paths are relative to the PostgreSQL server, not the psql client.

Assuming you are running PostgreSQL 9.4, you can put US.txt in the directory /var/lib/postgresql/9.4/main/.

timo.rieber
  • 3,727
  • 3
  • 32
  • 47
Masten SG
  • 61
  • 1
  • 2
5

Another option is to pipe it in from stdin:

cat US.txt | psql -c "copy geonames from STDIN WITH (FORMAT csv);"
What Would Be Cool
  • 6,204
  • 5
  • 45
  • 42
  • Great. An old custom scripting mechanism stopped working (file not found) and this did the trick, MMD TY – Gunnar Jul 21 '21 at 14:36
2

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.

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
31 bit
  • 325
  • 1
  • 10