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 1qonWO-002wEq-RY for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 16:17:49 +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 1qonWM-00965w-Iu for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 16:17:47 +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 1qonWM-00965m-2T for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 16:17:47 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qonWF-000Kwg-Tc for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 16:17:45 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-579de633419so27441007b3.3 for ; Fri, 06 Oct 2023 09:17:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696609059; x=1697213859; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=gVd2UJ565RC/b1Mr16oK32U7Bb/Tygg+yfLQOru++5k=; b=lAZym9cIwL1J7NiB2RO+AgKIiXigvDE6lqb3vWpd0wu4J83OYz9iVHlmHwbPzdMocD Lm0v5eWmxsiE2kGxY9c4PkjCHCqNhTMtaRu+EyAQg0i/WKb4zJ5LxKt3hq/2QL7i8xj/ qTNSI2Im5xMWRwpCsoLeFhqVWL++JBmpqES5T4keCfbJfkQbfFvgoCb4My+vr2t/KcrA 5cIwgV9eZ7NQXLGiHKTQzeYTC+Os807B9TPUgCuqKkJa/evzEr9Yy2YFpDbrCZKJjm/r gE5g2S2jsvcAPwACBp8It3cx6mtbErFSuMmkkElcOjVy72eSz+cjmNe/wEgRWQexMRrA 4/eQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696609059; x=1697213859; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=gVd2UJ565RC/b1Mr16oK32U7Bb/Tygg+yfLQOru++5k=; b=oSrg5WYuHSGXfs6DWIPl/rZnpAVh7mvGAG+X2+OY14wv2N4o0k0VmothGQvdQ3lGE7 zYOI+r6ksX4KoO7SmYAIbeWffRgt0ZN0TY3GUi7VWsADh4UyR8MNLP5KwJUHn1WRlmBk NkcmszfumoecKTyeaiK5bXNTv1RxJJ3hnRt0iXLsZh0E9XzIuacwwWQApXD3pNBbUNKx Hi5Hwds90460zE0q41T3VIwn4sY8CYCALVcv0uinyod92SAZkiunm3IL5Ruk9VaMEyOM S3sZ9XXry9c4Pa8fDtj0eQkyrGitsGGgtd7hXy2jylCx2lL40wJ8eU+10DS46uswFl3v J2/g== X-Gm-Message-State: AOJu0YzjJrj5GuCp2RD/FlS0VyNSfBlFKmMzJtd79X7X90zChTBc3hZI ZZuy15N1/riqYO5Jq4sEo/cG/nf4OeY= X-Google-Smtp-Source: AGHT+IETLIhnKNL1G7NFsgoVaRbZz31xsSuL82UsNclZXa1tp91zMG8mGknz5Y3iMHMUDrgb9JAJ9A== X-Received: by 2002:a81:4811:0:b0:5a1:eec1:b42e with SMTP id v17-20020a814811000000b005a1eec1b42emr9288442ywa.0.1696609058860; Fri, 06 Oct 2023 09:17:38 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:5cc2:23a0:afa6:cd2? ([2600:1700:bd2c:b810:5cc2:23a0:afa6:cd2]) by smtp.googlemail.com with ESMTPSA id j131-20020a816e89000000b005956b451fb8sm1359743ywc.100.2023.10.06.09.17.38 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Oct 2023 09:17:38 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Vg4EsFksFZNwU4CN8dWwul4x" Message-ID: <10519e75-1903-ebd2-2625-4f5b655a02bb@gmail.com> Date: Fri, 6 Oct 2023 11:17:38 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: why generated columsn cannot be used in COPY TO? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <2949797.1696600071@sss.pgh.pa.us> <8cebb434-d1ff-2744-1ad0-63816019df04@a-kretschmer.de> <7f80a4a3-af51-6863-b72d-bee1118b8f79@gmail.com> <4edb5259-10e6-4103-9d4b-9d63992a6a91@aklaver.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Vg4EsFksFZNwU4CN8dWwul4x Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/6/23 11:08, David G. Johnston wrote: > On Fri, Oct 6, 2023 at 8:54 AM Adrian Klaver > wrote: > > On 10/6/23 08:45, Ron wrote: > > On 10/6/23 09:04, Andreas Kretschmer wrote: > >> > > >>> Not sure how convincing that reasoning is, but it was at least > >>> thought about.  I do agree with it as far as the default column > >>> list goes, but maybe we could allow explicit selection of these > >>> columns in COPY TO. > >> > >> sounds okay > > > > Nah.  "The programmer -- and DBA -- on the Clapham omnibus" quite > > reasonably expects that COPY table_name TO (output)" copies all the > > columns listed in "\d table_name". > > > > Yeah, I would agree. > > > Sure, but it doesn't.  Mainly since copy's original design was intended to > solve the dump/restore problem and it doesn't make sense to specify data > for inbound generated data.  So while we do have a POLA violation here the > desirability to now fix it years later is basically zero.  And the current > behavior is at least defensible and consistent.  And there is a very easy > way to get the desired output making any change that much harder a sell. At least it's explicitly mentioned in the docs that generated columns are excluded. > > The error message maybe could use some help though, and if there isn't a > hint maybe add one. > > David J. > -- Born in Arizona, moved to Babylonia. --------------Vg4EsFksFZNwU4CN8dWwul4x Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/6/23 11:08, David G. Johnston wrote:
On Fri, Oct 6, 2023 at 8:54 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/6/23 08:45, Ron wrote:
> On 10/6/23 09:04, Andreas Kretschmer wrote:
>>

>>> Not sure how convincing that reasoning is, but it was at least
>>> thought about.  I do agree with it as far as the default column
>>> list goes, but maybe we could allow explicit selection of these
>>> columns in COPY TO.
>>
>> sounds okay
>
> Nah.  "The programmer -- and DBA -- on the Clapham omnibus" quite
> reasonably expects that COPY table_name TO (output)" copies all the
> columns listed in "\d table_name".
>

Yeah, I would agree.


Sure, but it doesn't.  Mainly since copy's original design was intended to solve the dump/restore problem and it doesn't make sense to specify data for inbound generated data.  So while we do have a POLA violation here the desirability to now fix it years later is basically zero.  And the current behavior is at least defensible and consistent.  And there is a very easy way to get the desired output making any change that much harder a sell.

At least it's explicitly mentioned in the docs that generated columns are excluded.


The error message maybe could use some help though, and if there isn't a hint maybe add one.

David J.


--
Born in Arizona, moved to Babylonia.
--------------Vg4EsFksFZNwU4CN8dWwul4x--