Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCAQm-0002iG-Nn for pgsql-docs@arkaria.postgresql.org; Fri, 15 Jul 2022 01:47:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCAQj-0003EL-Tw for pgsql-docs@arkaria.postgresql.org; Fri, 15 Jul 2022 01:47:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCAQj-0003Dp-Jq for pgsql-docs@lists.postgresql.org; Fri, 15 Jul 2022 01:47:45 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oCAQc-0001Rv-NA for pgsql-docs@lists.postgresql.org; Fri, 15 Jul 2022 01:47:45 +0000 Received: by mail-ed1-x52a.google.com with SMTP id y4so4572483edc.4 for ; Thu, 14 Jul 2022 18:47:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=qKw3NvdgJazARVsebKD1WTeysgTTtsuQN517A0rEwFY=; b=OxMBF1U6iLKbelP+NLgj0EHg6ScRkiFZpoexqzDoLOPrsr7grVNpZnYT6wyKSmx91B twzoHUlGREW1LP2dB+CKKShZRVyyDwr183ul0czib9EqYVSRgPdHflgL2kup9nP0eRq4 eaFSeKAbr90EgBGho+WPpiQv7uD5Mk/LJvGYWumezKSHLL6vBQ2D3VGBtiadDoeKZ5Ys TJ4E2bKJXtVZIA7aqeGYu1P+tq/wZxurYbk0oF9y0MVfrSyXpvQIpHwsfhUZU7Do2vyS NOS4F9sDTALcaNLxEIQ9dEBgFzFlnB/wGeF01JILp01xN1BUIyRxu5N2+VQhDQj+UPcT L2LQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=qKw3NvdgJazARVsebKD1WTeysgTTtsuQN517A0rEwFY=; b=pyBeYjM1DUWB6fhpgq+px6lLI3lIg2HpepdXyS9/i3+kXa2dqSFBLL8xN9wL8d1rB5 Rf3lsjKLRIjgE6LEgyvyyg5XLLiKBs4tUuDgZkA35/rFt3vLlOGLzPZa4XQ1VsaoeDFt uwAHu7rqSEUk87uFbFqCt0iEURi+llwU92mhyvMyF3jUzhPU29CMGD/g4aUFTiLeL4dD 37xkYmcPSZ6gqnZoHRo99YoCq0Vwfffa/RiCDsZ52+cu6bLdE6Y2iZ5KXiWR+pVfkady P40s7wRXuLZXoabobTnYOAak4a0PvcegYLGXkruNbjQl6RzHdug4kqLC1AfM2AC3sC9x gfpg== X-Gm-Message-State: AJIora8tD3Mdb1AkHYhllKo8LaAs+kXj6dl2H9jJ8etQE6TRTU2rmxtc rQzYhhl2admmJQNFjwn+pazryFjKgmCf+5/op38= X-Google-Smtp-Source: AGRyM1vEExHmE22JgemxAefzOY3wP1AhVzqPKGyuDmtD7jpvzX0haultfAFqnyUUnB0SMi4H+yhhnohniE3tusozZLA= X-Received: by 2002:a05:6402:1658:b0:43a:91cd:2ffb with SMTP id s24-20020a056402165800b0043a91cd2ffbmr15472947edx.277.1657849656896; Thu, 14 Jul 2022 18:47:36 -0700 (PDT) MIME-Version: 1.0 References: <164441177106.9677.5991676148704507229@wrigleys.postgresql.org> In-Reply-To: From: "David G. Johnston" Date: Thu, 14 Jul 2022 18:47:19 -0700 Message-ID: Subject: Re: CREATE TABLE AS, section IF NOT EXISTS should clarify what happens to the data To: Bruce Momjian Cc: frank_limpert@yahoo.com, Pg Docs Content-Type: multipart/alternative; boundary="000000000000e3ee2105e3ce33fd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e3ee2105e3ce33fd Content-Type: text/plain; charset="UTF-8" On Thu, Jul 14, 2022 at 6:08 PM Bruce Momjian wrote: > On Wed, Feb 9, 2022 at 01:02:51PM +0000, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/14/sql-createtableas.html > > Description: > > > > If I create a table with CREATE TABLE IF NOT EXISTS table_name AS, and > the > > table exists already, I get a NOTICE instead of an ERROR. > > > > I wanted to have it clarified explicitly that the data from the SELECT > > statement is ignored in this case. > > Good point. Attached is a documentation patch to clarify this. > > Not only is the existing table not modified, I think that the select query portion of the command is never even executed. That would seem to be useful knowledge to have. Maybe something like: Do not throw an error if a relation with the same name already exists. A notice is issued in this case. If WITH DATA is specified (the default) the execution of the parsed query does not happen, leaving the existing table with its existing contents. I mention parsing since the command itself will fail if the query is invalid even if a table already exists. But any potential runtime errors (or even side-effects) will not happen. I've removed the xref to create table as there is nothing additional there of material benefit - the sentence: "Note that there is no guarantee that the existing relation is anything like the one that would have been created." is basically what we are adding here but this has more detail. David J. --000000000000e3ee2105e3ce33fd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jul 14, 2022 at 6:08 PM Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Feb=C2=A0 9, 2022 at 01:02:51PM +0000, PG Doc comments f= orm wrote:
> The following documentation comment has been logged on the website: >
> Page: https://www.postgresql.org/docs/= 14/sql-createtableas.html
> Description:
>
> If I create a table with CREATE TABLE IF NOT EXISTS table_name AS, and= the
> table exists already, I get a NOTICE instead of an ERROR.
>
> I wanted to have it clarified explicitly that the data from the SELECT=
> statement is ignored in this case.

Good point.=C2=A0 Attached is a documentation patch to clarify this.

Not only is the existing table not modified= , I think that the select query portion of the command is never even execut= ed.=C2=A0 That would seem to be useful knowledge to have.

Maybe something like:

<para>
=C2= =A0Do not throw an error if a relation with the same name already exists.
=C2=A0A notice is issued in this case.
=C2=A0If <literal&= gt;WITH DATA</literal> is specified (the default) the execution of th= e parsed query does not happen, leaving the existing table with its existin= g contents.
</para>

I mention parsing = since the=C2=A0command itself will fail if the query is invalid even if a t= able already exists.=C2=A0 But any potential runtime errors (or even side-e= ffects) will not happen.

I've removed the xref to = create table as there is nothing additional there of material benefit - the= sentence:

"Note that there is no guarantee that = the existing relation is anything like the one that would have been created= ."

is basically what we are adding here but this = has more detail.

David J.

--000000000000e3ee2105e3ce33fd--