On 4/16/19 9:18 AM, Tim Kane wrote:
Thanks everyone..
It seems that the first step:
old_type --> varchar(9)
still requires a table rewrite, while the reverse direction does not.
Hmm:
CREATE DOMAIN old_type AS varchar(9);
create table rewrite_test (id integer, fld_1 old_type);
insert into rewrite_test values (1, '123456789'), (2, '123');
select ctid from rewrite_test;
ctid
-------
(0,1)
(0,2)
alter table rewrite_test alter COLUMN fld_1 set data type varchar(9);
select ctid from rewrite_test;
ctid
-------
(0,1)
(0,2)
update rewrite_test set fld_1 = '1' where id =2;
select ctid from rewrite_test;
ctid
-------
(0,1)
(0,3)
Where are you seeing the rewrite in your case?
I'm curious about the performance implication of domain types, i expect
that cost is only at insert/update time? I guess we've been wearing that
cost up until now.
Adrian is correct - the intention for the DOMAIN with CHECK approach was
to allow flexibility moving forward, as the data set is particularly
large...
I'm now thinking that since promotion to a larger size is a non-issue,
and domain type seems to be not quite the panacea I hoped, then the use
of varchar(n) is perhaps not so terrible!
Thanks for the advice/suggestions/discussion :)
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
