Re: Alter domain type / avoiding table rewrite

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@aklaver.com