SQL : User Defined Datatypes

This guide is for PostgreSQL : https://www.postgresql.org/

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

CREATE DOMAIN

• Create user defined data type with a range, optional, DEFAULT, NOT NULL and CHECK Constraint.
• They are unique within schema scope.
• Helps standardise your database types in one place.
• Composite Type : Only single value return

CREATE DOMAIN name datatype constraint

-- ex 1
-- 'addr' with domain VARCHAR(100)
CREATE DOMAIN addr VARCHAR(100) NOT NULL;

CREATE TABLE locations (
    address addr
);

            Table "public.locations"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 address | addr |           |          | 


-- Dropping Constraints
-- if domain isnt used anywhere
drop domain addr;    

-- this will drop the column in the table it is present in
-- use this with caution
drop domain addr CASCADE;

-- List all domains inside a schema
select typname from pg_catalog.pg_type 
 join pg_catalog.pg_namespace 
 on pg_namespace.oid = pg_type.typnamespace
 where typtype = 'd' and nspname = 'public';

      typname      
------------------
 positive_numeric
 valid_color
 addr

Number Based Components

-- Example 2
-- 'positive_numeric' : value > 0

CREATE DOMAIN positive_numeric 
    INT NOT NULL CHECK (VALUE > 0);

CREATE TABLE sample (
    number positive_numeric
);

INSERT INTO sample (NUMBER) VALUES (10);

-- error
INSERT INTO sample (NUMBER) VALUES (-10);

-- ERROR:  value for domain positive_numeric 
--    violates check constraint "positive_numeric_check"

SELECT * FROM sample;

 number 
--------
     10

Text Based Domain

-- Example 3
-- check email domain

CREATE DOMAIN 
     proper_email VARCHAR(150) 
CHECK 
    ( VALUE ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$' );


CREATE TABLE email_check (
    client_email proper_email
);

insert into email_check (client_email) 
    values ('[e-mail]') ;

-- error 
insert into email_check (client_email) 
    values ('a@#.com') ;

Enum Based Domain

-- enum based domain
CREATE DOMAIN valid_color VARCHAR(10)
CHECK (VALUE IN ('red','green','blue'));

CREATE TABLE color (
    color valid_color
);

INSERT INTO color (color) 
    VALUES ('red'),('blue'),('green');

-- error
INSERT INTO color (color) 
    VALUES ('yellow');

Composite Data Types

Syntax : (composite_column).city

Example 1

-- address type

CREATE TYPE address AS (
    city VARCHAR(50),
    country VARCHAR(100)
);

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    address address
);

INSERT INTO person ( address ) 
    VALUES (ROW('London','UK')), (ROW('New York','USA'));

select * from person;

 id |     address      
----+------------------
  1 | (London,UK)
  2 | ("New York",USA)

select (address).country from person;

 country 
---------
 UK
 USA

Example 2

CREATE TYPE currency AS ENUM(
    'USD','EUR','GBP','CHF'
);

SELECT 'USD'::currency

 currency 
----------
 USD

SELECT 'INR'::currency

-- ERROR:  invalid input value for enum currency: "INR"
-- LINE 1: SELECT 'INR'::currency

ALTER TYPE currency ADD VALUE 'CHF' AFTER 'EUR';

CREATE TABLE stocks (
    id SERIAL PRIMARY KEY,
    symbol currency
);

insert into stocks ( symbol ) VALUES ('CHF');

select * from stocks
 id | symbol 
----+--------
  1 | CHF

-- DROP TYPE currency;

Alter

Alter TYPE

ALTER TYPE addr RENAME TO user_address

ALTER TYPE user_address OWNER TO uday

ALTER TYPE user_address SET SCHEMA test_scm

ALTER TYPE test_scm.user_address 
    ADD ATTRIBUTE street_address VARCHAR(150)    

CREATE TYPE mycolors AS ENUM ('green','red','blue')

ALTER TYPE mycolors RENAME VALUE 'red' TO 'orange'

SELECT enum_range(NULL::mycolors);

ALTER TYPE mycolors ADD VALUE 'red' BEFORE 'green'

ALTER ENUM

CREATE TYPE status_enum AS enum 
('queued','waiting','running','done');

CREATE TABLE jobs (
    id SERIAL PRIMARY KEY,
    job_status status_enum
);

INSERT INTO jobs ( job_status ) VALUES 
    ('queued'),('waiting'),('running'),('done');

SELECT * FROM jobs;

 id | job_status 
----+------------
  1 | queued
  2 | waiting
  3 | running
  4 | done

-- UPDATING waiting to running

UPDATE jobs SET job_status = 'running' 
    WHERE job_status = 'waiting';

 id | job_status 
----+------------
  1 | queued
  3 | running
  4 | done
  2 | running

Updating/Replacing ENUM domain

ALTER TYPE status_enum RENAME TO status_enum_old;

CREATE TYPE status_enum as enum 
    ('queued','running','done');

ALTER TABLE jobs ALTER COLUMN job_status 
    TYPE status_enum USING job_status::text::status_enum;

DROP TYPE status_enum_old;

Default value ENUM

CREATE TYPE  status AS ENUM 
    ('PENDING','APPROVED','DECLINE')

CREATE TABLE cron_jobs (
    id SERIAL,
    status status DEFAULT 'PENDING'
);

INSERT INTO cron_jobs ( status ) VALUES ('APPROVED');

CREATE DOMAIN IF NOT EXISTS

DO
$$
BEGIN
    IF NOT EXISTS ( 
            SELECT 
                * 
            FROM pg_type tp 
            INNER JOIN 
                pg_namespace nsp ON nsp.oid = typ.typnamespace
                WHERE nsp.nspname = current_schema() 
                AND typ.typname = 'a' 
            ) 
        THEN
            CREATE TYPE ai AS ( 
                a TEXT, i INT 
            );
    END IF;
END;
$$
LANGUAGE plpgsql;