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 1rrWri-0018nO-Fw for pgsql-general@arkaria.postgresql.org; Tue, 02 Apr 2024 05:39:23 +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 1rrWqg-004xpe-T5 for pgsql-general@arkaria.postgresql.org; Tue, 02 Apr 2024 05:38:19 +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 1rrWqg-004xpV-Dm for pgsql-general@lists.postgresql.org; Tue, 02 Apr 2024 05:38:19 +0000 Received: from mail-vs1-xe2f.google.com ([2607:f8b0:4864:20::e2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rrWqe-0006JX-9d for pgsql-general@lists.postgresql.org; Tue, 02 Apr 2024 05:38:18 +0000 Received: by mail-vs1-xe2f.google.com with SMTP id ada2fe7eead31-476cec93716so1322869137.2 for ; Mon, 01 Apr 2024 22:38:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712036294; x=1712641094; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=LjeBjb9xdYDws6OHWAJHYE29e7ulz4oGlpIlNxaTuCM=; b=FalDzsJ4BjnrBXbQ9mFOGxXK7vw+qcxrrRBEJexmNz5tVyzkRwTUynYo9GyhRxedp4 ULhhhfx/pltCN+4EP/NjzhDXUa+tTkde59Efs7LRzPVM0hhq7GmBRhfRFU58A12TAz3B awJNt1InY/OCvlSN80b+opdSLFo/kgZXA5QrKsc7JoQONiwhaPIQWGqxQDQQ/QLwlw0S YqiOoWDe4L0olbEA41c3KHZF7iua2oIIb46B7jQNY85d/QUgdhptQK0L7EMCxEJDCdrp GaKfNOaYykG2+4hiZSiVA3pSX5Vw38rY5DsU9kBtz7GiWb9Yk27TsnbV0eX3mW3E/yZC vRLA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712036294; x=1712641094; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=LjeBjb9xdYDws6OHWAJHYE29e7ulz4oGlpIlNxaTuCM=; b=m/UNp5VdbfFp3zsDdwhBhE32S7nbXbVpvw2Ja96+ATZDp+sy+1ISskJoG0FcX6wnlw XxN88lStJouU4tPTnhcLVR7Unsud5dBwwA2vrdW57iLgtMG9mrwJHLvZUsskORdcSD1o orbLewm6uG/NGZv3bC8YLr9sAfghQXwxQSq0SP7/KBAh2Z7Q+0URGNx8UeWzvWnE1i62 mHlXLgT2L4VPwFlKKIsF0RYq421SgpHsgoxkVBDX/Qcx6klod7s59U+wu33fnJDQZGM2 FM9/rM1HtVbop1wKh4PELnTadR85ghOzSe/D+Uz4dK7idQj2vhTdA9/2FMCyYJZUZmuU NV3A== X-Gm-Message-State: AOJu0Yxh9FBPGy/tbKb8nFC2ccMZdzBOYuKwC7wt8T0V8Kkp3rBUElPB Z3MHJQNOTU1Irz+Xs5qUj/fQGXulr2Q7FsK4qs9vahzB8Axwv/nDqpn9csKt0w+A+izhPmwQdxq cRV1g+RcTidspUmGr1HhZ2qej578SLkIwYew= X-Google-Smtp-Source: AGHT+IFwBhU9SM5ObFdATWIO/jo4LnflBqu2sW3dc+4P/ekF6WE5EtQn+YpoaBaNoPE8kT2Yre+J2RUBObXN+xSuj4Y= X-Received: by 2002:a67:b342:0:b0:478:3c4f:c0c3 with SMTP id b2-20020a67b342000000b004783c4fc0c3mr8632324vsm.27.1712036294654; Mon, 01 Apr 2024 22:38:14 -0700 (PDT) MIME-Version: 1.0 From: sud Date: Tue, 2 Apr 2024 11:08:03 +0530 Message-ID: Subject: Timestamp conversion Error in dynamic sql script To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002f84980615168342" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002f84980615168342 Content-Type: text/plain; charset="UTF-8" Hello , I am trying to create a block which will create a few partitions dynamically and also insert ~1million rows into each of those partitions. Not able to figure out why it's giving below error during timezone conversion while defining the partitions even though I used the typecast? CREATE TABLE parent_table ( id Numeric, col1 TEXT, col2 TEXT, partition_key TIMESTAMP, primary key (partition_key, id) ) PARTITION BY RANGE (partition_key); ********** DO $$ DECLARE start_date TIMESTAMP := '2022-01-01'; begin FOR i IN 0..10 LOOP EXECUTE format(' CREATE TABLE parent_table_%s ( CHECK (partition_key >= DATE ''%s'' AND partition_key < DATE ''%s'' ) ) INHERITS (parent_table);', TO_CHAR(start_date + i, 'YYYY_MM_DD'), TO_CHAR(start_date + i, 'YYYY-MM-DD')::timestamp , TO_CHAR(start_date + i + INTERVAL '1 day', 'YYYY-MM-DD')::timestamp ); EXECUTE format(' ALTER TABLE parent_table ATTACH PARTITION parent_table_%s FOR VALUES FROM (''%s'') TO (''%s'');', TO_CHAR(start_date + i, 'YYYY_MM_DD'), TO_CHAR(start_date + i, 'YYYY-MM-DD')::timestamp, TO_CHAR(start_date + i + INTERVAL '1 day', 'YYYY-MM-DD') ::timestamp ); END LOOP; FOR i IN 0..10 LOOP EXECUTE format(' INSERT INTO parent_table_%s (id,col1, col2, partition_key) SELECT generate_series(1, 1000000), md5(random()::text), md5(random()::text), TIMESTAMP ''%s'' + INTERVAL ''%s days'' FROM generate_series(1, 1000000);', TO_CHAR(start_date + i, 'YYYY_MM_DD'), start_date, i ); END LOOP; END $$; *********** *SQL Error [42883]: ERROR: operator does not exist: timestamp without time zone + integerHint: No operator matches the given name and argument types. You might need to add explicit type casts.Where: PL/pgSQL function inline_code_block line 7 at EXECUTEError position:* --0000000000002f84980615168342 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello ,
I am trying to create a block which will creat= e a few partitions dynamically and also insert ~1million rows into each of = those partitions. Not able to figure out=C2=A0why it's giving below err= or during timezone conversion=C2=A0while defining the partitions even thoug= h I used the typecast?=C2=A0

CREATE TABLE parent= _table (
id Numeric,
col1 TEXT,
col2 TEXT,
p= artition_key TIMESTAMP,
primary key (partition_key, id)
)
PARTITIO= N BY RANGE (partition_key);

=
**********

<= /span>DO $$
DECLARE
start_date TIMESTAMP :=3D '2022-01-01&= #39;;
begin
FOR i IN 0..10 LOOP

EXECUTE format('
=C2=A0= =C2=A0 CREATE TABLE parent_table_%s (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CHECK= (partition_key >=3D DATE ''%s'' AND partition_key < = DATE ''%s'' )
=C2=A0 =C2=A0 ) INHERITS (parent_table);&#= 39;,
=C2=A0 =C2=A0 TO_CHAR(start_date + i, 'YYYY_MM_DD'),
=C2= =A0 =C2=A0 TO_CHAR(start_date + i, 'YYYY-MM-DD')::timestamp ,
= =C2=A0 =C2=A0 TO_CHAR(start_date + i + INTERVAL '1 day', 'YYYY-= MM-DD')::timestamp =C2=A0
);
EXECUTE format('
ALTER TABLE = parent_table ATTACH PARTITION parent_table_%s
FOR VALUES FROM (''= ;%s'') TO (''%s'');',
TO_CHAR(start_date + i= , 'YYYY_MM_DD'),
TO_CHAR(start_date + i, 'YYYY-MM-DD')::= timestamp,
TO_CHAR(start_date + i + INTERVAL '1 day', 'YYYY-= MM-DD') ::timestamp
);

END LOOP;

=C2=A0 FOR i IN 0..10= LOOP
=C2=A0 =C2=A0 EXECUTE format('
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = INSERT INTO parent_table_%s (id,col1, col2, =C2=A0partition_key)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ge= nerate_series(1, 1000000),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5= (random()::text),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5(random()= ::text),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TIMESTAMP ''%= s'' + INTERVAL ''%s days''
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 FROM generate_series(1, 1000000);',
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 TO_CHAR(start_date + i, 'YYYY_MM_DD'),
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 start_date,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 i
=C2=A0 =C2=A0 );
= END LOOP;
END $$;

***********
SQL Error [42883]: ERROR: ope= rator does not exist: timestamp without time zone + integer
Hint: No ope= rator matches the given name and argument types. You might need to add expl= icit type casts.
Where: PL/pgSQL function inline_code_block line 7 at EX= ECUTE

Error position:
--0000000000002f84980615168342--