Re: Alter domain type / avoiding table rewrite

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.)

--
Angular momentum makes the world go 'round.