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:
