PostgreSQL COPY – How to Use COPY FROM STDIN from Client App

copypostgresqlpsql

I have a client appconsole. I want to execute a SQL command "copy from stdin" and send data from client STDIN to Postgres server to fill a table. Is it possible without psqlclient and just via sqlcommand copy?

Every time, in other posts when someone asks about it, folks always tell us to use psqlclient, but I don't want to use it.

I've searched already in psqlclient source code at git. There, they are using methods like "PQputCopyData(conn, buf, linelen)" from libpq.dll to process stream data.

So that's why I'm asking if it is possible to pass data using just SQL commands and client data pipe. If it's not possible, what is the reason for this command "FROM STDIN"?

Best Answer

Sure it's possible just TIAS.

CREATE TABLE f ( id int );

Then

$ echo 1 | psql -d test -c 'COPY f FROM STDIN'
COPY 1

In fact all of \COPY is just a wrapper around that functionality. From the docs, on COPY

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

So that's why i'm asking, if it is possible to pass data using just sql commands and client data pipe, if it's not possible, what is the reason for this command "FROM STDIN"?

Because it's also possible for the server to open a file handle to a local file and read from that file directly. So there are two modes, the server reads from a file, or the server read reads from STDIN. \copy itself just pipes the data over the socket to the server and tells it to read from STDIN.

Related Solutions

Postgres \copy Command – Accessing Temporary Tables

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv';

Call:

psql -p5432 mydb -f test.sql

Note the terminating semicolon, which is optional at the end of a file (terminated implicitly), but required after any other SQL statement and also after the last one if executed in psql interactively.

Normally, psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Different rules apply after \copy, though. Essentially, psql switches back to SQL mode automatically after \copy See:

How to update selected rows with values from a CSV file in Postgres?

But you wrote you had all commands on separate lines. So that cannot be the explanation in your case.

All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command)?

Of course, the target file would have to be local to the server not the client in this case. And different file privileges apply. The manual:

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client.

Postgresql – Usage of COPY FROM STDIN in postgres

pgScript is a local script extension of pgAdmin, which you most probably do not want here.

pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql.

If you have a file (which you obviously do), just use SQL COPY from pgAdmin:

COPY action_abilitations (id, group_action_id, ...)
FROM 'C:\Users\usernexus\Desktop\database05-12-2012.sql';

The file needs to be readable to the postgres system user.

More info in this closely related request to the pgAdmin support list.

Best Answer

Related Solutions

Postgres \copy Command – Accessing Temporary Tables

Postgresql – Usage of COPY FROM STDIN in postgres

Related Question