Re: Alter domain type / avoiding table rewrite - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Alter domain type / avoiding table rewrite
Date
Msg-id 1bd403de-2552-7986-70ec-ac25e7717805@aklaver.com
Whole thread Raw
In response to Re: Alter domain type / avoiding table rewrite  (Ron <ronljohnsonjr@gmail.com>)
Responses Re: Alter domain type / avoiding table rewrite  (Ron <ronljohnsonjr@gmail.com>)
List pgsql-general
On 4/16/19 7:19 AM, Ron wrote:
> On 4/16/19 4:22 AM, Tim Kane wrote:
>> 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)
>>
>> This isn't ideal, let's just say.. legacy.
>>
>>
>> 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.
> 
> But the column only has -- at most -- 9 characters of data in it. Won't 
> the CHECK constraint instantly fail?  (ISTM that you should add the 
> check constraint AFTER modifying the length and updating your data.)
> 

Not sure how?:

create table check_test (id integer, fld_1 varchar(12));
CREATE TABLE
test=> insert into check_test values (1, '123456789'), (2, '');
INSERT 0 2 
 

test=> select length(fld_1) from check_test ; 
 

  length 
 

-------- 
 

       9 
 

       0 
 

(2 rows)

The lengths would be less then or equal to 12.

Also the NOT VALID will push the check into the future:

https://www.postgresql.org/docs/9.6/sql-altertable.html

"... If the constraint is marked NOT VALID, the potentially-lengthy 
initial check to verify that all rows in the table satisfy the 
constraint is skipped. The constraint will still be enforced against 
subsequent inserts or updates (that is, they'll fail unless there is a 
matching row in the referenced table, in the case of foreign keys; and 
they'll fail unless the new row matches the specified check 
constraints). But the database will not assume that the constraint holds 
for all rows in the table, until it is validated by using the VALIDATE 
CONSTRAINT option."


-- 
Adrian Klaver
adrian.klaver@aklaver.com



pgsql-general by date:

Previous
From: Ron
Date:
Subject: Re: Alter domain type / avoiding table rewrite
Next
From: Tom Lane
Date:
Subject: Re: SQLSTATE when PostgreSQL crashes during COMMIT statement