psql \copy with multi-line query

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: psql \copy with multi-line query
Date: 2023-03-24 23:04:32
Message-ID: 89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2023-03-24 23:14:28 Re: psql \copy with multi-line query
Previous Message Miles Elam 2023-03-24 21:18:38 Re: PostgreSQL vs MariaDB