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 1qon1n-002tpm-1u for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 15:46:11 +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 1qon1k-008cjh-DL for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 15:46:09 +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 1qon1j-008cjY-Ut for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 15:46:08 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qon1d-000cTf-HO for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 15:46:07 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-59f82ad1e09so27814417b3.0 for ; Fri, 06 Oct 2023 08:46:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696607159; x=1697211959; 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=Jvafm1oDw/MKrJ9jv62bbZMz2bVr1n5h8CwD88Bn1cE=; b=a4JJRP8EzBW4StW6+cX3lBs/WqCcTn7YNxoKN1JBsjhav+olIjDdDl0FiQT6uHx/ZD e9JeZtpAFNknKg0AQAmBeUehbUad84T/1mdBQVe2w8uY+m1Q/Lfx+FFF5loTbiLxFjZj 83+oBFVnMaAWm6PouXSoF+MFQGMOL2HgCHLYc9ZBekRdCHirX31Yy5VJvM3YG+JYn/Fp JKwenTCJ4R/2+hn2KStrmQu8BRmHME/pZyJ2BjZWmY0z1GbZeLPWEC7VV9tLIBj35WwJ vvRMQ8s0zuNcQIlRj2z4GN8K/pBCRE7HiRnKOODBo99yG+JdRVN2C3mR0fQ/q97mMrFV 54aw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696607159; x=1697211959; 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=Jvafm1oDw/MKrJ9jv62bbZMz2bVr1n5h8CwD88Bn1cE=; b=DaIDrLhVwgjBcQpfpzNQiKjBjPrsxSwIMqyPNABWpaR8oXNHb6OEYtCn3ZjNXmOZM+ XHldFqVGZjEroWSOfNe+LcNhtVA6bDr+bj07RQOZTtS0GmxJeC3VPSItF5KT3bmX6yzJ nE2/CCdtBwzXDZtoa/sm//y9VcJlTruNDnzDZ4WgnvcVTQ2hZdJ3CnSNElGPsVHh0hSS akE3uUHAKZCV+FKin9uhWOvrb8PkHDNQUMnkAvfcUkMJaZZUXLyx/lcI85nLGHmb2egM KG2HoDszesOiioOUTntzJgXFElwEDM267dU+dTLTuAyPvuv0DMvtjr53izcavlc50fVs V2mA== X-Gm-Message-State: AOJu0YwbOVRhfNwWLR3A6yvDV9+tb5N2IyB+4W2GRVQKrME72jgb9mAT qAO0H3miyAeV8wGujmYOpsHN5oQ49GI= X-Google-Smtp-Source: AGHT+IFFnGHVfjKEjwhmgflrZ1aNRcp2sNDv5xD4TAyqTIFN0J30wo7KpaD8kx8yIkOA+BPSsK9SmA== X-Received: by 2002:a25:6d89:0:b0:d86:2cd4:c443 with SMTP id i131-20020a256d89000000b00d862cd4c443mr7027280ybc.21.1696607159559; Fri, 06 Oct 2023 08:45:59 -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 t40-20020a25aaab000000b00d853795d3e6sm1140276ybi.9.2023.10.06.08.45.59 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Oct 2023 08:45:59 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------DF80m7KxvywtarQYOmiACFZ6" Message-ID: <7f80a4a3-af51-6863-b72d-bee1118b8f79@gmail.com> Date: Fri, 6 Oct 2023 10:45:58 -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> From: Ron In-Reply-To: <8cebb434-d1ff-2744-1ad0-63816019df04@a-kretschmer.de> 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. --------------DF80m7KxvywtarQYOmiACFZ6 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/6/23 09:04, Andreas Kretschmer wrote: > > > Am 06.10.23 um 15:47 schrieb Tom Lane: >> Luca Ferrari writes: >>> I'm wondering why in COPY TO (file or program) I cannot use generated >>> columns: since I'm pushing data out of the table, why they are not >>> allowed? >> There's a comment about that in copy.c: >> >>   * We don't include generated columns in the generated full list and we >> don't >>   * allow them to be specified explicitly.  They don't make sense for COPY >>   * FROM, but we could possibly allow them for COPY TO.  But this way >> it's at >>   * least ensured that whatever we copy out can be copied back in. > > ha, as always: read the source ;-) > >> >> 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". https://en.wikipedia.org/wiki/Man_on_the_Clapham_omnibus "The man on the Clapham omnibus is a hypothetical ordinary and reasonable person, used by the courts in English law where it is necessary to decide whether a party has acted as a *reasonable person* would – for example, in a civil action for negligence. The character is a *reasonably educated, intelligent but nondescript person*, against whom the defendant's conduct can be measured." -- Born in Arizona, moved to Babylonia. --------------DF80m7KxvywtarQYOmiACFZ6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/6/23 09:04, Andreas Kretschmer wrote:


Am 06.10.23 um 15:47 schrieb Tom Lane:
Luca Ferrari <fluca1978@gmail.com> writes:
I'm wondering why in COPY TO (file or program) I cannot use generated
columns: since I'm pushing data out of the table, why they are not
allowed?
There's a comment about that in copy.c:

  * We don't include generated columns in the generated full list and we don't
  * allow them to be specified explicitly.  They don't make sense for COPY
  * FROM, but we could possibly allow them for COPY TO.  But this way it's at
  * least ensured that whatever we copy out can be copied back in.

ha, as always: read the source ;-)


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".

https://en.wikipedia.org/wiki/Man_on_the_Clapham_omnibus
"The man on the Clapham omnibus is a hypothetical ordinary and reasonable person, used by the courts in English law where it is necessary to decide whether a party has acted as a reasonable person would – for example, in a civil action for negligence. The character is a reasonably educated, intelligent but nondescript person, against whom the defendant's conduct can be measured."

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