Re: [HACKERS] generated columns

On 2019/04/03 3:54, Erik Rijkers wrote:
> create schema if not exists "tmp";
> CREATE SCHEMA
> create server if not exists "tmpserver" foreign data wrapper file_fdw;
> CREATE SERVER
> drop   foreign table if exists tmp.pg_head cascade;
> DROP FOREIGN TABLE
> create foreign table           tmp.pg_head (
>     "Gene"                      text,
>     "Ratio H/L normalized Exp1" numeric
> )
> server tmpserver
> options (
>     delimiter E'\t'
>   , format 'csv'
>   , header 'TRUE'
>   , filename      '/tmp/pg_head.txt'
> );
> CREATE FOREIGN TABLE
> alter foreign table tmp.pg_head
>    add column "Ratio H/L normalized Exp1 Log2 (Generated column)" numeric
> generated always as (case when "Ratio H/L normalized Exp1" > 0 then log(2,
> "Ratio H/L normalized Exp1") else  null end) stored
> ;
> ALTER FOREIGN TABLE
> -- this is OK (although the generated-column values are all empty/null)
> select
>      "Gene"
>    , "Ratio H/L normalized Exp1"
>    , "Ratio H/L normalized Exp1 Log2 (Generated column)"
> from tmp.pg_head
> limit 3 ;
>   Gene  | Ratio H/L normalized Exp1 | Ratio H/L normalized Exp1 Log2
> (Generated column)
> --------+---------------------------+---------------------------------------------------
>
>  Dhx9   |                       NaN |
>  Gapdh  |                   0.42288 |
>  Gm8797 |                   0.81352 |
> (3 rows)
>
> -- but this fails
> select
>     "Gene"
>    , "Ratio H/L normalized Exp1 Log2 (Generated column)"
> from tmp.pg_head
> limit 3 ;
> ERROR:  column "Ratio H/L normalized Exp1 Log2 (Generated column)" is a
> generated column
> DETAIL:  Generated columns cannot be used in COPY.

Fwiw, here's a scenario that fails similarly when using copy, which is
what file_fdw uses internally.

$ cat foo.csv
1

create table foo (a int, b int generated always as (a+1) stored);
copy foo (a, b) from '/tmp/foo.csv';
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.

whereas:

copy foo from '/tmp/foo.csv';
COPY 1

select * from foo;
a │ b
───┼───
1 │ 2
(1 row)

Erik said upthread that generated columns should really be disallowed for
(at least) file_fdw foreign tables [1], because (?) they don't support
inserts. Changing copy.c to "fix the above seems out of the question.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/e61c597ac4541b77750594eea73a774c%40xs4all.nl