PostgreSQL Data Types

CREATE TABLE users (
  country_code  CHAR(2)          NOT NULL,  -- 'IN', 'US', 'GB'
  email         TEXT             NOT NULL,  -- no length needed
  username      VARCHAR(50)      NOT NULL,  -- explicit cap
  bio           TEXT                        -- unlimited, nullable
);

-- char pads with spaces — be careful with comparisons
SELECT 'IN'::CHAR(5) = 'IN   ';  -- → true (padded)
SELECT 'IN'::TEXT   = 'IN   ';  -- → false

-- Concatenation
SELECT 'Hello' || ' ' || 'World';           -- Hello World
SELECT CONCAT('Hello', ' ', 'World');      -- Hello World

-- Case
SELECT UPPER('hello');                    -- HELLO
SELECT LOWER('HELLO');                    -- hello

-- Search
SELECT email FROM users
  WHERE email LIKE '%@gmail.com';         -- ends with @gmail.com

SELECT email FROM users
  WHERE email ILIKE '%@GMAIL.COM';       -- case-insensitive LIKE

-- Length, trim
SELECT LENGTH('hello');                   -- 5
SELECT TRIM('  hello  ');               -- 'hello'

-- Substring
SELECT SUBSTRING('PostgreSQL', 1, 4);    -- 'Post'

-- BTree: fast for equality and prefix
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'alice@example.com'; -- uses index
SELECT * FROM users WHERE email LIKE 'alice%';         -- uses index
SELECT * FROM users WHERE email LIKE '%gmail%';       -- ❌ full scan

-- pg_trgm extension: enables contains/suffix indexing
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users
  USING gin(email gin_trgm_ops);
SELECT * FROM users WHERE email LIKE '%gmail%';       -- ✅ now uses index

-- Set session timezone
SET timezone = 'Asia/Kolkata';

CREATE TABLE orders (
  order_id    BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Insert in IST, stored as UTC internally
INSERT INTO orders DEFAULT VALUES;

-- Query returns in current session timezone (IST)
SELECT created_at FROM orders;
-- → 2025-01-15 20:00:00+05:30

-- Convert to UTC explicitly
SELECT created_at AT TIME ZONE 'UTC' FROM orders;
-- → 2025-01-15 14:30:00

-- Current date and time
SELECT NOW();              -- timestamptz of now
SELECT CURRENT_DATE;      -- just the date
SELECT CURRENT_TIME;      -- just the time

-- Arithmetic with intervals
SELECT NOW() + '7 days'::INTERVAL;        -- 7 days from now
SELECT NOW() - '1 hour'::INTERVAL;        -- 1 hour ago

-- Extract parts
SELECT EXTRACT(YEAR  FROM NOW());          -- 2025
SELECT EXTRACT(MONTH FROM NOW());          -- 1
SELECT DATE_PART('hour', NOW());           -- 14 (hour of day)

-- Truncate
SELECT DATE_TRUNC('month', NOW());         -- first moment of this month
SELECT DATE_TRUNC('day',   NOW());         -- midnight today

-- Diff between two timestamps (→ interval)
SELECT '2025-12-31'::DATE - CURRENT_DATE;  -- days until new year

CREATE TABLE subscriptions (
  user_id    INTEGER,
  started_at TIMESTAMPTZ,
  duration   INTERVAL           -- '1 month', '1 year', '14 days'
);

-- Computed expiry
SELECT user_id,
       started_at + duration AS expires_at
FROM   subscriptions
WHERE  started_at + duration > NOW();   -- active subs

-- Interval literals
SELECT '30 days'::INTERVAL;
SELECT '2 years 3 months 5 days'::INTERVAL;

-- Always index created_at / updated_at on high-volume tables
CREATE INDEX idx_orders_created ON orders(created_at);

-- Range queries use the index efficiently
SELECT * FROM orders
WHERE created_at >= DATE_TRUNC('month', NOW());  -- orders this month

-- ⚠️ Wrapping in a function defeats the index
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025;    -- ❌ full scan

-- Fix: use a range instead
SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31 23:59:59'; -- ✅

CREATE TABLE products (
  id          INTEGER    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_active   BOOLEAN    NOT NULL DEFAULT TRUE,
  is_deleted  BOOLEAN    NOT NULL DEFAULT FALSE,
  is_featured BOOLEAN              -- nullable: unknown / unset
);

-- Accepted literals for TRUE
SELECT TRUE, 'true'::BOOLEAN, 'yes'::BOOLEAN, 'on'::BOOLEAN, '1'::BOOLEAN;

-- Querying booleans
SELECT * FROM products WHERE is_active;          -- WHERE is_active = TRUE
SELECT * FROM products WHERE NOT is_deleted;     -- active products

-- NULL boolean needs IS NULL / IS NOT NULL
SELECT * FROM products WHERE is_featured IS NULL; -- not yet set

-- OLTP soft delete pattern (from Topic 2)
ALTER TABLE products
  ADD COLUMN is_deleted  BOOLEAN     NOT NULL DEFAULT FALSE,
  ADD COLUMN deleted_at  TIMESTAMPTZ;

-- Soft delete
UPDATE products
SET    is_deleted = TRUE, deleted_at = NOW()
WHERE  id = 42;

-- Partial index: only index active products
CREATE INDEX idx_active_products
  ON products(id)
  WHERE is_deleted = FALSE;   -- tiny, fast index!

-- Enable uuid-ossp or pgcrypto extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(), -- v4
  email      TEXT        NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO users(email) VALUES ('alice@example.com');
SELECT * FROM users;
-- id → 'a3f1b2c4-...'  (auto-generated)

-- Manual UUID
SELECT gen_random_uuid();

CREATE TABLE users (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id  UUID        NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  email      TEXT        NOT NULL UNIQUE
);

-- Internal joins use BIGINT id (fast)
-- External API returns public_id (safe, opaque)

-- GIN index: makes all @> and ? operators fast
CREATE INDEX idx_products_meta ON products USING gin(meta);

-- Expression index: index a single JSON field
CREATE INDEX idx_products_brand
  ON products((meta->>'brand'));

-- Now this query uses the index
SELECT * FROM products WHERE meta->>'brand' = 'Apple';

-- Add / update a key
UPDATE products
SET    meta = meta || '{"discount": 0.1}'::JSONB
WHERE  id = 1;

-- Remove a key
UPDATE products
SET    meta = meta - 'discount'
WHERE  id = 1;

-- jsonb_set: set nested path
UPDATE products
SET    meta = jsonb_set(meta, '{specs,ram}', '32')
WHERE  id = 3;

CREATE TABLE articles (
  id         INTEGER    PRIMARY KEY,
  title      TEXT       NOT NULL,
  tags       TEXT[],             -- array of text tags
  related_ids INTEGER[]          -- array of related article IDs
);

INSERT INTO articles VALUES
  (1, 'PostgreSQL Arrays',  ARRAY['postgres','database','sql'], ARRAY[2,3]),
  (2, 'JSONB Deep Dive',    ARRAY['postgres','json'],           ARRAY[1]),
  (3, 'Indexing Strategies',ARRAY['indexes','performance'],       ARRAY[1,2]);

-- Element access (1-indexed!)
SELECT tags[1] FROM articles WHERE id = 1;  -- 'postgres'

-- Length
SELECT array_length(tags, 1) FROM articles;  -- dimension 1 length

-- @> contains (left contains all elements of right)
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];
-- articles 1 and 2 (both contain 'postgres')

-- && overlap (any element in common)
SELECT * FROM articles
WHERE  tags && ARRAY['json', 'performance'];  -- articles 2 and 3

-- ANY / ALL
SELECT * FROM articles WHERE 'postgres' = ANY(tags);
SELECT * FROM articles WHERE 'public'   = ALL(tags);  -- all elements match

-- Append / prepend / concatenate
UPDATE articles
SET    tags = tags || ARRAY['featured']
WHERE  id = 1;

-- Unnest: expand array to rows
SELECT id, UNNEST(tags) AS tag FROM articles;
-- Returns one row per tag per article

-- GIN index on array column — makes @> and && fast
CREATE INDEX idx_articles_tags ON articles USING gin(tags);

-- Now fast even on millions of rows
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];

-- Define enum types
CREATE TYPE order_status AS ENUM (
  'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled'
);

CREATE TYPE user_role AS ENUM (
  'guest', 'user', 'moderator', 'admin', 'superadmin'
);

CREATE TABLE orders (
  id         BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status     order_status NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Insert
INSERT INTO orders(status) VALUES ('confirmed');

-- Fails! 'lost' is not a valid enum value
INSERT INTO orders(status) VALUES ('lost');  -- ❌ ERROR

-- Ordering uses the defined sequence
SELECT * FROM orders ORDER BY status;
-- → pending, confirmed, processing, shipped, delivered, cancelled

-- Range comparison
SELECT * FROM orders
WHERE  status > 'confirmed';       -- processing, shipped, delivered, cancelled

-- Inspect all enum values
SELECT unnest(enum_range(NULL::order_status));
-- → pending, confirmed, processing, ..., cancelled

-- Add a new value
ALTER TYPE order_status
  ADD VALUE 'refunded' AFTER 'delivered';

-- Rename a value (PG 10+)
ALTER TYPE order_status
  RENAME VALUE 'cancelled' TO 'canceled';

-- ❌ CANNOT remove a value — must drop & recreate type
-- Plan your enum values carefully upfront!