psql \copy with multi-line query - Mailing list pgsql-general
| From | Rob Sargent |
|---|---|
| Subject | psql \copy with multi-line query |
| Date | |
| Msg-id | 89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com Whole thread Raw |
| Responses |
Re: psql \copy with multi-line query
|
| List | 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)
\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)
pgsql-general by date: