Postgres send output of COPY over the network

Question

I would like to do the following:

psql -c "COPY (SELECT a.id, b.id FROM a JOIN b USING id) TO STDOUT;"

the problem is that it would never send anything until the join is fully computed. Is there a way to tell Postgres to output the join while its being computed without writing it into a some temporary storage?

Craig Ringer · Accepted Answer · 2015-06-24 21:59:34Z

1

Use psql's \copy.

\COPY (SELECT a.id, b.id FROM a JOIN b USING id) TO stdout;

You can replace stdout with '/path/to/file' if desired.

An inner join can be incrementally computed, so there should be no issues there presuming the tables aren't views over more complex queries. Show the output of explain select ... if you still don't get incremental output.

answered Jun 24, 2015 at 21:59

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

iggy Over a year ago

thanks, that seems to do the job. my join is slightly more complex than a JOIN B

iggy Over a year ago

Actually, from the docs of

psql:    \copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ]            Performs a frontend (client) copy. This is an operation that runs an SQL COPY(7) command, but instead of the server reading or writing the specified file, psql            reads or writes the file and routes the data between the server and the local file system.

So its same as COPY TO .. STDOUT with respect to JOIN computation

Collectives™ on Stack Overflow

Postgres send output of COPY over the network

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related