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