Copy output of WITH subquery to CSV in postgres

Question

I am trying to save the output of below 'WITH' sub-query to a csv file.

 WITH mer9 AS (
                SELECT *, 
                        substring(seq_window_mut_9mers, split9.start, 9)
                FROM split9
        ),

    mer23 AS (
                  SELECT *, 
                        substring(seq_window_mut_23mers, split23.start, 23)
                   FROM split23
        ),

    dataset AS (
                SELECT *
                    FROM table 
                    INNER JOIN mer9 ON mer9.seq_window_mut_9mers = table.seq_window_mut_9mers
                    INNER JOIN mer23 ON mer23.seq_window_mut_23mers = table.seq_window_mut_23mers

        )

COPY (SELECT * FROM dataset) TO '/tmp/filename.csv' (format CSV);

After running the query, I am getting an error:

[Code: 0, SQL State: 42601]  ERROR: syntax error at or near "COPY"
  Position: 3566  [Script position: 3566 - 3570]

6

`copy (with ( ..) select ...) to ` – Feb 07 '20 at 12:32

Jim Jones · Accepted Answer · 2021-10-12T22:28:43.610

3

Result sets generated from a CTE cannot be accessed in a different query. A CTE creates a sort of "temporary table" that only exists in the current query. That being said, put your CTE inside of the COPY statement and it should work, e.g.

COPY (
 WITH mer9 AS (
  SELECT *, substring(seq_window_mut_9mers, split9.start, 9)
  FROM split9),
 mer23 AS (
  SELECT *, substring(seq_window_mut_23mers, split23.start, 23)
  FROM split23),
 dataset AS (
  SELECT * FROM table 
  INNER JOIN mer9 ON mer9.seq_window_mut_9mers = table.seq_window_mut_9mers
  INNER JOIN mer23 ON mer23.seq_window_mut_23mers = table.seq_window_mut_23mers
 )
) TO '/tmp/filename.csv' (format CSV);

Edit. As pointed out by @a_horse_with_no_name:

Keep in mind that this command will create a file in the server. If you wish to create a file with the output in the client, consider using STDOUT in your COPY command, e.g. using psql:

$ psql -d yourdb -h yourdbhost -U your_user -c "COPY (WITH..) TO STDOUT" > file.csv

See also this answer.

edited Oct 12 '21 at 22:28

answered Feb 07 '20 at 12:51

Jim Jones

18,404
3
35
44

Is it possible to save the output in current directory, instead of tmp? – rshar Feb 07 '20 at 12:54
3

@rshar: `copy` creates the file **on the server** - and the Postgres service process does not really have a "current directory". And if it does, it will most probably not be what you think it is. – Feb 07 '20 at 12:58
@rshar if you wish to have the output file in the client, consider using `STDOUT`. I just added this option in my asnwer – Jim Jones Feb 07 '20 at 13:28
2

@JimJones: or use `\copy` in `psql` – Feb 07 '20 at 14:05
I am wriiting the output to /tmp folder however I am unable to see the csv. Query is not giving me any error. In principle, I should be able to see the csv file – rshar Feb 07 '20 at 15:03
@rshar what happens if you use `stdout` instead? – Jim Jones Feb 07 '20 at 15:08
1

Im getting a syntax error putting the CTE inside the COPY parenthesis "ERROR: syntax error at or near ")" – ennth Oct 09 '21 at 10:28

Copy output of WITH subquery to CSV in postgres

1 Answers1