Subject: Re: Timestamp conversion Error in dynamic sql script

From: sud <[email-masked]>
Date: Wed, 3 Apr 2024 01:38:39 +0530

On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <[email-masked]>
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: *

---

From: Erik Wienhold <[email-masked]>
Date: Tue, 2 Apr 2024 22:57:35 +0200

On 2024-04-02 22:08 +0200, sud wrote:
> [...]
> [...]

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

---

From: sud <[email-masked]>
Date: Thu, 4 Apr 2024 01:39:32 +0530

This one worked. Thank you so much.