Re: [HACKERS] generated columns

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.