On 4/16/19 9:28 AM, Adrian Klaver wrote:
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.
But there's no CHECK constraint.
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
NOT VALID is the part that obviates my concern.
--
Angular momentum makes the world go 'round.
