Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pfqSj-0005O1-06 for pgsql-general@arkaria.postgresql.org; Fri, 24 Mar 2023 23:04:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pfqSh-00008G-1O for pgsql-general@arkaria.postgresql.org; Fri, 24 Mar 2023 23:04:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pfqSg-000086-Cd for pgsql-general@lists.postgresql.org; Fri, 24 Mar 2023 23:04:42 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pfqSZ-0000fQ-DI for pgsql-general@lists.postgresql.org; Fri, 24 Mar 2023 23:04:41 +0000 Received: by mail-pj1-x1035.google.com with SMTP id q102so2760294pjq.3 for ; Fri, 24 Mar 2023 16:04:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1679699074; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=oY3f3RpEW6gMNdl9UOArYq02WyEHrk113enUyoBNQj8=; b=FwXSzBz52Or4vC2xyrR0prBqwCfspen1SU5EQerQCI1lqyWorFaK+V5CYZQuD9q9Dj 10S5V3uGCXmLWfqLvecvnobxUWMpRKnIMob0cXspaohiAFeApUrceRSNO8X02PcCUTki 9knV1t1oBKcfdija0kidVvtkctdR38DECs9U7hpj6JWBiDdTGSuLKls0f3ZBLDztW8DK HzU384TzEaeVIE5DTrBo2kpBLCNd1NMHH21RITx0qrOOCKNpwNn+GXVczTop8GaO6BJv y0PbPWlnfZHNbooALyHi6yUAIFgdlz4FhsxJGi6dLqQDYUP73crdP99+9qQCTJsLBRBo 8OcQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1679699074; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=oY3f3RpEW6gMNdl9UOArYq02WyEHrk113enUyoBNQj8=; b=188KIohEu8NfwgcNwKoBQYYQiuV1LHz93tqnFhpI0TtnGzi9iodOzYTtxzot3DoxXN RV2l/kwAclLaMapGIjYv76EPz55OxHX/f1dAz4I8vJC7ozBlL1j2R8kPDbBZ8a0H8vze D62SX2MfU4nfGtu0CUy+WfZxlaN3TQuLp+P/0t9e/cAMviPUeiFxsqioqTtysoJTZj7s Xj+1SABZd1F4UCXro3LxV21TsenKnAb4U3CEK1wXhmQow+cEeONogxC4gmFfzdgspVwK snVOKTDZvEE1KPefojw9g3BwkpgJ1ETDGgWuAXZKxXx1vqNCjiOP1AF890xkuJnVtmRq mmUg== X-Gm-Message-State: AO0yUKUvodBMv15IVAKSRItXqa57TIurO730RazAEilUF686XvZWo0CI VCL4F8/tnu+ZLfp3dJ5665WVlDczR6A= X-Google-Smtp-Source: AK7set+Hhz1wfOITDlDjNjTLS6sclwza35AK/yw5BUXD73rCzIn44SKW/9WBZ876W2GTSwiCKSJOPA== X-Received: by 2002:a05:6a20:8b26:b0:d9:e45d:95d6 with SMTP id l38-20020a056a208b2600b000d9e45d95d6mr4354950pzh.35.1679699073920; Fri, 24 Mar 2023 16:04:33 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:8a29:e239:83a4:da2e? ([2601:681:5a00:f790:8a29:e239:83a4:da2e]) by smtp.gmail.com with ESMTPSA id b23-20020aa78117000000b005ac8a51d591sm14387027pfi.21.2023.03.24.16.04.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 24 Mar 2023 16:04:33 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------AGFVL7tHC70xV5BfSlGAVsug" Message-ID: <89a56b8a-6876-eeb3-12d6-a0761581ce1d@gmail.com> Date: Fri, 24 Mar 2023 17:04:32 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Content-Language: en-CA To: "pgsql-generallists.postgresql.org" From: Rob Sargent Subject: psql \copy with multi-line query List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------AGFVL7tHC70xV5BfSlGAVsug Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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) --------------AGFVL7tHC70xV5BfSlGAVsug Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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)

--------------AGFVL7tHC70xV5BfSlGAVsug--