Thread: psql \copy with multi-line query

psql \copy with multi-line query

From
Rob Sargent
Date:
Using version 13 psql on version 14 server and having trouble sending a multi-line select in the \copy.  I don't see any remark about this in the man page.

\copy (
select a.mrn, a.relationship,a.relation_mrn,a.provided_relationship
from actual_and_inf_rel_part1_unique_clean a
join family_ids f on f.individual_id in (a.mrn, a.relation_mrn)
where f.family_id = 1954 and relationship != 'Child'
union
select a.relation_mrn, case when d.sex = 'M' then 'Father' when d.sex = 'F' then 'Mother' else 'Parent' end, a.mrn, a.provided_relationship
from actual_and_inf_rel_part1_unique_clean a
join family_ids f on f.individual_id in (a.mrn, a.relation_mrn)
join pt_demog d on a.mrn = d.mrn
where f.family_id = 1954 and relationship = 'Child'
)
to /some/long/path/emp1954/1954rel.csv  csv header delimiter E'\t'

\copy: parse error at end of line

select a.mrn, a.relationship,a.relation_mrn,a.provided_relationship
from actual_and_inf_rel_part1_unique_clean a
join family_ids f on f.individual_id in (a.mrn, a.relation_mrn)
where f.family_id = 1954 and relationship != 'Child'
union
select a.relation_mrn, case when d.sex = 'M' then 'Father' when d.sex = 'F' then 'Mother' else 'Parent' end, a.mrn, a.provided_relationship
from actual_and_inf_rel_part1_unique_clean a
join family_ids f on f.individual_id in (a.mrn, a.relation_mrn)
join pt_demog d on a.mrn = d.mrn
where f.family_id = 1954 and relationship = 'Child';

   mrn   |               relationship               | relation_mrn | provided_relationship
---------+------------------------------------------+--------------+-----------------------
 70971   | Great-grandchild                         | 2673727      |                      
 1518425 | Mother                                   | 70971        |                     1
 851744  | Mother                                   | 918639       |                     1
 2629602 | Father                                   | 959532       |                     1
 2981293 | Mother                                   | 1135789      |                     1
 2629602 | Mother                                   | 439681       |                     1
 70971   | Great-grandchild                         | 2811064      |                      
 2714530 | Father                                   | 851744       |                     1
 2520718 | Grandchild                               | 2029421      |                     1
 339921  | Mother                                   | 1135789      |                     1
 2520718 | Grandchild                               | 339921       |                      
 2981293 | Father                                   | 851744       |                     1
 2029421 | Mother                                   | 439681       |                     1
 918639  | Grandchild                               | 3696817      |                      
 70971   | Great-grandchild                         | 339921       |                      
 439681  | Spouse                                   | 959532       |                     1
 339921  | Father                                   | 851744       |                     1
 2520718 | Grandchild/Grandchild-in-law             | 3731101      |                      
 2520718 | Grandchild                               | 2673727      |                      
 918639  | Grandchild                               | 2673727      |                      
 70971   | Great-grandchild                         | 2981293      |                      
 918639  | Grandchild                               | 339921       |                      
 1341357 | Spouse                                   | 2520718      |                     1
 70971   | Grandchild                               | 1135789      |                      
 2520718 | Grandchild                               | 2981293      |                      
 3696817 | Father                                   | 851744       |                     1
 2520718 | Mother                                   | 70971        |                     1
 1135789 | Mother                                   | 2520718      |                     1
 918639  | Grandchild                               | 2714530      |                      
 3468386 | Mother                                   | 2694687      |                     1
 3594047 | Mother                                   | 2694687      |                     1
 1552075 | Spouse                                   | 3731101      |                     1
 2714530 | Mother                                   | 2694687      |                     1
 2673727 | Mother                                   | 1135789      |                     1
 1135789 | Nephew/Niece                             | 3731101      |                     1
 70971   | Great-grandchild                         | 2029421      |                      
 918639  | Grandchild                               | 2981293      |                      
 70971   | Great-grandchild/Great-grandchild-in-law | 3731101      |                      
 2520718 | Grandchild                               | 2811064      |                     1
 2673727 | Father                                   | 851744       |                     1
(40 rows)

Re: psql \copy with multi-line query

From
"David G. Johnston"
Date:
On Fri, Mar 24, 2023 at 4:04 PM Rob Sargent <robjsargent@gmail.com> wrote:
Using version 13 psql on version 14 server and having trouble sending a multi-line select in the \copy.  I don't see any remark about this in the man page.

The copy meta-command cannot contain newlines.  You should use "COPY ... TO STDOUT" directly instead.

All of this is discussed under "\copy" in the documentation (including notes).


David J.

Re: psql \copy with multi-line query

From
Rob Sargent
Date:
On 3/24/23 17:14, David G. Johnston wrote:
On Fri, Mar 24, 2023 at 4:04 PM Rob Sargent <robjsargent@gmail.com> wrote:
Using version 13 psql on version 14 server and having trouble sending a multi-line select in the \copy.  I don't see any remark about this in the man page.

The copy meta-command cannot contain newlines.  You should use "COPY ... TO STDOUT" directly instead.

All of this is discussed under "\copy" in the documentation (including notes).


David J.

Thank you.  I realize now I went to COPY not \copy docs.