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 :
- Create a new domain "mydomain2", based on NUMERIC(12,2)
- ALTER the columns of the tables using mydomain, and set the type of those columns to mydomain2
- Drop mydomain
- 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?