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.
#!/bin/bash
echo "Gene Ratio H/L normalized Exp1
Dhx9 NaN
Gapdh 0.42288
Gm8797 0.81352
Aldh2 0.89913
Ccdc12 NaN
Hip1 NaN
Hist1h2aa 0.66911
Tpm2 0.57535
Fasn NaN
Aldoa 0.61898
Unc13b NaN
Wrn 0.0050816
Psma1 NaN
Ldha 0.90211
Numa1 NaN" > /tmp/pg_head.txt
psql -Xa << FT_SETUP_TXT
create schema if not exists "tmp";
create server if not exists "tmpserver" foreign data wrapper file_fdw;
drop foreign table if exists tmp.pg_head cascade;
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'
);
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
;
FT_SETUP_TXT
psql -qXa << SQL_TXT
-- 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 ;
-- but this fails
select
"Gene"
, "Ratio H/L normalized Exp1 Log2 (Generated column)"
from tmp.pg_head
limit 3 ;
SQL_TXT
