CODEWITHSUNDEEP
postgresqlpostgresql-copy
vchitta
vchitta

Reputation: 2143

PostgreSQL's COPY statement

Using COPY statement of PostgreSQL, we can load data from a text file into data base's table as below:

COPY CME_ERROR_CODES FROM E'C:\\Program Files\\ERROR_CODES\\errcodes.txt' DELIMITER AS '~'

The above statement is run from a machine which has postgresql client where as the server is in another windows machine. Running the above statement is complaining me that ERROR: could not open file "C:\Program Files\ERROR_CODES\errcodes.txt" for reading: No such file or directory.

After some research, i observed that COPY statement is looking for the loader file(errcodes.txt) in the postgresql server's machine at the same path (C:\Program Files\ERROR_CODES). To test this , i have create the same folder structure in the postgresql server's machine and kept the errcodes.txt file in there. Then the COPY statement worked well. It looks very tough constraint for me with COPY statement. Is there any setting needed to avoid this? or it is the behavior of COPY statement? I didn't find any information on PostgreSQL documents.

Upvotes: 0

Views: 12032

Answers (2)

filiprem
filiprem

Reputation: 7124

here's the standard solution:

COPY foo (i, j, k) FROM stdin;
1<TAB>2<TAB>3
\.

The data must be properly escaped and tab-separated.

Actually, it is in the docs, even in grammar definition you have STDIN... See: http://www.postgresql.org/docs/9.1/static/sql-copy.html

If you're using some programming language with COPY support, you will have pg_putcopy or similar function. So you don't have to worry about escaping and concatenation.

Hints how to do this manually in Python -> Recreating Postgres COPY directly in Python?

The Perl way -> http://search.cpan.org/dist/DBD-Pg/Pg.pm#COPY_support

Hope this helps.

Upvotes: 2

Gavin
Gavin

Reputation: 6460

From the documentation

Quote: COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

If you want to copy from a local machine file to a server use \copy command.

Upvotes: 0

Related Questions