Re: why generated columsn cannot be used in COPY TO?

On Friday, October 6, 2023, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> > On Oct 6, 2023, at 7:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Luca Ferrari <fluca1978(at)gmail(dot)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.
> >
> > 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.
> >
> > regards, tom lane
> >
> >
> What would be copied? The forumla? Seems to me one is using “the fast
> option” so adding the column which can be regenerated is overhead.

This question and statement makes zero sense to me. If you specify the
column name you’d get the values of that column like always.

I’m good, though, with the columns you can choose being a strict subset of
those that are output when you do not list any. Writing a select query to
get a faithful reproduction of the entire table’s contents is fine for an
API.

David J.