Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Fri, 06 Feb 2026 09:58:43 -0300
Message-Id: <DG7WARDX1TY0.2VX3Q7QHSCXE9@gmail.com>
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "jian he" <jian.universality@gmail.com>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>
Cc: "torikoshia" <torikoshia@oss.nttdata.com>, "Masahiko Sawada"
 <sawada.mshk@gmail.com>, "vignesh C" <vignesh21@gmail.com>, "Jim Jones"
 <jim.jones@uni-muenster.de>, "Kirill Reshke" <reshkekirill@gmail.com>,
 "Fujii Masao" <masao.fujii@oss.nttdata.com>, "David G. Johnston"
 <david.g.johnston@gmail.com>, "Yugo NAGATA" <nagata@sraoss.co.jp>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com> <CALDaNm0FUtPjH9wz5dgwOBNtsAXEZj=0-TYGsyQXchQy-hXGrw@mail.gmail.com> <CACJufxFpm2Gzx4AD9qKtiQSiiiunX02wNTxu0JoFm7nEKF2KUw@mail.gmail.com> <CAD21AoBjmEYjZT9A6P-vAuwboiiYtXaMc12pX2ySmh3RXi=v8w@mail.gmail.com> <CACJufxF6_YwAboiCaVYLRtNpO4kGbXqkXzH_7W=pUvrNXK8WuQ@mail.gmail.com> <CAD21AoBWwEzpp3Z6tp-O-AQGftK-kuj6vRva2L5daWoWBtbnRg@mail.gmail.com> <CACJufxGEHmijmP-QqvrmqU6cxmhgpdjY7ewQBQ=E9NmdyEcqmw@mail.gmail.com> <f5ebfa4b318e97c41c0919304b94c77f@oss.nttdata.com> <CACJufxG=em0PHZvy1EAZ+vxPZ8UA68MfQ-Hji+h+WgnWNpqmVQ@mail.gmail.com> <CACJufxF0c3k5O8up9NOY-m02nyJ0f6N1tKxZwjCewTqvvFmbLw@mail.gmail.com> <CACJufxHr-LBV2pB9m64mA=1pgVMM4LvUn+-MzpeViWV=Ks_cyg@mail.gmail.com> <901967e5-e5dc-42c6-b2bf-fb3a49d7e787@gmail.com> <CACJufxEs_VBV39gYHnpLFOMcUaUD-ADAst_ePTCgmoDR8O=ekg@mail.gmail.com> <DFUHPCSP86G0.11VU7FGWRRXM2@gmail.com> <CACJufxGYPXQ_Jz1avF5eSh_XJRsxhPSUZ+=RzG3Hz4_XNAc32g@mail.gmail.com>
 <4c540fe3-495c-4bbf-8dcf-2c1e2b88bc3d@gmail.com>
 <CACJufxHFwQMw1As+QFk+fA7S8ZxRG2wOvHcvmsWuj2XJ+W6d_A@mail.gmail.com>
In-Reply-To: <CACJufxHFwQMw1As+QFk+fA7S8ZxRG2wOvHcvmsWuj2XJ+W6d_A@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/DG7WARDX1TY0.2VX3Q7QHSCXE9%40gmail.com>
Precedence: bulk

On Fri Feb 6, 2026 at 12:40 AM -03, jian he wrote:
>> Yeah, after some more thinking it seems ok to use both options
>> together. I just found a bit strange when using integer columns.
>> Consider this example:
>>
>> cat data.csv
>> 1,11
>> 2,22
>> 3,
>> 4,44
>>
>> postgres=3D# create table t(a int not null, b int);
>> CREATE TABLE
>>
>> postgres=3D# copy t from
>> '/Users/matheus/dev/pgdev/copy-on-error-set-null/data.csv' with
>> (FORCE_NOT_NULL(b), format csv, delimiter ',',  ON_ERROR set_null);
>> NOTICE:  1 row was replaced with null due to data type incompatibility
>> COPY 4
>>
>> postgres=3D# select * from t where b is null;
>>   a | b
>> ---+---
>>   3 |
>> (1 row)
>>
>> We are requiring a not null value on column b but we are still
>> generating rows with null values on b.
>>
>> The reasoning on this is that the row 3 would generate a "invalid
>> input syntax for type integer" error and the ON_ERROR set_null fix
>> this by inserting a NULL value. It make sense I think but I'm
>> wondering if it could cause any confusion?
>>
>
> After careful reading the FORCE_NOT_NULL, FORCE_NULL option.
> It's about dealing with empty value and NULL strings.
>
> copy t from stdin with(FORCE_NOT_NULL (b), format csv, delimiter ',');
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself, or an EOF signal.
>>> 1,
>>> \.
> ERROR:  invalid input syntax for type integer: ""
> CONTEXT:  COPY t, line 1, column b: ""
>
> in this case, FORCE_NOT_NULL will convert the empty value to null
> string (empty double quote)
>
> another FORCE_NULL.
> copy t from stdin with(FORCE_NULL (b), format csv, delimiter ',');
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself, or an EOF signal.
>>> 1,""
>>> \.
> COPY 1
> src9=3D# table  t;
>  a | b
> ---+---
>  1 |
> (1 row)
>
> In this case, FORCE_NULL will convert null string (empty double quote) to=
 NULL.
>
> ON_ERROR explanation, the first sentence:
> """
> Specifies how to behave when encountering an error converting a
> column's input value into its data type.
> """
> FORCE_NULL, FORCE_NOT_NULL is a special handling of input value, ON_ERROR=
 is
> about converting the input value to data type, so it's before ON_ERROR.
>
> Overall the current doc is fine, IMHO.
>
Yeah, after also reading more carefully it's sounds correct to me too.

> The attached patch has addressed your other points.
>
Thanks, overall the patch looks good to me. I'm attaching a diff with
just some small tweaks on documentation and error messages. Please see
and check if it's make sense.

I'm wondering if we should have an else if block on
CopyFromTextLikeOneRow() when cstate->cur_attval is NULL to handle
COPY_ON_ERROR_SET_NULL when log_verbosity is set to
COPY_LOG_VERBOSITY_VERBOSE

if (cstate->opts.on_error =3D=3D COPY_ON_ERROR_IGNORE)
    ereport(NOTICE,
            errmsg("skipping row due to data type incompatibility at line %=
" PRIu64 " for column \"%s\": null input",
                   cstate->cur_lineno,
                   cstate->cur_attname));
+ else if (cstate->opts.on_error =3D=3D COPY_ON_ERROR_SET_NULL)
+     ereport(NOTICE,
+             errmsg("setting to null due to data type incompatibility at l=
ine %" PRIu64 " for column \"%s\": null input",
+                    cstate->cur_lineno,
+                    cstate->cur_attname));

--
Matheus Alcantara
EDB: https://www.enterprisedb.com