Re: Alter domain type / avoiding table rewrite

Tim Kane <tim(dot)kane(at)gmail(dot)com> 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