Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rrkRG-002TdY-V5 for pgsql-general@arkaria.postgresql.org; Tue, 02 Apr 2024 20:08:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rrkRE-00BWXb-Uc for pgsql-general@arkaria.postgresql.org; Tue, 02 Apr 2024 20:08:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rrkRE-00BWXD-9x for pgsql-general@lists.postgresql.org; Tue, 02 Apr 2024 20:08:56 +0000 Received: from mail-vs1-xe33.google.com ([2607:f8b0:4864:20::e33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rrkRB-000EyM-6s for pgsql-general@lists.postgresql.org; Tue, 02 Apr 2024 20:08:55 +0000 Received: by mail-vs1-xe33.google.com with SMTP id ada2fe7eead31-476cec93716so1621875137.2 for ; Tue, 02 Apr 2024 13:08:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712088532; x=1712693332; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=CuHoek7oEYyWltjSbc7+b1XbjqJiKrEstPSxeFPjCm4=; b=Y4PmjUBm/nQVOzl6F8iUXrlC1uR0EXfiBZiIPy/kIUjO5iDF0XA0D71Sowips3UDFT ReX0r/gF10UIus5ulG1XS7xT3Xt+WOcMAfHwFZDdWRlbFehV8bIJ3rzdKI9NDvzC0hKh q6HGGoj9K5t6/8wXeXVTZGn855EicMSzA2/sQ2KzHtJfUDAYr7CYnInv+8TT42A04XqV Aim/muSSnqDLY75XEryAhhbm/TuGVsctQuJY8ZdmLEXs9Ew7voC3pKr/h49wZS9HmJLG LyhdUei3h8RayCUqtLZRyV8LoR6F7dqRGC0VzE39CcfJ+sn/klsCYggUui9OZCHTnQNf EaBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712088532; x=1712693332; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=CuHoek7oEYyWltjSbc7+b1XbjqJiKrEstPSxeFPjCm4=; b=oRsd3o6M/X9ikGQIZmAvYbBujDYjH3kx+8SbuXlZOoVYL4jYilyBQm1Tz/BZlEn55e r7ytfqny2KSPZXNDrvK17B6x66Dmi6yz6zLGqtLBLWxgnaW8gCx0zM2gICsoKCKrU4v9 NuN+JXlUBkqzZPfQCTUFdVvxepA4kTDMDFO8cFAa0EhCDyad52rCQVDobPt+2272nn74 JDog1ozkDcCKEmagTwF6l0V0omIRyK7TiJBLANocL6xRZwt37K+Bffg19LxMbGH6kGgz Nh7FOmlYmy3RrHEaeh8nexAZ6sVp26AL48J27rL0q/8UbYA9DmDFFTp6TMIn2G08+Jb5 SV1g== X-Gm-Message-State: AOJu0YxBQI/rpzWK/B5NcF8pw+l3/qkQThWHmJX/L6z1bMrV+ao8hXWX YTk00TWegYN3mqZwFcJaWikPzjjOuhktDscRmUxvzsTZlqXiHYlM8jGMeUB2iDKFblZLowWb6KT vrvlXOxDtdNUtJjKwwkkoifQTQkU= X-Google-Smtp-Source: AGHT+IHLwZqqR9KpXPL88cZrJ22dS2sOH0EPccfyYksgoxmmu21N8SicCn6CjL87JiXisWG5tH/dyOXh2BwZmP4BETI= X-Received: by 2002:a05:6102:34d4:b0:476:f23c:53e with SMTP id a20-20020a05610234d400b00476f23c053emr10529600vst.24.1712088532175; Tue, 02 Apr 2024 13:08:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Wed, 3 Apr 2024 01:38:39 +0530 Message-ID: Subject: Re: Timestamp conversion Error in dynamic sql script To: Greg Sabino Mullane , Erik Wienhold Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000c8d574061522ac4e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c8d574061522ac4e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 2, 2024 at 7:46=E2=80=AFPM Greg Sabino Mullane 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 =3D> 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 :=3D '2022-01-01'; begin FOR i IN 0..10 LOOP EXECUTE format(' CREATE TABLE parent_table_%s ( CHECK (partition_key >=3D ''%s'' AND partition_key < ''%s'' ) * ) LIKE (parent_table including all);',* TO_CHAR(start_date + make_interval(days=3D>i),'YYYY_MM_DD'), (start_date + make_interval(days=3D>i))::timestamptz , (start_date + make_interval(days=3D>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=3D>i),'YYYY_MM_DD'), (start_date + make_interval(days=3D>i))::timestamptz , (start_date + make_interval(days=3D>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=3D>i),'YYYY_MM_DD'), (start_date + make_interval(days=3D>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: * --000000000000c8d574061522ac4e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Tue, Apr 2, 2024 at 7:46=E2=80=AFPM Gr= eg Sabino Mullane <htamfids@gmail.= com> wrote:
1. Declare start_date as DATE when you want to add da= ys with date + int
2. Keep TIMESTAMP and use start_date + make_interval(days =3D> i)

Also

0. Use TIMESTAM= PTZ not TIMESTAMP


Thank you so much. That helped.=C2=A0

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 state= ment?=C2=A0

SQL Error [42601]: ERROR: syntax error at or near = "LIKE"
Where: PL/pgSQL functio= n inline_code_block line 8 at EXECUTE

Error position

CREATE TABLE parent_table (
id Numeric,
col1 TEXT,
<= span style=3D"color:rgb(31,35,40)">col2 TEXT,
partit= ion_key TIMESTAMP,
primary key (partition_key, id)
)
PARTITION BY RANGE (partitio= n_key);

*****= ****

DO $$
DECLARE
start_date TIMESTAMPtz := =3D '2022-01-01';
begin
FOR i IN 0..10 LOOP

E=
XECUTE format('
    CREATE TABLE parent_table_%s (
        CHECK (partition_key >=3D  ''%s'' AND partition_=
key <  ''%s'' )
    ) LIKE (parent_table including all);',
   TO_CHAR(start_date + make_interval(days=3D>i),'YYYY_MM_DD'),
    (start_date + make_interval(days=3D>i))::timestamptz ,
    (start_date + make_interval(days=3D>i))::timestamptz =20
);

EXECUTE fo= rmat('
ALTER TABLE parent_table ATTA= CH PARTITION parent_table_%s
FOR VALUES = FROM (''%s'') TO (''%s'');',
TO_CHAR(start_date + make_interval(days=3D>i)= ,'YYYY_MM_DD'),
(start_date + ma= ke_interval(days=3D>i))::timestamptz ,
(start_date + make_interval(days=3D>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''=20
       FROM generate_series(1, 1000000);',TO_CHAR(start_date + m=
ake_interval(days=3D>i),'YYYY_MM_DD'),
        (start_date + make_interval(days=3D>i))::timestamptz    );
END LOOP;

END $$;
SQL Error [42601]: ERROR: syntax error at or near "LIKE&quo=
t;
Where: PL/pgSQL function inline_code_=
block line 8 at EXECUTE
Error position:=C2=A0
=


--000000000000c8d574061522ac4e--