CODEWITHSUNDEEP
postgresql
user2301515
user2301515

Reputation: 5117

from file to postgresql table

If i have a schema named 'public' and table tbl in it and also data tablename.csv:

1   name1
2   name2

How to copy a file data in localhost to postgresql in remote host:

pg_query($dbConn, "COPY public.tbl FROM 'tablename.csv' DELIMITER '\t' CSV;");

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "\" LINE 1: \COPY public.tbl FROM 'C:/workspace/public.tbl... ^ in C:\workspace...\file.php on line 110. or pgAdmin III the same command

COPY tablename FROM 'C:\workspace\filename.CSV' DELIMITER ' ' CSV;

Shows "ERROR: could not open file "C:\workspace\filename.CSV" for reading: No such file or directory".

Upvotes: 0

Views: 1578

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 325141

The COPY command, if passed a filename, refers to a filename on the server side.

If you want to COPY from a local file you can use COPY ... FROM stdin, but it's a bit of a pain to work with directly using most client drivers.

Instead, use pg_copy_from, the PHP PostgreSQL driver function that's designed for this job.

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

From the fine manual . . .

It is recommended that the file name used in COPY always be specified as an absolute path. This is enforced by the server in the case of COPY TO, but for COPY FROM you do have the option of reading from a file specified by a relative path. The path will be interpreted relative to the working directory of the server process (normally the cluster's data directory), not the client's working directory.

So use an absolute path, and keep in mind that the path is interpreted from the point of view of the server process. If the server can't see your C: drive as a C: drive (and it can't), then you need a different path.

The easiest way to make this work is to upload filename.csv to your database server, and use an absolute path to it.

Upvotes: 0

Related Questions