Tim Kane <tim.kane@xxxxxxxxx> writes: > 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) > 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. No, that's not going to work: coercing to a domain that has any constraints is considered to require a rewrite. You could cast down to varchar(9) without a rewrite, and you could cast from there to varchar(12) without a rewrite, and it should work to do that in one step. If you really want a domain in there, I'd try creating the domain without any constraint, then doing the ALTER TABLE, then adding the constraint with ALTER DOMAIN. But TBH, that "new_type" is going to be a huge performance drag compared to plain varchar(12). I'd only recommend using a domain when there is no other way to get the check you need. PG just doesn't support domains very well (especially before the work I did for v12...) regards, tom lane
