From: | Tim Kane <tim(dot)kane(at)gmail(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Alter domain type / avoiding table rewrite |
Date: | 2019-04-16 09:22:27 |
Message-ID: | CADVWZZKjhV9fLpewPdQMZx7V6kvGJViwMEDrPAv9m50rGeK9UA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-04-16 12:05:17 | Re: Error while using pg_dump |
Previous Message | Raghavendra Rao J S V | 2019-04-16 07:07:06 | Error while using pg_dump |