Postgresql – How to apply a newly-created Domain to an existing Column

alter-tabledomainpostgresql

In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other.

CREATE DOMAIN fuel_domain_
  AS character varying
  COLLATE pg_catalog."default"
  NOT NULL
  CONSTRAINT fuel_check_ CHECK (VALUE::text = ANY (ARRAY['coal'::character varying, 'gas'::character varying, 'hydro'::character varying, 'wind'::character varying, 'other'::character varying]::text[]));
ALTER DOMAIN fuel_domain_
  OWNER TO postgres_admin_;

➤ How do I apply this new domain to an existing column?

The doc seems to say that a domain should be specified in place of the usual data type when declaring a column.

So I tried and failed to re-define the column’s data type from VARCHAR to the new domain. The base data type of the domain is the same, so the data types should not be in conflict. This SQL for a table unit_ and column fuel_ fails with a syntax error at or near "COLUMN":

ALTER COLUMN unit_.fuel_ SET DATA TYPE fuel_domain_;

WORKAROUND
As a workaround, I was able to delete the column and successfully recreate it with this SQL:

ALTER TABLE unit_ ADD COLUMN fuel_ fuel_domain_;

Best Answer

Incorrect SQL was the problem. The ALTER COLUMN must be part of an ALTER TABLE.

This SQL does indeed alter the data type of the existing column from VARCHAR to the newly-created domain.

ALTER TABLE unit_
ALTER COLUMN fuel_ 
SET DATA TYPE fuel_domain_;
Related Question