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 1t3GH3-00BWiC-Tb for pgsql-admin@arkaria.postgresql.org; Tue, 22 Oct 2024 14:54:18 +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 1t3GH1-000Qeh-DS for pgsql-admin@arkaria.postgresql.org; Tue, 22 Oct 2024 14:54:15 +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.94.2) (envelope-from ) id 1t3GH1-000QeZ-0w for pgsql-admin@lists.postgresql.org; Tue, 22 Oct 2024 14:54:15 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t3GGz-002PJJ-54 for pgsql-admin@lists.postgresql.org; Tue, 22 Oct 2024 14:54:14 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-5ebc9bda8c8so906524eaf.0 for ; Tue, 22 Oct 2024 07:54:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729608851; x=1730213651; darn=lists.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=UoABO8rgcpd5wAXfJQbQY07yzepfJFrSLLj9d2ZX1dg=; b=HjX2ZxI0llnUS9qfau/BBRBJFkWkkftipqfbXtRx7Ua+imQO7tRSUwX32zHpOszZxk NYm3JgHvore4L+onKqtWFh5wAB5wRrK1StDlCgBxHXbKfpnGU4PwOwRwDRIw3PZ9HDmc Sn0H32YW1b+GXfHhmz1F8ZCOagomzDS9oJEwxM97t7YAD+6Wt0ECX+IqKLGhK03wU/s3 6gIEOHOJfebnImBYgu/Hkols+iZhuu+jgkl1k2Db6NJZ7BPVbNST3Bd2RsmR0bs1ohN8 ES/mKNx44pfm1zhIEVGGefV8miDjht+MVUGIbYq3iUXphGJaaS+EiKhJFIxAKEni38KE VaGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729608851; x=1730213651; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=UoABO8rgcpd5wAXfJQbQY07yzepfJFrSLLj9d2ZX1dg=; b=oS0cyR5gzDdxeGp05AboDAJoiYeC/7Xuc2PtL3GyCYO3qA3Sb3bX6bAILeuSsvaE5b WOZZDb7kzL147vnHSEVo5YnKys7KVeRh3fH6IYPRgM89GEhARtxYEwdQ3lEpVmhNSUIG Cqrr1x1rU0DLWihU+AEjRRQZOfj8g8mo4ph8XJfhN1NzFX3xjhkkt3AFXDAc8oayrhhD eWDbukLqOAU+VeQ98qKyz4yuHlAaYdIjc6wcyCNIKVrBYdbv7Xc50mKeGppWClPHCrOb ffLW++aPJbi5+ueybcBSaWof7lMr1yLWUeLysV6/ihO3tt1QBKjQe73TWZaRSH95BMsx U1lA== X-Gm-Message-State: AOJu0Yy5/B16Y/KhpPy/4e9OySdlGYpXpUCsISpL4hJfbWBKBQZ3laTm 3K0uSwpMJ4XyEryjPJNuzoIZaUTf84BLhy2nhNhIshWPenK3gcKBhNBL9zYWt3y9g7OAjxfm6Ei cSG2u7Vlr4n4EghZhV49fO7rKq9gG/t/Z X-Google-Smtp-Source: AGHT+IGnSq12d0gp05i59L5JRI8cIpGDkqxzveBM5Z12g6mCuwc66e5e3RKdx0T9pJNkh21wdEVl06mzX3Vr+cRGzMY= X-Received: by 2002:a05:6870:2006:b0:277:f14c:9c0f with SMTP id 586e51a60fabf-2892c528328mr12716007fac.32.1729608850946; Tue, 22 Oct 2024 07:54:10 -0700 (PDT) MIME-Version: 1.0 References: <7bc2cd63-dff7-46bf-9cca-ee950463f13a@quadratum-braccas.com> In-Reply-To: <7bc2cd63-dff7-46bf-9cca-ee950463f13a@quadratum-braccas.com> From: "David G. Johnston" Date: Tue, 22 Oct 2024 07:53:34 -0700 Message-ID: Subject: Re: ERROR Inserting into partition To: Sbob Cc: "pgsql-performance@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002941fa062511f192" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002941fa062511f192 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Oct 22, 2024 at 7:36=E2=80=AFAM Sbob w= rote: > > INSERT INTO part_schema.cust_info_5 > ( cust_last_name) > SELECT l_name from public.global_addr WHERE l_name =3D 'NELL'; > ERROR: new row for relation "cust_info_5" violates partition constraint > DETAIL: Failing row containd (null, null, null, null, null, 'NELL', > null, null, null, null, null,null). > > Also, I am unable to reproduce this in development: CREATE TABLE cust_part ( cust_int_id bigint, cust_ext_id bigint, cust_tax_id varchar(20), cust_dob date, cust_dob_str varchar(20), cust_last_name varchar(100), cust_first_name varchar(100), cust_middle_name varchar(100), cust_addr1 text, cust_city text, cust_state text, cust_zip varchar(5) ) PARTITION BY LIST (cust_last_name); CREATE TABLE cust_info_5 PARTITION OF cust_part FOR VALUES IN ('NELL'); create table global_addr (l_name text); insert into global_addr values ('NELL'); INSERT INTO cust_info_5 ( cust_last_name) SELECT l_name from global_addr WHERE l_name =3D 'NELL'; David J. --0000000000002941fa062511f192 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Oct 22, 2024 at 7:36=E2=80=AFAM Sbob <sbob@quadratum-braccas.com> w= rote:

INSERT INTO part_schema.cust_info_5
( cust_last_name)
SELECT l_name from public.global_addr WHERE l_name =3D 'NELL';
ERROR:=C2=A0 new row for relation "cust_info_5" violates partitio= n constraint
DETAIL:=C2=A0 Failing row containd (null, null, null, null, null, 'NELL= ',
null, null, null, null, null,null).


A= lso, I am unable to reproduce this in development:

CRE= ATE TABLE cust_part (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 cust_int= _id =C2=A0 =C2=A0 bigint,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cust_= ext_id =C2=A0 =C2=A0bigint,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cus= t_tax_id =C2=A0 varchar(20),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cu= st_dob =C2=A0 =C2=A0 =C2=A0 =C2=A0date,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0cust_dob_str =C2=A0 varchar(20),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 cust_last_name =C2=A0varchar(100),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0cust_first_name =C2=A0 varchar(100),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 cust_middle_name =C2=A0varchar(100),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0cust_addr1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0tex= t,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 cust_city =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 text,
=C2=A0 =C2=A0 =C2=A0 =C2=A0= cust_state =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0t= ext,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 cust_zip =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(5)
) PARTITION BY LIST= (cust_last_name);

CREATE TABLE cust_info_5
PARTITION OF cust_par= t
FOR VALUES IN ('NELL');

create table global_addr (= l_name text);

insert into global_addr values ('NELL');
INSERT INTO cust_info_5
( cust_last_name)
SELECT l_name from global= _addr WHERE l_name =3D 'NELL';

David J.
= --0000000000002941fa062511f192--