Changing the datatype with a custom type in postgres custom data type-CodePudding

I have a custom type in Postgres DB called money_with_currency Created as:

CREATE TYPE public.money_with_currency AS (currency_code char(3), amount numeric);

We want to change the type of currency_code from char(3) to varchar.

I thought the code would be something like:

ALTER TYPE public.money_with_currency ALTER ATTRIBUTE currency_code SET DATA TYPE varchar;

But got an error:

  ALTER TYPE public.money_with_currency ALTER ATTRIBUTE currency_code SET DATA TYPE varchar;\n"
** (Postgrex.Error) ERROR 0A000 (feature_not_supported) cannot alter type "money_with_currency" because column "prog_fees.amount" uses it

Any thoughts if there is a solution without having to do manual migration to all columns using the type?

CodePudding user response：

First create another custom type and use an alter table query to change the column type of the prog_fees. Then alter the type of public.money_with_currency. Then again execute a alter query to table using public.money_with_currency type. Then it will work

CodePudding user response：

You will need to create a new type with the desired structure. But as there is no direct cast from the old to the new type, you need to use a row constructor when altering the type of the existing column:

CREATE TYPE money_with_currency_new AS (currency_code text, amount numeric);

alter table prog_fees
  alter column amount type money_with_currency_new 
  using ((amount).currency_code, (amount).amount)::money_with_currency_new;

Note the parentheses around the column name when referencing the type's attributes. They are required.

After that you can drop the old type and rename the new type to the old name:

drop type money_with_currency;

alter type money_with_currency_new
  rename to money_with_currency;