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 1rs6ve-004sQY-FH for pgsql-general@arkaria.postgresql.org; Wed, 03 Apr 2024 20:09:51 +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 1rs6vd-007qXy-6C for pgsql-general@arkaria.postgresql.org; Wed, 03 Apr 2024 20:09:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rs6vc-007qXq-Rh for pgsql-general@lists.postgresql.org; Wed, 03 Apr 2024 20:09:48 +0000 Received: from mail-vk1-xa2a.google.com ([2607:f8b0:4864:20::a2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rs6vZ-000Tis-VL for pgsql-general@lists.postgresql.org; Wed, 03 Apr 2024 20:09:47 +0000 Received: by mail-vk1-xa2a.google.com with SMTP id 71dfb90a1353d-4d47000f875so100654e0c.2 for ; Wed, 03 Apr 2024 13:09:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712174984; x=1712779784; 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=4fSSGjiGDBcqTkaF7OpBEWb6ZLs+oEUaahCiCRbMYOk=; b=AbQ/lwK2zf4HHl51jAj5e9VOtyKEbVMjKsw8iDRNeoaqi99gRgsu6xYOBWd6LB3UXh jXIrfv7q+yYkmUaVqkY6M+yfVQTpdlxT2XpZquoenbyu8VUh7YFsGqML/wOh/PWOCLZf FQepFGCThNXKMFxGbfoqsW5n5JpRIFsWjjSyWMOTK0CwysBIOY3XSz2at96cbJy5XSzn boxCnhAfeMkaSCpuHl3wzsihwx//tIULkXwGItQeZEYZ+ly4LBnqTjYlk/2ISeE5e9MD vLVa+1PjI+wgJU/koQ+hbI1FJtb0Qa/xgHIkP9Ee4BT8uim/zKH93E+N4VyzeKD6WkIb RHqQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712174984; x=1712779784; 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=4fSSGjiGDBcqTkaF7OpBEWb6ZLs+oEUaahCiCRbMYOk=; b=ihzprNF+0jCdAIHbef2oK59EGLHjuEWjADW54m7JVcWIxmezv4qpH9OTMAJaGzV/mn nUtwdpn4W5EIGNP0+7QE9fB1BawlGIhP6mMb/m2z1ohG8rl/wFbogVzB2eR3W7QE9ePf erceQhXMri/wv/nWOVy1UILu10uP8XDgA+bDgZ4SWmTUTj5tLORTAQj/yhpbsVFVDTPl xkQpuGqHxbXRJ2Mp568mOLzoCFlTeGI3XlGQAoiDk8w3YiKYn/88GAs4LENHyk051F0w cfkLqqYi0m6yvNpn8DQR7Y25W/+i0YIqmgKklU9Wc3swjlGdylEGS/th4P4zux2TMzVu sJlA== X-Forwarded-Encrypted: i=1; AJvYcCXtLrFlskydUDRG0LIliO8zYzGD9RwCWZKbbbUg/81fKX65UpnD6g0YP/Jn8huH9c9PsBNFI65uIgnFl/G0s1sfzxW6HdIHE3qKo9tw9P8ldIJV X-Gm-Message-State: AOJu0YyskW/Ni7FxtyGEkWMrT3D7WzhTCWWPExtIQ0U1OwgIv2KyLtEc cZYLK5I0LNT9NELUjcn20h4VoRP9tOBdvCqKK9s/Jr4Uj61ObOmKEfPAPz1dlruywzu8MoHm1hC PYvkNMs28WJKwatnOi0eYr11rrjk= X-Google-Smtp-Source: AGHT+IHbf48qFFDUU6ECAiBGIlDoPNHvvqye+3pjyzJtB8GVQMabhTiiBj4Vi9ooAridZlADWnw2nsIkud3LfDD3iw4= X-Received: by 2002:a05:6122:2025:b0:4d8:7b33:c624 with SMTP id l37-20020a056122202500b004d87b33c624mr414317vkd.0.1712174984196; Wed, 03 Apr 2024 13:09:44 -0700 (PDT) MIME-Version: 1.0 References: <6oywkrrg5bzvnmorg7l3kdvzc2ipejm2xx3o5qaam57chuacic@iu4pe64gjqoj> In-Reply-To: <6oywkrrg5bzvnmorg7l3kdvzc2ipejm2xx3o5qaam57chuacic@iu4pe64gjqoj> From: sud Date: Thu, 4 Apr 2024 01:39:32 +0530 Message-ID: Subject: Re: Timestamp conversion Error in dynamic sql script To: Erik Wienhold Cc: Greg Sabino Mullane , pgsql-general Content-Type: multipart/alternative; boundary="000000000000b9f7f2061536cd81" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b9f7f2061536cd81 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable This one worked. Thank you so much. On Wed, Apr 3, 2024 at 2:27=E2=80=AFAM Erik Wienhold wrote= : > On 2024-04-02 22:08 +0200, sud wrote: > > On Tue, Apr 2, 2024 at 7:46=E2=80=AFPM Greg Sabino Mullane > > 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 >=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 > > ); > > > > [...] > > > > *SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQ= L > > 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 > --000000000000b9f7f2061536cd81 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
This one worked. Thank you so much.

On Wed, Apr 3, 2024 at = 2:27=E2=80=AFAM Erik Wienhold <ewie@ew= ie.name> wrote:
On 2024-04-02 22:08 +0200, sud wrote:
> On Tue, Apr 2, 2024 at 7:46=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail.com>=
> 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 state= ment?
>
> [...]
>
> EXECUTE format('
>=C2=A0 =C2=A0 =C2=A0CREATE TABLE parent_table_%s (
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CHECK (partition_key >=3D=C2=A0 &#= 39;'%s'' AND partition_key <=C2=A0 ''%s'' )<= br> >=C2=A0 =C2=A0 * ) LIKE (parent_table including all);',*
>=C2=A0 =C2=A0 TO_CHAR(start_date + make_interval(days=3D>i),'YYY= Y_MM_DD'),
>=C2=A0 =C2=A0 =C2=A0(start_date + make_interval(days=3D>i))::timesta= mptz ,
>=C2=A0 =C2=A0 =C2=A0(start_date + make_interval(days=3D>i))::timesta= mptz
> );
>
> [...]
>
> *SQL Error [42601]: ERROR: syntax error at or near "LIKE"Whe= re: 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.:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE TABLE parent_table_yyyy_mm_dd (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 LIKE parent_table,<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CHECK (...)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 );

--
Erik
--000000000000b9f7f2061536cd81--