Re: [HACKERS] generated columns
От | Erik Rijkers |
---|---|
Тема | Re: [HACKERS] generated columns |
Дата | |
Msg-id | d873b54908ddc08e9649576fdf169d25@xs4all.nl обсуждение исходный текст |
Ответ на | Re: [HACKERS] generated columns (Erik Rijkers <er@xs4all.nl>) |
Ответы |
Re: [HACKERS] generated columns
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
Re: [HACKERS] generated columns (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>) |
Список | pgsql-hackers |
On 2019-04-02 15:36, Erik Rijkers wrote: > On 2019-04-02 14:43, Peter Eisentraut wrote: >> On 2019-04-01 10:52, Peter Eisentraut wrote: >>> On 2019-03-31 05:49, Erik Rijkers wrote: >>>> STORED in a >>>> file_fdw foreign table still silently creates the column which then >>>> turns out to be useless on SELECT, with an error like: >>>> >>>> "ERROR: column some_column_name is a generated column >>>> DETAIL: Generated columns cannot be used in COPY." >>>> >>>> Maybe it'd be possible to get an error earlier, i.e., while trying >>>> to >>>> create such a useless column? >>> >>> I'll look into it. >> >> I've been trying to create a test case for file_fdw for this, but I'm >> not getting your result. Can you send a complete test case? attached is run_ft.sh which creates a text file: /tmp/pg_head.txt then sets it up as a foreign table, and adds a generated column. Then selects a succesful select, followed by a error-producing select. Some selects are succesful but some fail. I'm not sure why it sometimes fails (it's not just the explicitness of the generated-column-name like I suggested earlier). My output of run_ft.sh is below. $ ./run_ft.sh 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.
Вложения
В списке pgsql-hackers по дате отправления: