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.
Answer
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.