| 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: | Whole Thread | Raw Message | 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 |