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 1qomEq-002qzh-0n for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 14:55:36 +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 1qomEn-0085rH-U5 for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 14:55:34 +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 1qomEn-0085qo-I3 for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 14:55:34 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qomEh-000KPV-UX for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 14:55:33 +0000 Received: by mail-pl1-x629.google.com with SMTP id d9443c01a7336-1c5ff5f858dso17342605ad.2 for ; Fri, 06 Oct 2023 07:55:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696604126; x=1697208926; darn=lists.postgresql.org; h=message-id:in-reply-to:to:references:date:subject:mime-version :content-transfer-encoding:from:from:to:cc:subject:date:message-id :reply-to; bh=r04zN8ianQjDep6GMmdGVE9mkYa/e4FsS7IjSuSGfFU=; b=ffYrxb3oaguKSFx6S0F/tnofLxjlPoJme13ddDJ2B6k007mvgYnwgh7Ob53uog6zdY qBEoTfF6xypmNtTRMvyPCPGpKlnIUezG3fWIb7NQ+HE4GiJb56zr4YMUixwTBpbkVWLQ xSqdfyTMunMA9ZhOnFuqUhjckBYrFn862fcNEhZq45UpmsLsgIgF8SoSOgLaR50s8/0d 7qlOAZCF+K6fehNssWwQNp+Is4qAyGEpZu8LzRVMV0PyQdG5bCB5gIBdJjPH9utWT2pt f2937n7pspz3oMQnVQ0ZTCzMR2+nxPP3AwZttGkSQOW4J85ZadxND+zQZXTWm6h714a1 QoKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696604126; x=1697208926; h=message-id:in-reply-to:to:references:date:subject:mime-version :content-transfer-encoding:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=r04zN8ianQjDep6GMmdGVE9mkYa/e4FsS7IjSuSGfFU=; b=hi40LDgsVlaUJYDzgIeBGfIvrbW7J3ZMT4gIwawAKPPfj+MvT+FVhAkFz/7bKB20RS zacm7ImBcagh/lerRgoP6QY6IeZeUpcv5peFIX7hXs+0nUQ3jcZOC5eTg4RlAXshk+5k VlFNwP6JJZEgogmfalAAI9Hb5gOvUwDhsVvrQRtVkpkNXkbLr9Ss7YcOKJJexdMkQJuv rMiq03Rv7WBf1HnLlmnVo432V8nDe5Le8udljDAsm7nHHMt/n7EozpeVI/sJ/pgztep2 i2M+e68XRO4NmsXBPiFIQcB1Ra7A1S5OKPkPFFKKyyN6v0ZJoF4dnHTRhNeBdmHs+I8e s5Iw== X-Gm-Message-State: AOJu0YyUcV5ceRIUAbtOvE4M/nyZV23gOryIAaRDiHMlgxXBsuUcmiQl zoY4SzddAvKuUg0DGvyYR+F8XmBLeX0= X-Google-Smtp-Source: AGHT+IHI4K+nNY3FKUwQ9KDCeQAS/jQk23rUEvkdVapobd4NQcwONYoy7S0W29sSuDYiTuUbbQho6Q== X-Received: by 2002:a17:903:25ca:b0:1c6:e8d:29c9 with SMTP id jc10-20020a17090325ca00b001c60e8d29c9mr5963046plb.50.1696604126370; Fri, 06 Oct 2023 07:55:26 -0700 (PDT) Received: from smtpclient.apple ([155.100.47.3]) by smtp.gmail.com with ESMTPSA id m10-20020a170902768a00b001c631236505sm3958387pll.228.2023.10.06.07.55.25 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 06 Oct 2023 07:55:25 -0700 (PDT) From: Rob Sargent Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.15\)) Subject: Re: why generated columsn cannot be used in COPY TO? Date: Fri, 6 Oct 2023 08:55:24 -0600 References: <2949797.1696600071@sss.pgh.pa.us> To: pgsql-general In-Reply-To: <2949797.1696600071@sss.pgh.pa.us> Message-Id: <8C3C0E91-3515-4E51-8932-1ABE6CE57977@gmail.com> X-Mailer: Apple Mail (2.3654.120.0.1.15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Oct 6, 2023, at 7:47 AM, Tom Lane wrote: >=20 > 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? >=20 > There's a comment about that in copy.c: >=20 > * 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. >=20 > 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. >=20 > regards, tom lane >=20 >=20 What would be copied? The forumla? Seems to me one is using =E2=80=9Cthe = fast option=E2=80=9D so adding the column which can be regenerated is = overhead.