I have a PostgreSQL DOMAIN "mydomain" based on NUMERIC(8,2). I need to change it to be based on NUMERIC (12, 2). That DOMAIN is used by about 50 tables.

ALTER DOMAIN doesn't allow to modify the domain's base type. It would be great if we could simply do something like ALTER DOMAIN domain SET TYPE new_type. Alas, it's not supported...

What I'm about to do :

  1. Create a new domain "mydomain2", based on NUMERIC(12,2)
  2. ALTER the columns of the tables using mydomain, and set the type of those columns to mydomain2
  3. Drop mydomain
  4. Rename mydomain2 to mydomain

I see no reason this won't work, but it's a bit cumbersome (I'll have to ALTER about 50 tables).

Is there a faster way to do this?

  • Unfortunately, not. The domain concept is a nice idea, but the fact that you can't change the data type limits its usefulness quite severely. – a_horse_with_no_name Jul 22 '20 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.