Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2RjR-000IWb-2V for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2026 10:33:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2RjQ-000GMK-2J for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2026 10:33:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w1fnY-003p1W-01 for pgsql-general@lists.postgresql.org; Sun, 15 Mar 2026 07:22:04 +0000 Received: from mail-newzealandnorthazon11021107.outbound.protection.outlook.com ([40.107.4.107] helo=AK2P299CU003.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w1fnV-00000000Eck-00Uc for pgsql-general@postgresql.org; Sun, 15 Mar 2026 07:22:03 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=q/NHRuA1/Aq+ss7Q1/bmjW0v3wLyKT2pRWPsm7b838rPkWeqG0x6nfOImHTK9cwQwvry7C1CFGdRDJ8kxd/0+0x8NsF7bzYCp5X1qyB2Rc7EMIFMQbtBB/tZR5t/eU284OLXQGiOVLgRdwxO/Md3cM5Cqp3h+kUj5c5KJ9OX4brp11TRUoNThx2TkxffJIRUtg5qxb6ShnMwWQM//2bG+9Bd2UGmInCV3cN+c04MslYYjUIn2o0ALzXbL16RTLv0c3PY9CL5MHsENKE5lcHnk60woNcrJAPsDIxkHIm+LWzxLyWudQ9zymgzpREYgT0+Nh6pZa0rpz2ZJDP9T4mPcw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=6QvTabDeZdu+y/mwogJS4rx6DPMS0qbjtI14wxXboWw=; b=p+42kb70AdqqgE3RcKlwbSYxbqsIqKh+UE6dnnAnIkS7/ggDWUoKOcCj1+k67ZIvizAi8n99hQAnGtVk1rFI44lTj9rQmClAn+dks5n7hrQ4u4YW3FkNyrJtNTzZhYkbqtQZMKEFSlpbTk9bvE6btjB7JiLL0z0cBINBQilEeo0B8Ai3WAL0k2bAaj6m55BuOUK5ALQsm4TQvzMI7t1fo+pv2yzf+/NwInd6X9Y1xE/oWX20zvgi1Rh7MfX4u2b8B1SZ9Fl3z3DgsQwe64MQo4NyLKmbzsjxAHDLiX4RKovbsO8YAdfQgR3MYyAvQq3M2HpyVAN9QD1YbWfFSYmElQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=earthsciences.nz; dmarc=pass action=none header.from=earthsciences.nz; dkim=pass header.d=earthsciences.nz; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=earthsciences.nz; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=6QvTabDeZdu+y/mwogJS4rx6DPMS0qbjtI14wxXboWw=; b=kB71pgl/nP4dj3cS2gAb/lUfxbHWbZoohEIcuUTfbHweC830yv3OpxdvMaDtD0IUylJnO0hQFJPl8wssnfs8x9P9b6rBJxTrCP/IHEERVXKnERv2d288pUfwQm97Pv99WSZZs31ZZ5ULQYKtC5roW1Zu4X05vBe+yQonsHIQdjvqxT+AVsbdc+zHOkWJ111h3tjd/zdmC9Dw4CSDWEMT50C0AWpZNiDeO9jCIQQORO5GsAZJv+//8DAoGWqdqwc/bPVjxEBw5MAAo4qokB5QKFLdVkoG6MSOzdLlNDfvyBBbURwjKlQjH4c2bd2ht8TczL3OZFzssLbEH3FfvtWd9A== Received: from AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM (2603:10c6:108:9::12) by AK2P299MB0063.NZLP299.PROD.OUTLOOK.COM (2603:10c6:108:13::14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9700.18; Sun, 15 Mar 2026 07:21:52 +0000 Received: from AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM ([fe80::412c:b6d6:b0b1:31d6]) by AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM ([fe80::412c:b6d6:b0b1:31d6%6]) with mapi id 15.20.9700.017; Sun, 15 Mar 2026 07:21:52 +0000 From: Brent Wood To: rob stone CC: "pgsql-general@postgresql.org" Subject: Re: Using \copy to populate a table Thread-Topic: Using \copy to populate a table Thread-Index: AQHctEsbHGga1YiPHUKWag8nHiEHUbWvL5od Date: Sun, 15 Mar 2026 07:21:52 +0000 Message-ID: References: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au> In-Reply-To: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_Enabled=True;MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_SiteId=f8c50276-a201-408e-a8b4-aa496e2a26cf;MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_SetDate=2026-03-15T07:21:51.733Z;MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_Name=All Employees (unrestricted);MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_ContentBits=1;MSIP_Label_defa4170-0d19-0005-0004-bc88714345d2_Method=Standard; authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=earthsciences.nz; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AK0P299MB0101:EE_|AK2P299MB0063:EE_ x-ms-office365-filtering-correlation-id: 3ab917bf-237a-4efe-0825-08de826387a3 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|69100299015|366016|376014|3109299003|1800799024|7049299003|38070700021|8096899003|56012099003|22082099003|18002099003|7053199007; x-microsoft-antispam-message-info: a6Lr32rpnO3I0SmzrmViJeld5iKFPP2RcvuFMfkybzyybnLHTcIv+Oqvd6gdO3IZ2UFdC2i0qX9WuOt+A68ZI7EGKJmoj8gAD6a1G3NFceYUO56jQeOokqye7Pkutb+9IToGvvbVL9TgXPA4z1aG/3JPLwOHEi0udiiitIH02o0/1C/K0qgMye17DmyCUgCSz4T0e+qP+6FNclaFu6OEXppqDqzJ/P9d56ZfeVpUM+WzdOQ4bTgGFRblcKD3c11dRtQYFUOMsW09ViJ5dvi5HTehvTAJMqhA755BMjP5fXo4OronZCaJyzMOnw/JB3RknipSEk1gWgIdzC/eZwvoHt7gRO3U/Iw3LN3zHCp8n6v3kTsm3d/2gdH79B2Uyhy941LgzGWlR0mWmOOC/S8k60pm1tUg8LMS3MCNSag/RMsvOIRdQahZVfVw3AlyT7YL0frAdp7Es1gZOg1EmF2keHldHbhoUUahYrstPCDU/4rcDH5w8jLyYi/fQly6pRlyzZESNCq/LA2bg6oWIGEsm5fIE3idqgLBjP/OphR9eYTsm0ZZomtP67KDTjHDGImH+iX2MiNNxIsqN80P0yTiwNQdfqC3c52gtHRROYm2yPQ0YV1urpK2r0ZtS+hFvNtCu9Gux1y7pI3eWz5Ofuu6L59Sqm3r0JKjDpYFJfJcoF4j91q6RY+tUTxjGg1Ygb5HxDEV+WLZKFvoSsBhPqCcxPRdDNzMqPXP7RTzGqqV6OVF4lK37KQW9DQK5V6JW4Fb2s2hHhUA6dkOI9X9n+V0aijbtl/YLsLJj44QTMrxEOM= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(69100299015)(366016)(376014)(3109299003)(1800799024)(7049299003)(38070700021)(8096899003)(56012099003)(22082099003)(18002099003)(7053199007);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?sQSkhbzb7hlGWY3G/sh5rQABSTy0F+cC/J6hU6BUr+3TOY+1WSaLZalSTH?= =?iso-8859-1?Q?i6+O7TbjI4yoAL/VDmmX+yLoPD95+IiZYQOe2FqHYtDbpuMjkvJeBuryZw?= =?iso-8859-1?Q?NRnrByj1HhTKhpych12wWyr6gLW0jUl3NKC5FzfoHArHoV6mkU8cpaXlLO?= =?iso-8859-1?Q?0UaflnaeqCTwBN7Q2jf1ZFTfQ9Qlfo3jNUy9HT2OTsgu5uWXhU0+PXyfwv?= =?iso-8859-1?Q?Wshlu4wJiLm0HkN49QvFzi2MCkuUGa2Y2ih6WtL59gA5vko+YsQvVK37dp?= =?iso-8859-1?Q?ZXxqI6Sc+a/pq03hFRtLye3uRvgzQ0164o6IxVs8BvAidIcVGnEL66clsq?= =?iso-8859-1?Q?IQpfvtTAiSyOJWdtA7xGVlcoG3d+JNSPT+OD8mNw/Z/jMY6y3G4LhcUJSK?= =?iso-8859-1?Q?6CgFkYD8b6hJKA+dZXDUhm4qlQRUhr8nvKU5uvCTqdKkWMgDMFiZku+nbF?= =?iso-8859-1?Q?pzxHNUcFExont50yiZTkOyPKB1tbVQgo5irkJWf9W7Zfxa7zVb5HxBxBy+?= =?iso-8859-1?Q?m9QipBEK6zzpw3PpZU6z+ISfb9ExB1SCqEjYszXik14cJztyf4867emXzR?= =?iso-8859-1?Q?TRBV7xo2kMPdNXy4xVywIMlnR9dKJdA8tGGvg8Ufh6h+3FoSa1dztcfN1N?= =?iso-8859-1?Q?As/bMeW32pK7boz2Bnx6FIZ2RUVvjUBCV2I/mYwg8JgcDN7tkBq0kg3hKY?= =?iso-8859-1?Q?9BdT6Wh9ocmL3wRDIDvqwTbI6eI8wmf6eGhXnz8KsdWuW30CSA/koGsQvZ?= =?iso-8859-1?Q?W5kd59C3/67YsHEGacXBY7g/1b4C7ZhmYWOmdwUQ0t9QQ0oKBzmIu/V9pp?= =?iso-8859-1?Q?29QG919fsLy5vFUSmaZvyZdQ9iNGk5PC8oyfNSAoIdHUiUGyZ3Dcb/P7cI?= =?iso-8859-1?Q?7JiaeVIMGBzGIL+0AZL1HCl58RL6Bn1+zdgDCSTayl0gwvVyB14/tCLE4Z?= =?iso-8859-1?Q?ZV8fuE4CwLpQcDbWxIi69XtaZ+r1QiJ5pgGLoapo2/f5RqV0lis7ih2p99?= =?iso-8859-1?Q?r6S2UEB9dXeqSX47OmP760K097YXFrNrk/EcuUvnt3dMbiMTBxHVgpQZib?= =?iso-8859-1?Q?jRtyder30GjWKgC+MERkcPv62lY+6lxrPfBLXVh8UlC6jf8KtCVZUhfXvz?= =?iso-8859-1?Q?4Y6QuajdSJ/TAzgHHl/3+yGWTrK1xg0kLMqh52gb7+B3L9iEnDFW/tB9DQ?= =?iso-8859-1?Q?JMXv5xx04DrdsW0FrTMez+Uqk41ikWw0Y4ug3Uc4+iafEgftlpUb88WmAh?= =?iso-8859-1?Q?mPJJ8vZeFofXeTxUHM6PjRmSUXMBqAB5jss4gxoBjA3CVg8DfiAzWY+tfT?= =?iso-8859-1?Q?oodg0sgpn41tbsLYMh8sqHac2pys9pe9ZziY8V6UFTXKreCdX2+0r5Pm2T?= =?iso-8859-1?Q?2beNZoKTiUQE/vifXztB1YUK8SyjSGrFTXgq8wNEYfvESf4/LAud8600Eb?= =?iso-8859-1?Q?0HrehcXXjNrHUWQxtAwQY8AogOcZZ/fv3ffu8St/MuTCgaD0dwp9GXmWva?= =?iso-8859-1?Q?p9oDpKZ0UjdyrRWCWtL4sH4hgA69QuC6JrHE/ysFpSGKRsEBLggkcwlSNW?= =?iso-8859-1?Q?7iKom3l4zatC5OisYGiiydWDScC3f/A1txgEqJaq107T+uF1pbdLpu2biP?= =?iso-8859-1?Q?k37mew52FV3BBgdwg1GHPA62B66ZNRMGvxHzGOtuoL5uL7H5cl8sfNGdaR?= =?iso-8859-1?Q?cxFix+cj2y3avgIO8y09yFpgR4Amhd+1D0f0MB1ivpsePj+sStQint75Nu?= =?iso-8859-1?Q?3vs6My8QlrNBwcZIo7NWnROFBWhhXb14Pnz7oCVUYx1c6j3FrRXvpbFSr+?= =?iso-8859-1?Q?9aVQzmpKCA=3D=3D?= Content-Type: multipart/alternative; boundary="_000_AK0P299MB010177B7E8902CCB5C4DD48DFF43AAK0P299MB0101NZLP_" MIME-Version: 1.0 X-OriginatorOrg: earthsciences.nz X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AK0P299MB0101.NZLP299.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 3ab917bf-237a-4efe-0825-08de826387a3 X-MS-Exchange-CrossTenant-originalarrivaltime: 15 Mar 2026 07:21:52.1637 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: f8c50276-a201-408e-a8b4-aa496e2a26cf X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: V8J+tGu8xFxovKldRqzfQgwLAAsPQRV7ymqTvL4l7K4Df6NW7lQUx9cxBXxC0CCZQUrUkfi+2cBTDJpxLDk66SJKmnjyBPaS9n5Ma/+ZRnc= X-MS-Exchange-Transport-CrossTenantHeadersStamped: AK2P299MB0063 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AK0P299MB010177B7E8902CCB5C4DD48DFF43AAK0P299MB0101NZLP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Have you tried explicitly specifying the string to represent null values? COPY table_name FROM 'path/to/file' WITH (FORMAT text, NULL 'null'); -- Or the older syntax: COPY table_name FROM 'path/to/file' WITH DELIMITER ',' NULL AS 'null'; ________________________________ From: rob stone Sent: Sunday, March 15, 2026 8:11 PM To: PostGreSQL MailingList Subject: Using \copy to populate a table psql (18.3 (Debian 18.3-1+b1)) Type "help" for help. Debian OS is forky. When setting up a new development database, the following occurred:- applntestdb=3D> \copy forms_table (item_type, navgn_refn, html_name, table_key, navgn_bar, rows_page, forward_to, second_to, tertiary_to, active_item, super_only, system_admin, inserted_by) from '/home/postgres/loadfiles/formstable.txt' header delimiter '|'; ERROR: invalid input syntax for type smallint: "null" CONTEXT: COPY forms_table, line 2, column rows_page: "null" applntestdb=3D> This is what is on line 2 of the file:- C|0|pageloader|||null|null|null|null|t|f|f|0 The test below was simplified to just choose one SMALLINT column. applntestdb=3D> insert into forms_table (item_type, navgn_refn, html_name, rows_page, active_item, inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0); INSERT 0 1 applntestdb=3D> delete from forms_table where item_type =3D 'C'; DELETE 1 applntestdb=3D> insert into forms_table (item_type, navgn_refn, html_name, rows_page, active_item, inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0); ERROR: invalid input syntax for type smallint: "" LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0) ^ So, psql is quite happy to insert null into a column defined as smallint, but when you use the \copy mechanism to populate a table it pulls an error. How do we fix this? Alter all the nulls to zeroes in the file or write a program to dissect the file and create individual insert statements? Has anybody else had this problem and if so what was the solution? Thanks, Rob Brent Wood Principal Technician - GIS and Spatial Data Management +64-4-386-0529 301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand Earth Sciences New Zealand [Earth Sciences New Zealand] The Institute of Geological and Nuclear Sciences Limited and the National I= nstitute of Water and Atmospheric Research Limited joined to become the New= Zealand Institute for Earth Science Limited. We are known as Earth Science= s New Zealand. For more information on the Earth Sciences transition click = here. Notice: This email and any attachments may contain information which is con= fidential and/or subject to copyright or legal privilege, and may not be us= ed, published or redistributed without the prior written consent of Earth S= ciences New Zealand. If you are not the intended recipient, please immediat= ely notify the sender and delete the email and any attachments. Any opinion= or views expressed in this email are those of the individual sender and ma= y not represent those of Earth Sciences New Zealand. For information about how we process data and monitor communications please= see our privacy policy. --_000_AK0P299MB010177B7E8902CCB5C4DD48DFF43AAK0P299MB0101NZLP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Have you tried explicitly specifying the string to represent null values? 

=
COPY table_name FROM 'path/to/file' WITH (FORMAT text, NULL 'null');=0A=
-- Or the older syntax:=0A=
COPY table_name FROM 'path/to/file' WITH DELIMITER ',' NULL AS 'null';


From: rob stone <floriparob@tpg.com.au>
Sent: Sunday, March 15, 2026 8:11 PM
To: PostGreSQL MailingList <pgsql-general@postgresql.org>=
Subject: Using \copy to populate a table
 
psql (18.3 (Debian 18.3-1+b1))
Type "help" for help.

Debian OS is forky.

When setting up a new development database, the following occurred:-

applntestdb=3D> \copy forms_table (item_type, navgn_refn, html_name,
table_key, navgn_bar, rows_page, forward_to, second_to, tertiary_to,
active_item, super_only, system_admin, inserted_by) from
'/home/postgres/loadfiles/formstable.txt' header delimiter '|';
ERROR:  invalid input syntax for type smallint: "null"
CONTEXT:  COPY forms_table, line 2, column rows_page: "null"=
applntestdb=3D>

This is what is on line 2 of the file:-
C|0|pageloader|||null|null|null|null|t|f|f|0

The test below was simplified to just choose one SMALLINT column.

applntestdb=3D> insert into forms_table (item_type, navgn_refn,
html_name, rows_page, active_item,
inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0);
INSERT 0 1
applntestdb=3D> delete from forms_table where item_type =3D 'C';
DELETE 1
applntestdb=3D> insert into forms_table (item_type, navgn_refn,
html_name, rows_page, active_item,
inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0);
ERROR:  invalid input syntax for type smallint: ""
LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0)
            &nb= sp;            =             &nb= sp;            = ^
            &nb= sp;            =             &nb= sp;            =
So, psql is quite happy to insert null into a column defined as
smallint, but when you use the \copy mechanism to populate a table it
pulls an error.

How do we fix this? Alter all the nulls to zeroes in the file or write
a program to dissect the file and create individual insert statements?

Has anybody else had this problem and if so what was the solution?

Thanks,
Rob



Brent Wood
Principal Technician - GIS and Spatial Data= Management
+64-= 4-386-0529
301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
Earth Sciences New Zealand
3D"Earth
The Institute of Geological and Nuclear Sciences Limited and the National I= nstitute of Water and Atmospheric Research Limited joined to become the New= Zealand Institute for Earth Science Limited. We are known as Earth Science= s New Zealand. For more information on the Earth Sciences transition click here.

Notice: This email and any attachments may contain information which= is confidential and/or subject to copyright or legal privilege, and may no= t be used, published or redistributed without the prior written consent of = Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and d= elete the email and any attachments. Any opinion or views expressed in this= email are those of the individual sender and may not represent those of Ea= rth Sciences New Zealand.

For information about how we process data and monitor communications please= see our privacy policy= .
--_000_AK0P299MB010177B7E8902CCB5C4DD48DFF43AAK0P299MB0101NZLP_--