Alter domain type / avoiding table rewrite

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

Responses

Browse pgsql-general by date

  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