public inbox for [email protected]  

help / color / mirror / Atom feed

From: sud <[email protected]>
To: Greg Sabino Mullane <[email protected]>
To: Erik Wienhold <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Timestamp conversion Error in dynamic sql script
Date: Wed, 3 Apr 2024 01:38:39 +0530
Message-ID: <CAD=mzVXTjv4nY96MZ0sAxVsdsEFMXzB2gJkzD_T445R82YOt0g@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJXnjoEJRxRd7az0pSZX6UDMeNG=5ZUgZmKgha2VVdmUw@mail.gmail.com>
References: <CAD=mzVVHrGgJ08SH8pECjTAH1EKE26ZdL26OjW3mcXhM-AZi2A@mail.gmail.com>
	<m5bfxgtwkn6vasomhh3h36frkehjapftu74ebxattsyajxgq4w@uhxjzqsnqzyu>
	<CAKAnmmJXnjoEJRxRd7az0pSZX6UDMeNG=5ZUgZmKgha2VVdmUw@mail.gmail.com>

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: *

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Timestamp conversion Error in dynamic sql script
  In-Reply-To: <CAD=mzVXTjv4nY96MZ0sAxVsdsEFMXzB2gJkzD_T445R82YOt0g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox