On 4/16/19 7:12 AM, Tom Lane wrote:
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.
I suspect the OP wants the type to text with a CHECK constraint to allow
for increasing the length of field values in the future by just changing
the CHECK setting. If that is the case would changing the type to text
and then adding a CHECK NOT VALID work without too much pain?
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
