In Postgres 9.4 Beta 1, I successfully created a domain of type
fuel_domain_. The domain checks that a value is any of 5 possible strings,
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_;
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_;