PostgreSQL Advanced

-- Available extensions
SELECT * FROM pg_available_extensions;

-- Install an extension
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Check installed extensions
SELECT * FROM pg_extension;

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    geom GEOMETRY(POINT, 4326) NOT NULL  -- WGS84 (GPS coordinates)
);

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Insert a point (longitude, latitude)
INSERT INTO locations (name, geom)
VALUES ("San Francisco', ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

-- Find locations within 10km of a point
SELECT name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(-122.4, 37.77), 4326)) AS distance_meters
FROM locations
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(-122.4, 37.77), 4326), 10000)
ORDER BY distance_meters;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN trigram index for fast ILIKE
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- Fast case-insensitive partial match
SELECT * FROM users WHERE name ILIKE '%john%';

-- Similarity search (0 to 1, where 1 is exact match)
SELECT name, similarity(name, 'John Smith') AS sim
FROM users
WHERE name % 'John Smith'
ORDER BY sim DESC;

-- Adjust similarity threshold
SET pg_trgm.similarity_threshold = 0.3;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Generate UUIDs
SELECT gen_random_uuid();
-- Returns: 550e8400-e29b-41d4-a716-446655440000

-- Hash functions
SELECT digest('password', 'sha256');
SELECT crypt('password', gen_salt('bf'));

-- Verify password
SELECT (crypt('password', stored_hash) = stored_hash) AS is_valid
FROM users WHERE email = 'user@example.com';

-- PGP encryption
SELECT pgp_sym_encrypt('secret data', 'encryption_key');
SELECT pgp_sym_decrypt(encrypted_column, 'encryption_key');

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes HSTORE
);

INSERT INTO products (name, attributes)
VALUES ('Widget', 'color => "red", weight => "2.5", material => "steel"');

-- Query hstore fields
SELECT name FROM products WHERE attributes -> 'color' = 'red';
SELECT name, attributes -> 'weight' AS weight FROM products;

-- GIN index for hstore containment
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Containment queries
SELECT * FROM products WHERE attributes @> 'color => "red"';
SELECT * FROM products WHERE attributes ? 'weight';  -- has key 'weight'

CREATE EXTENSION IF NOT EXISTS uuid-ossp;

-- UUID v1 (MAC address + timestamp)
SELECT uuid_generate_v1();

-- UUID v4 (random)
SELECT uuid_generate_v4();

-- UUID v5 (SHA-1 hash of namespace + name, deterministic)
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (
    username CITEXT PRIMARY KEY,
    email CITEXT NOT NULL
);

-- Both insertions fail (case-insensitive uniqueness)
INSERT INTO accounts VALUES ('Alice', 'alice@example.com');
INSERT INTO accounts VALUES ('alice', 'alice@other.com');
-- ERROR: duplicate key value violates unique constraint

-- Must be loaded at server start
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time
SELECT query, calls, total_exec_time / 1000 AS total_ms,
       mean_exec_time / 1000 AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Set wal_level = logical in postgresql.conf and restart
-- wal_level = logical

CREATE PUBLICATION pub_orders FOR TABLE orders;
CREATE PUBLICATION pub_all_tables FOR ALL TABLES;

-- Publication with filter
CREATE PUBLICATION pub_active_orders FOR TABLE orders
    WHERE (status = 'active' AND created_at >= '2024-01-01');

CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=publisher.example.com dbname=mydb user=replicator'
    PUBLICATION pub_orders
    WITH (create_slot = true, slot_name = 'sub_orders_slot');

-- Verify subscription status
SELECT * FROM pg_stat_subscription;

-- Configure conflict resolution
ALTER SUBSCRIPTION sub_orders SET (slot_name = 'sub_orders_slot');

-- Handle conflicts (check pg_stat_subscription_stats)
SELECT subname, slot_name, sync_error_count, apply_error_count
FROM pg_stat_subscription_stats;

-- Common conflicts:
-- 1. insert_duplicate_key: row already exists on subscriber
-- 2. update_conflict: row version mismatch
-- Solutions: skip errors, or use trigger-based conflict resolution
ALTER SUBSCRIPTION sub_orders SKIP (ALTER TABLE orders REPLICA IDENTITY FULL);

-- Default: primary key
ALTER TABLE orders REPLICA IDENTITY DEFAULT;

-- Use a unique index
ALTER TABLE orders REPLICA IDENTITY USING INDEX idx_orders_order_id;

-- Full row comparison (no unique index available, slower)
ALTER TABLE orders REPLICA IDENTITY FULL;

-- No row identity (deletes are not replicated)
ALTER TABLE orders REPLICA IDENTITY NOTHING;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create the foreign server
CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote.example.com', port '5432', dbname 'analytics');

-- Create user mapping (local user → remote user)
CREATE USER MAPPING FOR local_app
    SERVER remote_db
    OPTIONS (user 'analytics_user', password 'secret');

-- Import remote tables
IMPORT FOREIGN SCHEMA public LIMIT TO (events, users)
    FROM SERVER remote_db INTO public;

-- Or create individual foreign tables
CREATE FOREIGN TABLE remote_events (
    event_id BIGINT,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ
) SERVER remote_db OPTIONS (schema_name 'public', table_name 'events');

-- Query the remote table (PostgreSQL pushes down WHERE and LIMIT)
SELECT event_type, COUNT(*) FROM remote_events
WHERE created_at >= '2024-01-01'
GROUP BY event_type;

-- Join local and remote tables
SELECT u.name, COUNT(r.event_id) AS event_count
FROM users u
JOIN remote_events r ON u.user_id = r.user_id
WHERE r.created_at >= '2024-01-01'
GROUP BY u.name;