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
