On 4/16/19 7:12 AM, Tom Lane wrote:
> Tim Kane <tim.kane@gmail.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.
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@aklaver.com