Timestamp conversion Error in dynamic sql script

From: sud <suds1434(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Timestamp conversion Error in dynamic sql script
Date: 2024-04-02 05:38:03
Message-ID: CAD=mzVVHrGgJ08SH8pECjTAH1EKE26ZdL26OjW3mcXhM-AZi2A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2024-04-02 08:08:44 Re: Timestamp conversion Error in dynamic sql script
Previous Message sud 2024-04-01 20:11:40 Re: Not able to purge partition