Set DOMAIN for existing column

Question

I have an existing table like this:

CREATE TABLE public.data (
    id integer,
    name text,
    sell_value real)
);

and I define a domain as:

CREATE DOMAIN dataDomain AS TEXT
CHECK(name = ANY ('{joe, john, jack}'::text[]));

How can I apply the domain to the column "name"?

EDIT: I have to apply the domain in a existing table

score 1 · Accepted Answer · answered Sep 19 '22 at 13:49

You just use it like any other type:

create table public.data (
    id integer,
    name dataDomain,
    ...
);

Documentation with examples is here.

By the way, be aware that PostgreSQL folds identifiers to lower case unless you double-quote them: so dataDomain and datadomain are identical, but different from "dataDomain".

score 1 · Answer 2 · answered Sep 19 '22 at 14:09

You can use ALTER COLUMN fro apply domain policy:

CREATE TABLE data (
    id integer,
    name text,
    sell_value real
);

CREATE DOMAIN data_domain AS TEXT CHECK(VALUE = ANY ('{joe, john, jack}'::text[]));

ALTER TABLE data
ALTER COLUMN name SET DATA TYPE data_domain;

online sql editor

Set DOMAIN for existing column

2 Answers2