public inbox for [email protected]  

help / color / mirror / Atom feed

Timestamp conversion Error in dynamic sql script
2+ messages / 2 participants
[nested] [flat]

* Timestamp conversion Error in dynamic sql script
@ 2024-04-02 05:38  sud <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: sud @ 2024-04-02 05:38 UTC (permalink / raw)
  To: pgsql-general <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Timestamp conversion Error in dynamic sql script
@ 2024-04-02 12:01  Erik Wienhold <[email protected]>
  parent: sud <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Erik Wienhold @ 2024-04-02 12:01 UTC (permalink / raw)
  To: sud <[email protected]>; +Cc: pgsql-general <[email protected]>

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-02 12:01 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-02 05:38 Timestamp conversion Error in dynamic sql script sud <[email protected]>
2024-04-02 12:01 ` Erik Wienhold <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox