On Apr 29, 2005, at 12:21 PM, Tom Lane wrote:
> My recollection is that there are some pretty serious limitations on
> what you can do in this line, but basic cases do work. I think the
> lack
> of an ALTER TYPE that can handle the same cases is just a matter of
> lack
> of round tuits.
>
> regards, tom lane
I see, for example, that adding basic columns work:
test=# alter table testtype drop column v3;
ALTER TABLE
test=# alter table testtype add column v3 int;
ALTER TABLE
But domains cannot be added after the fact:
test=# create domain one_of_three as int check (VALUE in (1,2,3));
CREATE DOMAIN
test=# alter table testtype add column v4 one_of_three;
ERROR: cannot alter table "testtype" because column "testtable"."val"
uses its rowtype
Likewise with something that has a default value, as in your example
listed
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00623.php:
d=# alter table a add column qq timestamp default now() not null;
ERROR: cannot alter table "a" because column "b"."z" uses its rowtype
But domains work well before the type gets used:
test=# create table testtype2 ( v1 one_of_three, v2 one_of_three );
CREATE TABLE
test=# create table uses_tt2 (id int, val testtype2);
CREATE TABLE
test=# insert into uses_tt2 values (1, (2, 3));
INSERT 0 1
test=# insert into uses_tt2 values (1, (3, 5));
ERROR: value for domain one_of_three violates check constraint
"one_of_three_check"
Removing domain columns works, too:
test=# alter table testtype2 drop column v2;
ALTER TABLE
test=# select * from uses_tt2;
id | val
----+-----
1 | (2)
(1 row)
So, adding nontrivial columns to an in-use composite type amounts to
creating a new type and running crossover script(s) to convert the
in-use data, rebuilding any indices on the old type columns, then
dropping the old type. Inconvenient, not insurmountable.
That said -- anyone stepping up to claiming using 'em? Are these things
seen as against the data normalization grain?
----
James Robinson
Socialserve.com
