public inbox for [email protected]
help / color / mirror / Atom feedRe: Timestamp conversion Error in dynamic sql script
3+ messages / 2 participants
[nested] [flat]
* Re: Timestamp conversion Error in dynamic sql script
@ 2024-04-02 20:08 sud <[email protected]>
2024-04-02 20:57 ` Re: Timestamp conversion Error in dynamic sql script Erik Wienhold <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: sud @ 2024-04-02 20:08 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; Erik Wienhold <[email protected]>; +Cc: pgsql-general <[email protected]>
On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <[email protected]>
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: *
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Timestamp conversion Error in dynamic sql script
2024-04-02 20:08 Re: Timestamp conversion Error in dynamic sql script sud <[email protected]>
@ 2024-04-02 20:57 ` Erik Wienhold <[email protected]>
2024-04-03 20:09 ` Re: Timestamp conversion Error in dynamic sql script sud <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Wienhold @ 2024-04-02 20:57 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; pgsql-general <[email protected]>
On 2024-04-02 22:08 +0200, sud wrote:
> On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <[email protected]>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Timestamp conversion Error in dynamic sql script
2024-04-02 20:08 Re: Timestamp conversion Error in dynamic sql script sud <[email protected]>
2024-04-02 20:57 ` Re: Timestamp conversion Error in dynamic sql script Erik Wienhold <[email protected]>
@ 2024-04-03 20:09 ` sud <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: sud @ 2024-04-03 20:09 UTC (permalink / raw)
To: Erik Wienhold <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; pgsql-general <[email protected]>
This one worked. Thank you so much.
On Wed, Apr 3, 2024 at 2:27 AM Erik Wienhold <[email protected]> wrote:
> On 2024-04-02 22:08 +0200, sud wrote:
> > On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane <[email protected]>
> > 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-04-03 20:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-02 20:08 Re: Timestamp conversion Error in dynamic sql script sud <[email protected]>
2024-04-02 20:57 ` Erik Wienhold <[email protected]>
2024-04-03 20:09 ` sud <[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