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 ('a@b.com') ;
-- 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;
Top comments (0)