pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: psql \copy with multi-line query
Date: Fri, 24 Mar 2023 17:04:32 -0600
Message-ID: <89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com> (raw)

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)

view thread (3+ messages)  latest in thread

Message-ID: <89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com>
Permalink:  ../89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com/
Also on:    postgresql.org/message-id/89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com
  Subject: Re: psql \copy with multi-line query
  In-Reply-To: <89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com>
  References: <89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox