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 1w1i7u-000XOR-0G for pgsql-general@arkaria.postgresql.org; Sun, 15 Mar 2026 09:51:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w1i7t-0044Tx-0Q for pgsql-general@arkaria.postgresql.org; Sun, 15 Mar 2026 09:51:13 +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 1w1i7s-0044Tp-26 for pgsql-general@lists.postgresql.org; Sun, 15 Mar 2026 09:51:13 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w1i7q-00000000FVd-0ydt for pgsql-general@postgresql.org; Sun, 15 Mar 2026 09:51:13 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-6652452d94aso323466a12.3 for ; Sun, 15 Mar 2026 02:51:10 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773568270; cv=none; d=google.com; s=arc-20240605; b=gpy9vydygUTM6w8m2Z/HL+sXIAVyqo7hzKsLK9GosWB+TRLu1eSPGwTmMsViC+PgEx m8Rs4Kq24r7IpfoU2QJeWEagk+HksCm8rFELLq73dzjQwEkIyqDHEZ0abAYMMpF2IDYf 9A1H84aZlD1GPyt5e22WHHop35c0qzWPNmfmh4JQGLnIIUh2HBKiGvq9WSM7CDbnOW2o EXl8l8VYcHembWcEWsjqxQVeKTw55BLwn68dQeiHArcTlpr140gLKRTQBSQUhuAutAs9 Rwtfh/PmvLT1ak42ijCdQIt5kyWQ+eF4g9G3tJUxSly30TbmjuPOpe9dAg8KTBH9Tra3 qxXw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=77D+4YPF7YI883VPLHb7vbn1uhWMx1l7wlwdXEKJrEY=; fh=PLEZCXRgtmeCXDYef22Z0Gom37e2a66fKYmIwBaC19M=; b=aQusm00F9xTAonkGAPgx05t4lGF5ch/vIxaRg732LwcNN7k/sx3/3QDRMyi0CKj1ix fJZCuEA4LoD8CmeYVnFdAnuhKCVLrTLJsLrQoCCud5BnQYctSONn+WpDtAArxS1ihUqN 5sKNMDX1CehrgMUlc42hhrIzp7VbcAND/vCAedBSxtVTZ8k/NYXZsALQABaJQg2aNvaE hk9aWzfIsmU7lzYSxBI8CY4s/T5R+ua3eQonF0fTbSksbnQwb2f5oVZ1+TsaM9c0HEt8 ZIM0HOr0QmQjG/SQi7ugkv9zvAEdgLtih04p84aruI42QscFyC2Ut7fnG+jqtmi6zxte MqQg==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=peoplecall-com.20230601.gappssmtp.com; s=20230601; t=1773568270; x=1774173070; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=77D+4YPF7YI883VPLHb7vbn1uhWMx1l7wlwdXEKJrEY=; b=RIUyYt3snREvuLi97FkPL/8obIbUzlx1ivCRtZ4mAZd7Y7fFsaR6MhsOcuXNy6I7YG Wvgcp2+GTKhRd5w4Hg3Ufj18BdTewed47A7k2JmkBPLGy/4R+427fXZB4mKfvo9tHeaM qaTSx0HrVbckljE4gQbjJ+nlozpvz+ul1CSIBqexh5A0aGIQnGCuk0DIMa10hQ9RO5Ga z8P0gQ4lJ2N54IBWRziooZzoyy0X5ZeyGsKmDdU2rCUba3fBuZv3cYGRmQe4dt1H6sUq qmKj1/G8GKjR92WpCYrTV2VBOr+Qq/tpHuTTuEZEbqypeEMNool04aKm7S6s/kvmLl7W RqHA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773568270; x=1774173070; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=77D+4YPF7YI883VPLHb7vbn1uhWMx1l7wlwdXEKJrEY=; b=XVzdDoShmVKsNsPuYvjbEJbyf5I/rnthSCn58gfEBZSehsUpXd14B7Hw+MfLaSeQvm wNgTJO6QO+0D3osWLWJbKQQ9LwDhjTr5JZ3fgHiXrHD2WlTZaZNXcaBqlbRbOyyhuaec Loc0HjiXwHDudk+rN6cGQ+/LWHiSDkNAyKvtrzP5g63hCTWMcffWgVnLkR0PiNFlGN7z PsxAqA9UXxY2r4+YdwaL/pRCmA0cj+mwO0Pd5Cs+sJa0+XGhXAzURmERZLNVlXiBebV5 Rr7VEe/R2nmjqYJlylu4esmN5zMIYav6CooZR8q0T1AI58ZcFXwWnouVXut5K1e39usI mXBQ== X-Gm-Message-State: AOJu0YzNa43k7iXzf7za0/ukcJDvlb6EmXBFm7i9hcAZpJU5yFkVXEjF dQJaCyyZRtvBgQEuPtPX7X0dSd+FppRM4Azq83libLh9WOTh+87dMtEDq/O/c2AOsmRcVrC/V3E MrawFeVJCStPFt05/cug7vP2gd/VnS/NWv57TgNtMRLPYa6VB1LRb0g== X-Gm-Gg: ATEYQzxpvEzi0EKjx6/a3Ha3OLwWpf5oZ/JRwWK4DQ0h+XacgT9cJwlNWYH+sLc2aE9 v0wbjzrGyX7Jzo7216K+wCLGlQKiB3cOSo5ZQnwqJqPf4saa+ltfwCQB18OCDLIm/Fy9Q0lubhh A/yr5V/hGSk9Bekwn1Az8FnGk+FPWuFnOhzkGfxHsq6lQ77BAyqPJPDoAJvvu+78PNZnnMiKH92 io0jDCEmSHKqi4KaH5H6eNhBXG4aeB8z8GDhXpedkhMozPvj8ohivxkWfxkAlws4elJnyiFjas8 TYAXT/1D X-Received: by 2002:a05:6402:26ce:b0:663:48e4:570 with SMTP id 4fb4d7f45d1cf-663bac09326mr4414034a12.21.1773568269306; Sun, 15 Mar 2026 02:51:09 -0700 (PDT) MIME-Version: 1.0 References: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au> In-Reply-To: <43388977a1cdbde81043c690b41e0647e9b95a2b.camel@tpg.com.au> From: Francisco Olarte Date: Sun, 15 Mar 2026 10:50:33 +0100 X-Gm-Features: AaiRm51x5a0qgMYaYihM2W_Bf_4uMWbFoKHP8H_IlxNyCsD4kcoYjGxedbprUkU Message-ID: Subject: Re: Using \copy to populate a table To: rob stone Cc: PostGreSQL MailingList Content-Type: multipart/alternative; boundary="000000000000ad5ccb064d0d0a65" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ad5ccb064d0d0a65 Content-Type: text/plain; charset="UTF-8" Hi Rob: On Sun, 15 Mar 2026 at 08:12, rob stone wrote: > > This is what is on line 2 of the file:- > C|0|pageloader|||null|null|null|null|t|f|f|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. > It does not for me, but syntax for INSERT and COPY ( \copy just does a COPY from stdin ) is different. > 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? > Have you tried a little RTFM? specifically .... https://www.postgresql.org/docs/18/sql-copy.html Where somewhere it says: NULL Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format. If you generate four text files I would recommend switching to \N, it will be less painful. Francisco Olarte. --000000000000ad5ccb064d0d0a65 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Rob:

On Sun, 15 Mar 2026 at 08:12, r= ob stone <floriparob@tpg.com.au= > wrote:
=
This is what is on line 2 of the file:-
C|0|pageloader|||null|null|null|null|t|f|f|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.

It does not for me, but syn= tax for INSERT and COPY ( \copy just does a COPY from stdin ) is different.=
=C2=A0
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?
<= /blockquote>

Have you tried a little RTFM?

specific= ally ....

https://www.postgresql.org/docs/18/sql-copy.html

Where so= mewhere it says:

NULL

Specifies the string that represents a null value. The default= is \N (backslash-N) in text format, a= nd an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases=20 where you don't want to distinguish nulls from empty strings. This=20 option is not allowed when using binary format.


If you generate four text files I would recommend= switching to \N, it will be less painful.

Francisco Olarte.=
--000000000000ad5ccb064d0d0a65--