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.94.2) (envelope-from ) id 1tgdlU-00CMYH-DT for pgsql-bugs@arkaria.postgresql.org; Sat, 08 Feb 2025 05:52:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgdlS-007YgK-Ra for pgsql-bugs@arkaria.postgresql.org; Sat, 08 Feb 2025 05:52:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgdlS-007YgB-JD for pgsql-bugs@lists.postgresql.org; Sat, 08 Feb 2025 05:52:26 +0000 Received: from mail-pl1-x643.google.com ([2607:f8b0:4864:20::643]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tgdlQ-003tfl-1Q for pgsql-bugs@lists.postgresql.org; Sat, 08 Feb 2025 05:52:25 +0000 Received: by mail-pl1-x643.google.com with SMTP id d9443c01a7336-21670dce0a7so66043645ad.1 for ; Fri, 07 Feb 2025 21:52:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738993944; x=1739598744; darn=lists.postgresql.org; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=U3oV2DzU5jYraHZ7oKZMrQQqfgz6dWCs9TRIxPXt6CA=; b=asCeIfeIiKuIItxZqDovRgN3LfMgy9TF7OWBCMuv150P6lQSyuU7VeEZipkByRA3eL AtXpKppZupXUJ8FFdHaTl23kpcrYRpXj/JRrg4PTJ7iwf/Z1SiMKOHUKfKLNuNZRJ9gZ YTUkdguQOXaIMnlUwvPio7rUQOnaX7Fs/mnoZKDkdmotJSerP1gLOfwKpm46ulSHVTTU rqtqQXvqSmJ14io4xxJ0rcZdPqpWkdA3K5Z+59onbbIzox+Ijvpxnlt7PcLksSJQkP5J 2UQQ99xlFnGf5LeCHX6OpPxITcvkHBlT1n5ZQ2EOsGhDbh9HB9avCYJpCDg/hDDAAvj5 5mqQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738993944; x=1739598744; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=U3oV2DzU5jYraHZ7oKZMrQQqfgz6dWCs9TRIxPXt6CA=; b=fRiGClGFpKxAedwbZ+5sy2UXZDj8bEVHLkJYOz8nfQDF+7BBHlmS5KFWWWLsdNz6Zw zjNKXw5oy1EeEHdXbK7irahZxWL0yW+wLAdBuxtADsqZ3B9qwi+7baY3fLqr2N83KGtF ZHcS06JQkSjETb+oImLkISa67tiZpZAbb0INXEuq6ZZzhs5YIlmJCW8j+OAOn3GqYjKJ 1KtLnohoLrAXpoF+ahq84ttnq7KGhvJIj+XieZRMtG8jQSlNxdnry8AIsrFslv5FFhSt J50Ax0a4ULQAYnFROQyhOPKpeX/B8hRYjHXr+0gLcZcYKHOT+MATkxQCE2kpG/w34AbO rZxQ== X-Gm-Message-State: AOJu0YzxxuB3m1mmZccx3GZP5DcprV9sz2G739JXEz2LmHnHTwQOkRQE xZzV7fMsF43hBePnKEGCc+BVO1+r46x3s/IymDeaNKvOrrsiprTqv88htVxJNEayIw== X-Gm-Gg: ASbGnctqtlBqDHGN5jtSdA8R4PTjZ6chBFWERm3KhJD53C1IaaFKIZCuDWdKJFpVLql aFRxiu/KYzOWkg+jKGTyy0dX25Y4ugCSrYfhZnO0g40nTAECw87w2AkndGL1peFAkVYlvkW1AK6 YieK1/ukF5Q4CnqRKJDwI7N12E8ouQ7rlSMtLvSbwiL7EKIPjNnw50V3zvYoIkWoDkDvbCMdrAr dIYDMGXZpy2609HXoJ8AW0jbI2oM8qS+zW+jbmcqUetDwivKlW2y9YFz4Lrm1UzuntMawr86zx5 1qUT5w0u8fBr8wpzMZ25 X-Google-Smtp-Source: AGHT+IGMj31P7na4hs9w1RDMEKXyRrGUhqo9hACfaqvVdJvhUVEHHllyRV9TYNPp96fJWBBCLmExrg== X-Received: by 2002:a17:902:ce90:b0:216:2477:e4d3 with SMTP id d9443c01a7336-21f4e7f2c46mr94462625ad.51.1738993943506; Fri, 07 Feb 2025 21:52:23 -0800 (PST) Received: from [127.0.0.1] ([2406:da18:9e5:1502:726c:cbf2:7e1f:8825]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-21f368afde8sm39814235ad.223.2025.02.07.21.52.21 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Feb 2025 21:52:23 -0800 (PST) Date: Sat, 8 Feb 2025 13:52:14 +0800 From: Zhang Mingli To: pgsql-bugs@lists.postgresql.org, me nefcanto Message-ID: In-Reply-To: References: Subject: Re: Bug in copy X-Readdle-Message-ID: fa5c934f-9b57-4900-ad15-bb9c6d9d414d@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="67a6f114_7b48073b_1738b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --67a6f114_7b48073b_1738b Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On =46eb 8, 2025 at 13:28 +0800, me nefcanto , wrote= : > Hello > I run this command: > copy =22Parts=22 (=22Id=22,=22Title=22) from stdin with (format csv, de= limiter =22,=22, on=5Ferror ignore) > But I receive this error: > duplicate key value violates unique constraint =22PartsUniqueLocaleTitl= e=22 > This means that the on=5Ferror setting is not working. When I try to in= sert a million records, this becomes extremely annoying and counterproduc= tive. > When we specify that on=5Ferror should be ignored, any type of error in= cluding data type inconsistency, check constraint inconsistency, foreign = key inconsistency, etc. should be ignored and Postgres should move to the= next record and not fail the entire bulk operation. > RegardsSaeed Nemati Hi, As my understanding,=C2=A0=C2=A0on=5Ferror=C2=A0is designed to handle err= ors during data type conversions in PostgreSQL, similar to what we do in = Greenplum or Cloudberry. Since these rows are valid,=C2=A0on=5Ferror=C2=A0doesn=E2=80=99t raise an= y concerns. -- Zhang Mingli HashData --67a6f114_7b48073b_1738b Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On =46eb 8, 2025 at 13:28 +0800, me nefcanto <sn= .1361=40gmail.com>, wrote:
Hello
I run this command:
copy =22Parts=22 (=22I= d=22,=22Title=22) from stdin with (format csv, delimiter =22,=22, on=5Fer= ror ignore)
But I receive this err= or:
duplicate key value vi= olates unique constraint =22PartsUniqueLocaleTitle=22
This means that the on= =5Ferror setting is not working. When I try to insert a million records, = this becomes extremely annoying and counterproductive.
When we specify that o= n=5Ferror should be ignored, any type of error including data type incons= istency, check constraint inconsistency, foreign key inconsistency, etc. = should be ignored and Postgres should move to the next record and not fai= l the entire bulk operation.
RegardsSaeed Nemati

Hi,

As my understanding,&=23160;&=23160;on=5Ferror&=23160;is des= igned to handle errors during data type conversions in PostgreSQL, simila= r to what we do in Greenplum or Cloudberry.&=23160;
Since these rows are valid,&=23160;on=5Ferror&=23160;doesn=E2= =80=99t raise any concerns.

--
Zhang Mingli
HashData
--67a6f114_7b48073b_1738b--