COPY FROM STDIN from client app

Question

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"?

Answer 1

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.