From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | sud <suds1434(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Timestamp conversion Error in dynamic sql script |
Date: | 2024-04-02 12:01:44 |
Message-ID: | m5bfxgtwkn6vasomhh3h36frkehjapftu74ebxattsyajxgq4w@uhxjzqsnqzyu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2024-04-02 07:38 +0200, sud wrote:
> 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:*
Two ways to fix it:
1. Declare start_date as DATE when you want to add days with date + int
2. Keep TIMESTAMP and use start_date + make_interval(days => i)
--
Erik
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2024-04-02 14:16:04 | Re: Timestamp conversion Error in dynamic sql script |
Previous Message | Sándor Daku | 2024-04-02 09:48:18 | Re: Getting wrong datetime in database using insert into table query. |