pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
why generated columsn cannot be used in COPY TO?
14+ messages / 8 participants
[nested] [flat]

* why generated columsn cannot be used in COPY TO?
@ 2023-10-06 11:53  Luca Ferrari <fluca1978@gmail.com>
  0 siblings, 2 replies; 14+ messages in thread

From: Luca Ferrari @ 2023-10-06 11:53 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@lists.postgresql.org>

Hi all,
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?

Example:

testdb=# CREATE TABLE test( pk int generated always as identity primary key
, ts timestamp default current_timestamp
, month int generated always as ( extract( month from ts ) ) stored
);

testdb=# insert into test( ts ) values( current_timestamp );

testdb=# copy test to program 'head'; -- ok but silently removes the column
COPY 1
testdb=# copy test( month ) to program 'head';
ERROR:  column "month" is a generated column
DETAIL:  Generated columns cannot be used in COPY.

Thanks,
Luca





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 12:20  Andreas Kretschmer <andreas@a-kretschmer.de>
  parent: Luca Ferrari <fluca1978@gmail.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Andreas Kretschmer @ 2023-10-06 12:20 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



Am 06.10.23 um 13:53 schrieb Luca Ferrari:
> Hi all,
> 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?
>
> Example:
>
> testdb=# CREATE TABLE test( pk int generated always as identity primary key
> , ts timestamp default current_timestamp
> , month int generated always as ( extract( month from ts ) ) stored
> );
>
> testdb=# insert into test( ts ) values( current_timestamp );
>
> testdb=# copy test to program 'head'; -- ok but silently removes the column
> COPY 1
> testdb=# copy test( month ) to program 'head';
> ERROR:  column "month" is a generated column
> DETAIL:  Generated columns cannot be used in COPY.

you can use copy (select * ...) to ...

test=*# CREATE TABLE test( pk int generated always as identity primary 
key , ts timestamp default current_timestamp , month int generated 
always as ( extract( month from ts ) ) stored );
CREATE TABLE
test=*# commit;
COMMIT
test=# insert into test( ts ) values( current_timestamp );
INSERT 0 1
test=*# select * from test;
  pk |            ts             | month
----+---------------------------+-------
   1 | 06-OCT-23 14:18:28.742152 |    10
(1 row)

test=*# commit;
COMMIT
test=# copy test to stdout;
1    06-OCT-23 14:18:28.742152
test=*# copy test to stdout;
1    06-OCT-23 14:18:28.742152
test=*# copy (select * from test) to stdout;
1    06-OCT-23 14:18:28.742152    10
test=*#



Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 13:16  Ron <ronljohnsonjr@gmail.com>
  parent: Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 0 replies; 14+ messages in thread

From: Ron @ 2023-10-06 13:16 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 10/6/23 07:20, Andreas Kretschmer wrote:
>
>
> Am 06.10.23 um 13:53 schrieb Luca Ferrari:
>> Hi all,
>> 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?
>>
>> Example:
>>
>> testdb=# CREATE TABLE test( pk int generated always as identity primary key
>> , ts timestamp default current_timestamp
>> , month int generated always as ( extract( month from ts ) ) stored
>> );
>>
>> testdb=# insert into test( ts ) values( current_timestamp );
>>
>> testdb=# copy test to program 'head'; -- ok but silently removes the column
>> COPY 1
>> testdb=# copy test( month ) to program 'head';
>> ERROR:  column "month" is a generated column
>> DETAIL:  Generated columns cannot be used in COPY.
>
> you can use copy (select * ...) to ...

But why not?

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 13:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Luca Ferrari <fluca1978@gmail.com>
  1 sibling, 2 replies; 14+ messages in thread

From: Tom Lane @ 2023-10-06 13:47 UTC (permalink / raw)
  To: Luca Ferrari <fluca1978@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

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.

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 14:04  Andreas Kretschmer <andreas@a-kretschmer.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Andreas Kretschmer @ 2023-10-06 14:04 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



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


Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 14:55  Rob Sargent <robjsargent@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 14+ messages in thread

From: Rob Sargent @ 2023-10-06 14:55 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@lists.postgresql.org>



> On Oct 6, 2023, at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> 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.
> 
> 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.






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 15:03  David G. Johnston <david.g.johnston@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  1 sibling, 0 replies; 14+ messages in thread

From: David G. Johnston @ 2023-10-06 15:03 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

On Friday, October 6, 2023, Rob Sargent <robjsargent@gmail.com> wrote:

>
>
> > On Oct 6, 2023, at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > 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.
> >
> > 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.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 15:06  Dominique Devienne <ddevienne@gmail.com>
  parent: Rob Sargent <robjsargent@gmail.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Dominique Devienne @ 2023-10-06 15:06 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

On Fri, Oct 6, 2023 at 4:59 PM Rob Sargent <robjsargent@gmail.com> wrote:

> What would be copied?  The formula?


Of course not. That's DDL, not DML IMHO.


> Seems to me one is using “the fast option” so adding the column which can
> be regenerated is overhead.
>

Regenerated by whom?  COPY TO to is output to the outside world.
Nothing says it will come back to PostgreSQL later. COPY is not only for
backups.
I use COPY to "stream" rows out for processing, combines the best of SELECT
and the incremental nature of CURSOR, for the best latency and throughput.

All that said, that's a work-around like Andreas showed.
I agree with Tom that ignoring them in the default list makes sense,
but that if explicitly asked for, they should be copied out. My $0.02.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 15:45  Ron <ronljohnsonjr@gmail.com>
  parent: Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 1 reply; 14+ messages in thread

From: Ron @ 2023-10-06 15:45 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 15:53  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Adrian Klaver @ 2023-10-06 15:53 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

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.

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 16:08  David G. Johnston <david.g.johnston@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 2 replies; 14+ messages in thread

From: David G. Johnston @ 2023-10-06 16:08 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

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.

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

David J.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 16:17  Ron <ronljohnsonjr@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Ron @ 2023-10-06 16:17 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 16:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Tom Lane @ 2023-10-06 16:37 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

"David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On 10/6/23 08:45, Ron wrote:
>>> 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".

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

Changing the default behavior now is certainly a non-starter.
I don't really see any backwards-compatibility problem with
allowing cases that had been errors, though.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: why generated columsn cannot be used in COPY TO?
@ 2023-10-06 20:28  David G. Johnston <david.g.johnston@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 14+ messages in thread

From: David G. Johnston @ 2023-10-06 20:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Friday, October 6, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> >> On 10/6/23 08:45, Ron wrote:
> >>> 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".
>
> > 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.
>
> Changing the default behavior now is certainly a non-starter.
> I don't really see any backwards-compatibility problem with
> allowing cases that had been errors, though.
>

I wouldn’t vote against it but the current simplicity seems sufficient.
 “Copy table doesn’t recognize generated columns, use copy (select) if you
want to include them in the output.”

David J.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2023-10-06 20:28 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-06 11:53 why generated columsn cannot be used in COPY TO? Luca Ferrari <fluca1978@gmail.com>
2023-10-06 12:20 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-06 13:16   ` Ron <ronljohnsonjr@gmail.com>
2023-10-06 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-06 14:04   ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-10-06 15:45     ` Ron <ronljohnsonjr@gmail.com>
2023-10-06 15:53       ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-10-06 16:08         ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-06 16:17           ` Ron <ronljohnsonjr@gmail.com>
2023-10-06 16:37           ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-06 20:28             ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-06 14:55   ` Rob Sargent <robjsargent@gmail.com>
2023-10-06 15:03     ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-06 15:06     ` Dominique Devienne <ddevienne@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox