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

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