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
