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.
This seems to boil down to DOMAIN types not being considered as binary compatible..
I've tried using a custom CAST..
CREATE CAST (old_type AS new_type) WITHOUT FUNCTION AS IMPLICIT;
But that blows up, with:
WARNING: cast will be ignored because the source date is a domain
ERROR: domain data types must not Be marked binary compatible
So I'm a little stuck at this point.
I feel like - if I can prove that the binary representation of both domains are truly identical - I might be forced to modify the system tables as a work around.. that scares me on a production system.
Is there a way around this that i'm not seeing?
I'm on PostgreSQL 9.6.2