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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
