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.
