Re: Timestamp conversion Error in dynamic sql script

[sud <suds1434@xxxxxxxxx>]

On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <htamfids@xxxxxxxxx> 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: