demo of the PostgreSQL domain type - a possble replacement for the PostgreSQL enum types

pg-domain-type-demo.sql

BEGIN;

-- note: the downside of a DOMAIN type in pg is that the type cannot be modified if it's used in
-- a composite-type column as yet (i hope we don't do that)

-- pgTAP is a suite of test functions to test SQL scripts on PostgreSQL - see https://pgtap.org/
CREATE EXTENSION IF NOT EXISTS pgtap;

-- we will have 4 pgTAP tests
SELECT plan(4);


--
-- first create a domain type with 2 colors ('red' and 'green') and a table with test data
--

CREATE DOMAIN color
    AS TEXT
    CONSTRAINT color_v1 CHECK (
        CASE value
            WHEN 'red' THEN true
            WHEN 'green' THEN true
            ELSE false
        END
    );

CREATE TABLE people (
    name text NOT NULL,
    favorite_color color NOT NULL
);

INSERT INTO people (name, favorite_color)
    VALUES
        ('Alan', 'red'),
        ('Brian', 'green');

-- we cannot insert rows with 'blue'
SELECT throws_ok($$
    INSERT INTO people (name, favorite_color)
        VALUES
            ('Claire', 'blue');
$$, 23514); -- E23514 = check_violation


--
-- introduce a new color ('blue') and add test data that uses it
--

ALTER DOMAIN color
    ADD CONSTRAINT color_v2 CHECK (
        CASE value
            WHEN 'red' THEN true
            WHEN 'green' THEN true
            WHEN 'blue' THEN true
            ELSE false
        END
    );

-- we don't need the old constraint any more
ALTER DOMAIN color
    DROP CONSTRAINT color_v1;

SELECT lives_ok($$
    INSERT INTO people (name, favorite_color)
        VALUES
            ('Claire', 'blue');
$$);


--
-- remove a color ('red') and update the test data using it
--

-- note: we do it in two steps (first adding it as NOT VALID then validating), for this demo it's not necessary, but shows how
-- to do that on a real world example (there those steps would be separate transactions, and if the second one was using the
-- serializable isolation level, it would guarantee that there will be no rows with 'red' after the second transaction,
-- even if other transactions attempt to insert/update such rows into the table)

-- add the new constraint, but for now it's only applied for new/updated rows (not for rows already in the table)
ALTER DOMAIN color
    ADD CONSTRAINT color_v3 CHECK (
        CASE value
            WHEN 'green' THEN true
            WHEN 'blue' THEN true
            ELSE false
        END
    ) NOT VALID;

-- the color_v3 constraint prevents inserting new rows with 'red'
SELECT throws_ok($$
    INSERT INTO people (name, favorite_color)
        VALUES
            ('Diana', 'red');
$$, 23514); -- E23514 = check_violation

-- the color_v3 constraint prevents updating rows to 'red'
SELECT throws_ok($$
    UPDATE people
        SET favorite_color = 'red'
        WHERE name = 'Brian';
$$, 23514); -- E23514 = check_violation

UPDATE people
    SET favorite_color = 'green'
    WHERE favorite_color = 'red';

-- now we make the new constraint valid for existing rows as well
ALTER DOMAIN color
    VALIDATE CONSTRAINT color_v3;

-- we don't need the old constraint any more
ALTER DOMAIN color
    DROP CONSTRAINT color_v2;

--
-- end of tests
--

SELECT * FROM finish();

ROLLBACK;