Re: Alter domain type / avoiding table rewrite

On 4/16/19 9:28 AM, Adrian Klaver wrote:
> On 4/16/19 7:19 AM, Ron wrote:
>> On 4/16/19 4:22 AM, Tim Kane wrote:
>>> So I have a situation where I would like to modify a field that is 
>>> currently a domain type over a varchar(9)
>>>
>>> Specifically:
>>> CREATE DOMAIN old_type AS varchar(9)
>>>
>>> This isn't ideal, let's just say.. legacy.
>>>
>>>
>>> I wish to modify this type.. ideally to a text type with a length 
>>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>>
>>> CREATE DOMAIN new_type AS text;
>>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= 
>>> 12)) NOT VALID;
>>>
>>> ALTER TABLE target ALTER
>>> COLUMN value SET DATA TYPE new_type;
>>>
>>>
>>> But it seems impossible to achieve either without a full table rewrite.
>>
>> But the column only has -- at most -- 9 characters of data in it. Won't 
>> the CHECK constraint instantly fail?  (ISTM that you should add the check 
>> constraint AFTER modifying the length and updating your data.)
>>
>
> Not sure how?:
>
> create table check_test (id integer, fld_1 varchar(12));
> CREATE TABLE
> test=> insert into check_test values (1, '123456789'), (2, '');
> INSERT 0 2
>
> test=> select length(fld_1) from check_test ;
>
>  length
>
> --------
>
>       9
>
>       0
>
> (2 rows)
>
> The lengths would be less then or equal to 12.

But there's no CHECK constraint.

>
> Also the NOT VALID will push the check into the future:
>
> https://www.postgresql.org/docs/9.6/sql-altertable.html
>
> "... If the constraint is marked NOT VALID, the potentially-lengthy 
> initial check to verify that all rows in the table satisfy the constraint 
> is skipped. The constraint will still be enforced against subsequent 
> inserts or updates

NOT VALID is the part that obviates my concern.

-- 
Angular momentum makes the world go 'round.