Create query that copies from a CSV file on my computer to the DB located on another computer in Postgres

Question

I am trying to create a query that will copy data from a CSV file that is located on my computer to a Postgres DB that is on a different computer.

Our Postgres DB is located on another computer, and I work on my own to import and query data. I have successfully copied data from the CSV file on MY computer TO the DB in PSQL Console using the following:

\COPY table_name FROM 'c:\path\to\file.csv' CSV DELIMITER E'\t' HEADER;

But when writing a query using the SQL Editor, I use the same code above without the '\' in the beginning. I get the following error:

ERROR:  could not open file "c:\pgres\dmi_vehinventory.csv" for reading: No such file or directory
********** Error **********
ERROR: could not open file "c:\pgres\dmi_vehinventory.csv" for reading: No such file or directory
SQL state: 58P01

I assume the query is actually trying to find the file on the DB's computer rather than my own.

How do I write a query that tells Postgres to look for the file on MY computer rather than the DB's computer?

Any help will be much appreciated !

sebpa · Accepted Answer · 2014-02-13 18:20:50Z

2

\COPY is a correct way if you want to upload file from local computer (computer where you've stared psql)

COPY is correct when you want to upload on remote host from remote directory

here is an example, i've connected with psql to remote server:

test=# COPY test(i, i1, i3)
      FROM './test.csv' WITH DELIMITER ',';
ERROR:  could not open file "./test.csv" for reading: No such file
test=# \COPY test(i, i1, i3)
      FROM './test.csv' WITH DELIMITER ',';
test=# select * from test;
 i | i1 | i3
---+----+----
 1 |  2 |  3
(1 row)

answered Feb 13, 2014 at 18:20

sebpa

663 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lpsmith · Accepted Answer · 2014-02-15 16:50:17Z

There are several common misconceptions when dealing with PostgreSQL's COPY command.

Even though psql's \COPY FROM '/path/to/file/on/client' command has identical syntax (other than the backslash) to the backend's COPY FROM '/path/to/file/on/server' command, they are totally different. When you include a backslash, psql actually rewrites it to a COPY FROM STDIN command instead, and then reads the file itself and transfers it over the connection.
Executing a COPY FROM 'file' command tells the backend to itself open the given path and load it into a given table. As such, the file must be mapped in the server's filesystem and the backend process must have the correct permissions to read it. However, the upside of this variant is that it is supported by any postgresql client that supports raw sql.
Successfully executing a COPY FROM STDIN places the connection into a special COPY_IN state during which an entirely different (and much simpler) sub-protocol is spoken between the client and server, which allows for data (which may or may not come from a file) to be transferred from the client to the server. As such, this command is not well supported outside of libpq, the official client library for C. If you aren't using libpq, you may or may not be able to use this command, but you'll have to do your own research.
COPY FROM STDIN/COPY TO STDOUT doesn't really have anything to do with standard input or standard output; rather the client needs to speak the sub-protocol on the database connection. In the COPY IN case, libpq provides two commands, one to send data to the backend, and another to either commit or roll back the operation. In the COPY OUT case, libpq provides one function that receives either a row of data or an end of data marker.

I don't know anything about SQL Editor, but it's likely that issuing a COPY FROM STDIN command will leave the connection in an unusable state from its point of view, especially if it's connecting via an ODBC driver. As far as I know, ODBC drivers for PostgreSQL do not support COPY IN.

Thank you very much, your answer clarified a lot. And unfortunately, after researching COPY FROM STDIN, it looks like there is no easy way to COPY from a client computer within SQL Editor. Seems like the PSQL Console is the only way.

Collectives™ on Stack Overflow

Create query that copies from a CSV file on my computer to the DB located on another computer in Postgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related