Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ljt62-0004P0-Jc for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 00:32:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ljt60-0003nz-C8 for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 00:32:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ljt5z-0003nq-ND for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 00:32:56 +0000 Received: from sonic305-22.consmr.mail.ne1.yahoo.com ([66.163.185.148]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ljt5s-0007eK-EJ for pgsql-performance@postgresql.org; Fri, 21 May 2021 00:32:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621557166; bh=ewRDQfLMf64TU6ymss+vKWQyyi3YLyNU3pm5GoDqJrY=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=Fb/sJomIfIybjleurmVLHgZdaFZfySL2odqiTGr50cPXP8z+vhRBuYzn3t+iz7NsHHB3QHg5cuf1PzirfNXi6LL5ZiiJIGnqoBaCWqBpLZJ2TFTGScZALewSVxeKwZbLksqwlbI0I0LKnfDAGdw+mfD7aB/FN4+u2vpuAUMsSq6xzgAPuVuWz/uB4KvKMk7PDKNqVxfo3MevDVBbu72lIeJZ0iguz7mkgdYU/VXmPahWcekzs2Z6C6YN/9NLYlRwLoahdBi9cD0edsxGKZHrPDr27G6+hhKdXukTpH1Tb6Ex+w6suZ8lKd5CPcPdD1p4lLSxI1aZo+c61ZFvCtU1xQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621557166; bh=oQvzzKIbYbGpAv099yeuFySN1vnXswg0A77BXt1LThB=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=dkV6cSRD8P+S98OfNIsQctMG/2CYJyU92aYxGa/FrsvU6ilpBbz7AdUEt4soHIC7UN9dQVdJs1GyT68zVlt1T3ms4hGLYpiwnZKI0X/01Qixjlm8PE4Dagr690TPRd6vn/YcueMRAIqrGch/OFBP8gVoIDDRAWPGPY8QItFNNKgMzaQg1TwWwB0H0XgUAGJh0OqUcUF6orVJ6fO/AodFU4m0IQnwngNusUITFg6uth+7ZuenpJEKwcdmOPrtLcqIZ19JhrOzMuqoJP6V4ZjqzM+3LQ6tU1eR+e+hvsV0j7qXwHJFB27gPh8Aj1WUB+vJIwgYNxMwuiMkh6RkNiux0Q== X-YMail-OSG: Pkimg.UVM1ljl4OdIFzyYO2aHxTTqHaPt76UIYm4a6B77rmTkZmCsXvgMag6gGO VjYeKHUS3tjVEDkiDGq7B2jTxrnDcv9ZwMUNeshcLASWxRHHKHCZgRae4w_OkTcBIDfQcyfHlHrP M9BRjI1301y4zpwlyHDqhTu9lVmEOoJKyo09XcebSOwJCiFK1W2NsMniJ2np6ssi_tljJKgjwSWv XD8WCBbrTn.XCZyZokJtO4BtLchDnpVE0pQeDbrlEPgQJcbCrGwGd99KPEM_qx8GQO.BU8Oz51Eu B1cTQ74qzOKwxI9gBzI5ljBmRvER9hL_fdWsQyeaiFuCoPlz_uioQwhOQWb2ZvJCShmO7cRPey71 xCSx9IpVhWNAiigv9OnPBSQ3HP1vJPloUcPzrmCzagUbtzQhkBVtn5adoiC4xE.2RvAvkWdFCeOY fDG2XoOIAet39tEezj2UOXPIq.Dmc_ed61o.bPgEsNlvICxcniJQAL0NNs_y8iBXJm1GpwcFl2Pv CZg.aWAL.CJnX7PkK6WC41v_9uGXqV46qfsupoAbGojy_OwpCHydDA7YlaJTuDy.RSIJL7OxS_Tu aKvwT9IhmkjAjXmaZuKNjBkMsktkN.xYD3QQ6Y64t5wCmieQmm_U4t_.lfpUUPrNRWUS2hzlB31I yuJe.Be2CXEoR49Ss5o2cwfaa55GoCO0lItHVsTIeRL66bty5NuatssSW1qNACKBHjbA5Dct1dnV 69OjwxxoWRf0VhimQVGK8Pb6S9zNxkGgNqwFaCSMoxpHQGxxZCiiMqaVl28bO2iIr7dcX3ZdsKQU 3E4ynSTBummY3VHyHxtP_NwoeDVlbj4erKtb_o5OUM13xUkZBy3tMz81ZADAeO8c.qy5OCRAB5X8 Zz52eoFa0kiZnvZ9x0EC2IVMamsD6ltMEJUbgEBdJWkd.C7fl19kId63x.lg7gy3KsjbUTPRjG1e Hn3naR.0opaRMH027tmcnwF.UKZefnKgY80KSDvQc89Oc5tcujrQjEMY2E6T7vZ0yv3WsiNn.n76 ZikLOyQ0JOJ32uDhgfPUvtDI7INKeyNorJ0ifx7F9w6YX_GqPpjgDapIQvCx8qvlMLUIfrT0itoT nJlMClpBSo8WNagdiNBvyrVXqEHc_Kp1ibs.6xCSAIi87Y6tZSuNfQkbcW3FP.AVDMSYjvcn48Wp qzBAHt1OeDzAWG__OpGUMN9Igvhnmhbn8d9L15Vknat02RLUeylboFUX3WgoBo33ATxyxRj28_9z PMCrZcVNxLHcIqIP1KDDVlEcfVAqb9_HxQKcVYTLr2xuPATYZOO8h3ERi4LvTOPaQGvZPsBvq3q5 y8ZNJouEa9LNfjM_8eHq.OPJmJkb9Ia4prBf0gRBFUxVpvwsmyXIIOcyspAuUQtLLrA3QalDrevx KmsIwma41c8ioX47OwsAVnZNm9vGpJkzA2og.wBny_kUP8VY8W4Yl9wLFEjwUbZAZ7.hU_M9v6DN _LmdSaEyN2dQCasVsXPxFHmUJNMVL7.OGAiZI8hm798b1zWSZN5uS6VLbfZSjqyyzI38IsBszcM5 UW4rZ6mPf62nBJ_HJwFXb3AQY7o45VYApHVDxvnVeFQeUoNhF1VzWBg2x3adElG8kGZD_m4qJWYf oO9WpEWetkK84fsybPFrorn5cBSJuP6twMagl5a66mxDlyzhfLqr2uMZy3xbpvn64cdWugalTo94 vwkETAF2JIdDfnLuBuGCGfW9nQ7G2gFHiyqWjlkF3LXtte6YMol38t73C1rwLRPbWuT42Me1_52w rJ1N4qzQUFDRFJnL_KNXvuScDZCMhBMQTSL02bp6w0fm7JUG2iXZW3UKZRFQzZ.SZniLn1r0N3X8 spbHssolMaJaAqosr_4xckGpPCSwGMT._t5KSEO2NCnGZFNiqV9LHHIM.LvqZj6lKeFZTsEaYbEd n5DK9egur_gGEwDllxsksKl5PSmwgmFxoezSj1zJc05ZIX2ViujIX6cssSYpyMmdOz3uVfh6xESU uDnpAHlgzY2Y6ZjrDvWAJlO9dpu4PZtF7TQchkc60cjYp43pOcungGo6N.esdMujncD7.bPAvMhI gWiQrAjswrPEMHOzpSiqqAdXvLdH1G5n5Mv9RcUVSKAHU1yzzs2AQF1NfRYapnE73E5SNB9P2o5O RicWcTNIYPLfrQ1AzbUJ2G4A4ALJpWS_Pj9OdP4EDyWQ.OHJqYxwLXRPQNi.rSN2wh76Rc7RKzCR Hl.QrSnFJTuPC.FSFKsqj7kLvxLV2wpi3OPJGNhUMR86xt6QKgaqsqivl9SQ2HifGmnTLC7euys1 lyE13HxxS5nlXB_Z.OdmfWd2wjuLQy_ykRvjjTfwTpZOnN1eAPyD41eKEMpQS6e5cXpkexaQOMcx Jt_TQbuw4NCWI.Ir.R7wmh8fPcuXqnW9CUdNBSdTaEfGJfsEs6ZMQvp1ukDUWcN8hOC0EA.bl1i4 E X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 00:32:46 +0000 Date: Fri, 21 May 2021 00:32:11 +0000 (UTC) From: Nagaraj Raj To: Pgsql-performance Message-ID: <87840670.92362.1621557131080@mail.yahoo.com> Subject: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_92361_867324827.1621557131077" References: <87840670.92362.1621557131080.ref@mail.yahoo.com> X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 9195 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_92361_867324827.1621557131077 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, I am trying to create partitions on the table based on first letter of the = column record=C2=A0 value using inherit relation & check constraint. Somehow able to create and load the data into the tables as per my requirem= ent. But the problem is when querying the data on that partitioned column, it's = referring to all the children's tables instead of the matching table. create table t1(id int,name text); =C2=A0CREATE TABLE partition_tab.t1_name_null(=C2=A0CONSTRAINT null_check C= HECK (name IS NULL)) INHERITS (t1);=C2=A0CREATE or replace FUNCTION partiti= on_tab.func_t1_insert_trigger()=C2=A0RETURNS trigger=C2=A0LANGUAGE 'plpgsql= '=C2=A0COST 100=C2=A0VOLATILE NOT LEAKPROOFAS $BODY$DECLARE=C2=A0chk_cond t= ext;=C2=A0c_table TEXT;=C2=A0c_table1 text;=C2=A0new_name=C2=A0 text;=C2=A0= m_table1 text;=C2=A0BEGIN=C2=A0=C2=A0if ( NEW.name is null) THEN=C2=A0INSER= T into partition_tab.t1_name_null VALUES (NEW.*);=C2=A0elseif ( NEW.name is= not null) THEN=C2=A0new_name:=3D substr(NEW.name,1,1);=C2=A0raise info 'ne= w_name %',new_name;=C2=A0=C2=A0c_table :=3D TG_TABLE_NAME || '_' || new_nam= e;=C2=A0c_table1 :=3D 'partition_tab.' || c_table;=C2=A0m_table1 :=3D ''||T= G_TABLE_NAME;=C2=A0IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname= =3Dlower(c_table)) THEN=C2=A0RAISE NOTICE 'values out of range partition, c= reating partition table: partition_tab.%',c_table; =C2=A0chk_cond :=3D new_name||'%';=C2=A0=C2=A0raise info 'chk_cond %',chk_c= ond; =C2=A0EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name li= ke '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');'; =C2=A0END IF;=C2=A0=C2=A0EXECUTE 'INSERT INTO ' || c_table1 || ' SELECT(' |= | m_table1 || ' ' || quote_literal(NEW) || ').* RETURNING id;';=C2=A0END IF= ;=C2=A0RETURN NULL;=C2=A0END;$BODY$; CREATE TRIGGER t1_trigger=C2=A0BEFORE INSERT OR UPDATE=C2=A0ON t1=C2=A0FOR = EACH ROW=C2=A0EXECUTE PROCEDURE partition_tab.func_t1_insert_trigger() examples:=C2=A0Postgres 11 | db<>fiddle |=20 |=20 | |=20 Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | Any suggestions. Thanks,Rj =C2=A0 ------=_Part_92361_867324827.1621557131077 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

<= /div>
I am trying to create partitions on the table based o= n first letter of the column record  value using inherit relation &= ; check constraint.

Someho= w able to create and load the data into the tables as per my requirement.

But the problem is when que= rying the data on that partitioned column, it's referring to all the childr= en's tables instead of the matching table.

=
create table t1(= id int,name text);

 
CREATE TABLE p= artition_tab.t1_name_null
(
 CONSTRAINT null_check= CHECK (name IS NULL)
) INHERITS (t1);
 
CREATE or replace FUNCTION partition_tab.func_t1_insert_trigger()
 RETURNS trigger
 LANGUAGE 'plpgsql'
 = COST 100
 VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
 chk_cond text;
 c_table TEXT;
 c_table1 text;
 new_name  text;
=  m_table1 text;
 BEGIN
 
 = ;if ( NEW.name is null) THEN
 INSERT into partition_tab.t1_n= ame_null VALUES (NEW.*);
 elseif ( NEW.name is not null) THE= N
 new_name:=3D substr(NEW.name,1,1);
 raise = info 'new_name %',new_name;
 
 c_table :=3D T= G_TABLE_NAME || '_' || new_name;
 c_table1 :=3D 'partition_t= ab.' || c_table;
 m_table1 :=3D ''||TG_TABLE_NAME;
 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=3Dlower(c_t= able)) THEN
 RAISE NOTICE 'values out of range partition, cr= eating partition table: partition_tab.%',c_table;

=  chk_cond :=3D new_name||'%'; 
 raise info 'chk_co= nd %',chk_cond;

 EXECUTE 'CREATE TABLE partit= ion_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS = (' ||TG_TABLE_NAME|| ');';


 EN= D IF;
 
 EXECUTE 'INSERT INTO ' || c_table1 |= | ' SELECT(' || m_table1 || ' ' || quote_literal(NEW) || ').* RETURNING id;= ';
 END IF;
 RETURN NULL;
 END= ;
$BODY$;

CREATE TRIGGER t1_trigger
 BEFORE INSERT OR UPDATE
 ON t1
 F= OR EACH ROW
 EXECUTE PROCEDURE partition_tab.func_t1_insert_= trigger()


examples: 

=

Postgres 11 | db&= lt;>fiddle

Free = online SQL environment for experimenting and sharing.

=


Any suggestions.

=

Thanks,
Rj



=  

------=_Part_92361_867324827.1621557131077--