Re: Alter domain type / avoiding table rewrite - Mailing list pgsql-general

From Ron
Subject Re: Alter domain type / avoiding table rewrite
Date
Msg-id 54c9d5ab-9be5-1499-1c36-f45ea33cc322@gmail.com
Whole thread Raw
In response to Alter domain type / avoiding table rewrite  (Tim Kane <tim.kane@gmail.com>)
Responses Re: Alter domain type / avoiding table rewrite  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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.



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Alter domain type / avoiding table rewrite
Next
From: Adrian Klaver
Date:
Subject: Re: Alter domain type / avoiding table rewrite