Timestamp conversion Error in dynamic sql script

On Tue, 2024-04-02 at 11:08 +0530, sud wrote:

Not able to figure out why it's giving below error during timezone conversion
while defining the partitions even though I used the typecast? 

[...]
DECLARE
start_date TIMESTAMP := '2022-01-01';
[...]
    TO_CHAR(start_date + i, 'YYYY_MM_DD')

***********
SQL Error [42883]: ERROR: operator does not exist: timestamp without time zone + integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

As the error states, you cannot add an integer to a timestamp.

What is the supposed meaning of that addition?

Yours,
Laurenz Albe

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

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)

Also

0. Use TIMESTAMPTZ not TIMESTAMP

Cheers,
Greg

On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

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)

Also

0. Use TIMESTAMPTZ not TIMESTAMP

Thank you so much. That helped.

Now this block seems to be failing near the "LIKE" operator. Isn't it
allowed to add the check constraints along with the CREATE TABLE statement?

*SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
function inline_code_block line 8 at EXECUTE*
*Error position*

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 TIMESTAMPtz := '2022-01-01';
begin
FOR i IN 0..10 LOOP

EXECUTE format('
CREATE TABLE parent_table_%s (
CHECK (partition_key >= ''%s'' AND partition_key < ''%s'' )
* ) LIKE (parent_table including all);',*
TO_CHAR(start_date + make_interval(days=>i),'YYYY_MM_DD'),
(start_date + make_interval(days=>i))::timestamptz ,
(start_date + make_interval(days=>i))::timestamptz
);

EXECUTE format('
ALTER TABLE parent_table ATTACH PARTITION parent_table_%s
FOR VALUES FROM (''%s'') TO (''%s'');',
TO_CHAR(start_date + make_interval(days=>i),'YYYY_MM_DD'),
(start_date + make_interval(days=>i))::timestamptz ,
(start_date + make_interval(days=>i))::timestamptz
);

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),
''%s''
FROM generate_series(1, 1000000);',TO_CHAR(start_date +
make_interval(days=>i),'YYYY_MM_DD'),

(start_date + make_interval(days=>i))::timestamptz );

END LOOP;

END $$;

*SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
function inline_code_block line 8 at EXECUTE*
*Error position: *

On 2024-04-02 22:08 +0200, sud wrote:

On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

Now this block seems to be failing near the "LIKE" operator. Isn't it
allowed to add the check constraints along with the CREATE TABLE statement?

[...]

EXECUTE format('
CREATE TABLE parent_table_%s (
CHECK (partition_key >= ''%s'' AND partition_key < ''%s'' )
* ) LIKE (parent_table including all);',*
TO_CHAR(start_date + make_interval(days=>i),'YYYY_MM_DD'),
(start_date + make_interval(days=>i))::timestamptz ,
(start_date + make_interval(days=>i))::timestamptz
);

[...]

*SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
function inline_code_block line 8 at EXECUTE*
*Error position: *

The LIKE clause goes inside the parenthesis along with the column and
constraint definitions, i.e.:

CREATE TABLE parent_table_yyyy_mm_dd (
LIKE parent_table,
CHECK (...)
);

--
Erik

This one worked. Thank you so much.

On Wed, Apr 3, 2024 at 2:27 AM Erik Wienhold <ewie@ewie.name> wrote: