CODEWITHSUNDEEP
databasepostgresql
RollinRight
RollinRight

Reputation: 105

How to cast between to two similar composite types when altering the column type?

I am trying to change the type on a column from one composite type to another. The only difference between the two composite types is that the previous one used custom domains that prevented the attribute from being null. Upon running the command:

ALTER TABLE contact_address ALTER COLUMN target TYPE nullable_mailing_address USING target::nullable_mailing_address;

I recieve the error:

ERROR:  cannot cast type mailing_address to nullable_mailing_address

What is the proper way to cast between two similar composite types? Thanks!

Upvotes: 2

Views: 399

Answers (1)

Abelisto
Abelisto

Reputation: 15624

Decompose and then compose the old type to the new one. If you just changed the field type, for example:

ALTER TABLE contact_address
  ALTER COLUMN target TYPE nullable_mailing_address
  USING ((target).non_changed_field, (target).changed_field::new_type);

Almost same if you added/dropped field(s).

Example

Upvotes: 2

Related Questions