Working with Data Types

CREATE TABLE prices (amount numeric(12,2) NOT NULL);

SELECT INTERVAL '1 year 2 months 3 days 04:05:06';
SELECT now() + INTERVAL '30 days';
SELECT EXTRACT(EPOCH FROM INTERVAL '1 day');  -- 86400

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();              -- v4
-- PG 18+ has gen_uuid_v7() for time-ordered UUIDs

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  payload jsonb NOT NULL
);
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

CREATE TABLE posts (
    id   bigserial PRIMARY KEY,
    tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO posts(tags) VALUES (ARRAY['sql','postgres','tips']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);
CREATE INDEX posts_tags_gin ON posts USING gin(tags);

CREATE TYPE order_status AS ENUM ('new','paid','shipped','cancelled');
ALTER TABLE orders ALTER COLUMN status TYPE order_status USING status::order_status;
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';

CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE people (id bigserial PRIMARY KEY, home address);
INSERT INTO people(home) VALUES (ROW('1 Main','Boston','02110'));
SELECT (home).city FROM people;

CREATE DOMAIN email_addr AS text
  CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]+
      

        
Benefit
Detail
        
Reuse
Apply same CHECK to many columns
        
Centralize
Change rule in one place
      

      
14. Using Range Types
      

        
Type
Element
        
int4range / int8range
integer / bigint
        
numrange
numeric
        
tsrange / tstzrange
timestamp / timestamptz
        
daterange
date
      
      

  
CREATE TABLE reservations (
   room_id int NOT NULL,
   during  tstzrange NOT NULL,
   EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

  
    
  


      
15. Using Network Types
      

        
Type
Use
        
inet
Host or network IPv4/IPv6 with optional CIDR
        
cidr
Strict network (no host bits)
        
macaddr
6-byte MAC address
        
macaddr8
8-byte EUI-64 MAC
      
      

  
SELECT '192.168.1.10'::inet << '192.168.1.0/24'::cidr;   -- true

  
    
  

    );
CREATE TABLE users (id bigserial PRIMARY KEY, email email_addr NOT NULL);

CREATE TABLE reservations (
   room_id int NOT NULL,
   during  tstzrange NOT NULL,
   EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

SELECT '192.168.1.10'::inet << '192.168.1.0/24'::cidr;   -- true