User Defined Data Types
yours truely
- 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
-- 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
-- Example 3
-- check email domain
CREATE DOMAIN
proper_email VARCHAR(150)
CHECK
( VALUE ~* '^[A-Za-z0-9._%-][email protected][A-Za-z0-9.-]+[.][A-Za-z]+$' );
CREATE TABLE email_check (
client_email proper_email
);
insert into email_check (client_email)
values ('[email protected]') ;
-- error
insert into email_check (client_email)
values ('[email protected]#.com') ;
-- 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');
Syntax :
(composite_column).city
-- 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
CREATE TYPE currency AS ENUM(
'USD','EUR','GBP','CHF'
);