Re: generated columns

On 10 September 2017 at 00:08, Jaime Casanova
<jaime(dot)casanova(at)2ndquadrant(dot)com> wrote:
>
> During my own tests, though, i found some problems:
>

a few more tests:

create table t1 (
id serial,
height_cm int,
height_in int generated always as (height_cm * 10)
) ;

"""
postgres=# alter table t1 alter height_cm type numeric;
ERROR: unexpected object depending on column: table t1 column height_in
"""
should i drop the column and recreate it after the fact? this seems
more annoying than the same problem with views (drop view & recreate),
specially after you implement STORED

"""
postgres=# alter table t1 alter height_in type numeric;
ERROR: found unexpected dependency type 'a'
"""
uh!?

also is interesting that in triggers, both before and after, the
column has a null. that seems reasonable in a before trigger but not
in an after trigger
"""
create function f_trg1() returns trigger as $$
begin
raise notice '%', new.height_in;
return new;
end
$$ language plpgsql;

create trigger trg1 before insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE: <NULL>
INSERT 0 1

create trigger trg2 after insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE: <NULL>
NOTICE: <NULL>
INSERT 0 1
"""

the default value shouldn't be dropped.
"""
postgres=# alter table t1 alter height_in drop default;
ALTER TABLE
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+--------------------------------
id | integer | | not null |
nextval('t1_id_seq'::regclass)
height_cm | integer | | |
height_in | integer | | | generated always as ()
"""
--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services