PostgreSQL COPY pipe output to gzip and then to STDOUT

The following command works well

$ psql -c "copy (select * from foo limit 3) to stdout csv header"

# output
column1,column2
val1,val2
val3,val4
val5,val6

However the following does not:

$ psql -c "copy (select * from foo limit 3) to program 'gzip -f --stdout' csv header"

# output
COPY 3

Why do I have COPY 3 as the output from this command? I would expect that the output would be the compressed CSV string, after passing through gzip.

The command below works, for instance:

$ psql -c "copy (select * from foo limit 3) to stdout csv header" | gzip -f -c

# output (this garbage is just the compressed string and is as expected)
߉T`M�A �0 ﾥ}6�BL�I+�^E�gv�ĳAp���qH�1����� FfВ�,Д���}������+��

How to make a single SQL command that directly pipes the result into gzip and sends the compressed string to STDOUT?

Solution

When you use COPY ... TO PROGRAM, the PostgreSQL server process (backend) starts a new process and pipes the file to the process's standard input. The standard output of that process is lost. It only makes sense to use COPY ... TO PROGRAM if the called program writes the data to a file or similar.

If your goal is to compress the data that go across the network, you could use sslmode=require sslcompression=on in your connect string to use the SSL network compression feature I built into PostgreSQL 9.2. Unfortunately this has been deprecated and most OpenSSL binaries are shipped with the feature disabled.

There is currently a native network compression patch under development, but it is questionable whether that will make v14.

Other than that, you cannot get what you want at the moment.