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 1q8T0z-0006lm-Q8 for pgsql-admin@arkaria.postgresql.org; Sun, 11 Jun 2023 21:54:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q8T0y-0006pH-98 for pgsql-admin@arkaria.postgresql.org; Sun, 11 Jun 2023 21:54:24 +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 1q8T0x-0006jN-Pe for pgsql-admin@lists.postgresql.org; Sun, 11 Jun 2023 21:54:23 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q8T0s-001fvb-W8 for pgsql-admin@lists.postgresql.org; Sun, 11 Jun 2023 21:54:22 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 543C7380454; Sun, 11 Jun 2023 23:54:16 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id aytCvGST8Mmx; Sun, 11 Jun 2023 23:54:16 +0200 (CEST) Received: from [192.168.179.156] (p57b83fa6.dip0.t-ipconnect.de [87.184.63.166]) by rs.plausibolo.de (Postfix) with ESMTPSA id ED6CC380313; Sun, 11 Jun 2023 23:54:15 +0200 (CEST) Message-ID: <726b10c7-5f27-620a-7fb7-a5ad19cf8bd2@jakobs.com> Date: Sun, 11 Jun 2023 23:54:15 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Subject: Re: How to use long list of columns with COPY command In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------qSKXdTNHyRaOzsnag89jyPpa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------qSKXdTNHyRaOzsnag89jyPpa Content-Type: multipart/mixed; boundary="------------U2r90FJKearOv4z58W5kV54X"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <726b10c7-5f27-620a-7fb7-a5ad19cf8bd2@jakobs.com> Subject: Re: How to use long list of columns with COPY command References: In-Reply-To: --------------U2r90FJKearOv4z58W5kV54X Content-Type: multipart/alternative; boundary="------------HfmKRmcrzKsJTuItzW1JU2Lv" --------------HfmKRmcrzKsJTuItzW1JU2Lv Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 DQpBbSAxMS4wNi4yMyB1bSAyMzo0NCBzY2hyaWViIE0gU2Fyd2FyOg0KPiBIZWxsbywNCj4g SSBoYXZlIHNvbWUgcHJvYmxlbSB3aGlsZSBsb2FkaW5nIHRoZSBkYXRhIHVzaW5nIENPUFkg Y29tbWFuZC4NCj4NCj4gKl9NeSBlbnZpcm9ubWVudF8qOg0KPiBBbWF6b24gUkRTDQo+IFBv c3RncmVzIC0gRGF0YWJhc2UNCj4gUGdBZG1pbiAtLcKgIHdpdGggUFNRTCB0b29sLg0KPiBD b21tYW5kIC0tIENPUFkNCj4NCj4gSSBhbSB0cnlpbmcgdG8gdXNlIENPUFkgY29tbWFuZCB0 byBsb2FkIHRoZSBkYXRhLiBUYWJsZSANCj4gVEVTVF9HTE9CQUxfTUNNX1JBVyBoYXMgMjA5 IGNvbHVtbnMuIEluIENPUFkgY29tbWFuZCwgSSBhbSB0cnlpbmcgdG8gDQo+IHVzZSBhcm91 bmQgMjAwIGNvbHVtbnMgdG8gbG9hZCB0aGUgZGF0YS4NCj4gV2hlbiBJIHRyeSB0byBzcGFu IENPUFkgY29tbWFuZCBpbnRvIG11bHRpcGxlIGxpbmVzLCBDT1BZIGNvbW1hbmQgaXMgDQo+ IG5vdCB3b3JraW5nLiBJIGFtIGZvcmNlZCB0byB1c2UgYWxsIHRoZSBjb21tYW5kIGluIGEg c2luZ2xlIGxpbmUgYW5kIA0KPiB3aGljaCBpcyBub3QgbGVnaWJsZS4NCj4gRG8gSSBoYXZl IGFueSBvcHRpb25zIHRvIG1ha2UgdGhlIENPUFkgY29tbWFuZCBsZWdpYmxlIHdoaWxlIHVz aW5nIGFsbCANCj4gdGhlIGNvbHVtbnMgb2YgYW55IHRhYmxlLg0KPiBJIGhhdmUgdHJpZWQg XG4gYW5kIHRoaXMgaXMgbm90IHdvcmtpbmcuDQo+DQo+IEl0IGlzIHBvc3NpYmxlIHRoYXQg c29tZW9uZcKgIGhhcyBmYWNlZCB0aGlzIHNpdHVhdGlvbiBpbiB0aGXCoHBhc3QuDQo+DQo+ IFRoYW5rIHlvdSwNCj4gU2Fyd2FyDQo+DQpIaSBTYXJ3YXIsDQoNCg0KUGFnZSBodHRwczov L3d3dy5wb3N0Z3Jlc3FsLm9yZy9kb2NzLzE1L2FwcC1wc3FsLmh0bWwgc2F5czoNCg0KQW5v dGhlciB3YXkgdG8gb2J0YWluIHRoZSBzYW1lIHJlc3VsdCBhcyB8XGNvcHkgLi4uIHRvfCBp cyB0byB1c2UgdGhlIA0KU1FMIHxDT1BZIC4uLiBUTyBTVERPVVR8IGNvbW1hbmQgYW5kIHRl cm1pbmF0ZSBpdCB3aXRoIHxcZyAvfGZpbGVuYW1lfC98IA0Kb3IgfFxnIHwvfHByb2dyYW18 L3wuIFVubGlrZSB8XGNvcHl8LCB0aGlzIG1ldGhvZCBhbGxvd3MgdGhlIGNvbW1hbmQgdG8g DQpzcGFuIG11bHRpcGxlIGxpbmVzOyBhbHNvLCB2YXJpYWJsZSBpbnRlcnBvbGF0aW9uIGFu ZCBiYWNrcXVvdGUgDQpleHBhbnNpb24gY2FuIGJlIHVzZWQuDQoNCg0KU28sIHdoZW4gbG9h ZGluZyBGUk9NwqAgYSBmaWxlIHVzaW5nIFxDT1BZICh5b3Ugd2VyZSB3cml0aW5nIGFib3V0 IENPUFksIA0Kd2hpY2ggYWxsb3dzIGxpbmUgYnJlYWtzIGxpa2UgYWxsIFNRTCBjb21tYW5k cyksIGxpbmUgYnJlYWtzIGFyZW4ndCANCmFsbG93ZWQuIEl0J3MgdGhlIHNhbWUgZm9yIGFs bCBiYWNrc2xhc2ggbWV0YSBjb21tYW5kcyBvZiBwc3FsLg0KDQoNCkl0IHNlZW1zIHRoYXQg eW91J2xsIGhhdmUgdG8gd3JpdGUgdGhlIGNvbXBsZXRlIFxDT1BZIGNvbW1hbmQgaW4gb25l IA0KbGluZS4gSXQncyB1c3VhbGx5IHF1aXRlIHNob3J0LCB1bmxlc3MgeW91IGhhdmUgdG8g bmFtZSBhIGhlbGwgb2YgYSBsb3QgDQpvZiBjb2x1bW4gbmFtZXMuDQoNCg0KUmVnYXJkcywN Cg0KSG9sZ2VyDQoNCg0KDQoNCi0tIA0KSG9sZ2VyIEpha29icywgQmVyZ2lzY2ggR2xhZGJh Y2gsIFRlbC4gKzQ5LTE3OC05NzU5MDEyDQoNCg== --------------HfmKRmcrzKsJTuItzW1JU2Lv Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


Am 11.06.23 um 23:44 schrieb M Sarwar:=
Hello,
I have some problem while loading the data using COPY command.

My environment:
Amazon RDS
Postgres - Database
PgAdmin --=C2=A0 with PSQL tool.
Command -- COPY

I am trying to use COPY command to load the data. Table TEST_GLOBAL_MCM_RAW has 209 columns. In COPY command, I am trying to use around 200 columns to load the data.
When I try to span COPY command into multiple lines, COPY command is not working. I am forced to use all the command in a single line and which is not legible.
Do I have any options to make the COPY command legible while using all the columns of any table.
I have tried \n and this is not working.

It is possible that someone=C2=A0 has faced this situation in the=C2=A0past.

Thank you,=
Sarwar

Hi Sarwar,


Page https://www.postgresql.org/docs/15/app-ps= ql.html says:

Another way to obtain the same result as \= copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike \copy, this method allows the= command to span multiple lines; also, variable interpolation and backquote expansion can be used.


So, when loading FROM=C2=A0 a file using \COPY (you were writing a= bout COPY, which allows line breaks like all SQL commands), line breaks aren't allowed. It's the same for all backslash meta commands of psql.


It seems that you'll have to write the complete \COPY command in one line. It's usually quite short, unless you have to name a hell of a lot of column names.


Regards,

Holger




--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------HfmKRmcrzKsJTuItzW1JU2Lv-- --------------U2r90FJKearOv4z58W5kV54X-- --------------qSKXdTNHyRaOzsnag89jyPpa Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZIZChwUDAAAAAAAKCRCKV5yTsxlhukzJ AKC/H9k5YNwSg2/b5yqIx1zdObRPoACfVz8hWZhq9nGKw5+zcPDwmW4Vteg= =l9+L -----END PGP SIGNATURE----- --------------qSKXdTNHyRaOzsnag89jyPpa--