Re: Alter domain type / avoiding table rewrite

On 4/16/19 7:16 AM, Adrian Klaver wrote:
> On 4/16/19 7:12 AM, Tom Lane wrote:
>> 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.
>
> I suspect the OP wants the type to text with a CHECK constraint to allow
^ to change
> 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(dot)klaver(at)aklaver(dot)com