CREATE TYPE and CREATE DOMAIN

CREATE TYPE AS ENUM#

CREATE TYPE name AS ENUM ('label' [, ...])

1. 01Create

   CREATE TYPE employee_status AS ENUM ('active', 'on_leave', 'terminated');

2. 02Use as column

   CREATE TABLE staff (id BIGINT PRIMARY KEY, status employee_status NOT NULL DEFAULT 'active');
   INSERT INTO staff VALUES (1, 'active'), (2, 'on_leave');

3. 03Query

   SELECT * FROM staff WHERE status = 'active' ORDER BY id;
   SELECT enum_range(NULL::employee_status);

Behavior: user OID assigned from FIRST_USER_OID=16384; labels stored in catalog; comparison is label-order; invalid label on insert → error. DROP TYPE [IF EXISTS] name [CASCADE|RESTRICT].

CREATE TYPE AS COMPOSITE#

CREATE TYPE name AS (col TYPE [, ...])

CREATE TYPE address_type AS (street TEXT, city TEXT, zip TEXT);
CREATE TABLE customers (id BIGINT PRIMARY KEY, addr address_type);
INSERT INTO customers VALUES (1, ROW('5 Main', 'Springfield', '12345'));
SELECT (addr).city FROM customers;
SELECT json_person FROM ...;  -- see tests § composite

attributes: Some([...]) in AST; None = enum, Some([]) = standalone composite without columns. (record).field access works in expressions.

CREATE DOMAIN#

CREATE DOMAIN name AS base_type [CONSTRAINT cname CHECK (expr)] [CHECK (expr) ...]

CREATE DOMAIN positive_integer AS integer CONSTRAINT positive CHECK (VALUE > 0);
CREATE DOMAIN email AS TEXT CHECK (VALUE LIKE '%@%');
CREATE TABLE t (id positive_integer PRIMARY KEY, contact email);
INSERT INTO t VALUES (1, '[email protected]');       -- ok
INSERT INTO t VALUES (-1, '[email protected]');      -- CHECK violation
INSERT INTO t VALUES (2, 'nope');          -- CHECK violation
DROP DOMAIN [IF EXISTS] positive_integer [CASCADE|RESTRICT];

VALUE keyword is the domain input reference (also usable as column name elsewhere). Multiple CHECKs all enforced. Domains carry base, typmod, not_null in Domain{...} registry entry.

Casts and typmod#

SELECT 'active'::employee_status, CAST('5' AS positive_integer);
SELECT 'x'::VARCHAR(10), '1.5'::NUMERIC(12,2);

can_be_column_type() gates pseudo-types; record only via ROW(...).

Related#

Types · ENUM/DOMAIN deep dive · Constraints