Getting Started with PostgreSQL

# Connect to default database
psql

# Connect to a specific database
psql -d myapp

# Connect with explicit options
psql -h localhost -p 5432 -U myuser -d myapp

-- Create a database
CREATE DATABASE myapp;

-- Connect to it
\c myapp

-- Create tables
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    body TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create an index for common queries
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published) WHERE published = TRUE;

INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', '$2b$10$abc...');

INSERT INTO posts (user_id, title, body, published)
VALUES (1, 'My First Post', 'Hello, world!', TRUE);

-- Insert multiple rows
INSERT INTO posts (user_id, title, body, published) VALUES
    (1, 'Second Post', 'More content', TRUE),
    (1, 'Draft Post', 'Work in progress', FALSE);

-- Basic query
SELECT * FROM users WHERE username = 'alice';

-- Specific columns with a join
SELECT u.username, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published = TRUE
ORDER BY p.created_at DESC
LIMIT 10;

-- Aggregate
SELECT user_id, COUNT(*) AS post_count
FROM posts
WHERE published = TRUE
GROUP BY user_id
HAVING COUNT(*) > 5;

UPDATE posts
SET title = 'Updated Title', updated_at = NOW()
WHERE id = 1;

-- Update with returning (get the updated row back)
UPDATE users SET email = 'newemail@example.com'
WHERE id = 1
RETURNING id, username, email;

DELETE FROM posts WHERE id = 3;

-- Delete with a condition
DELETE FROM posts
WHERE published = FALSE AND created_at < NOW() - INTERVAL '30 days';

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

INSERT INTO events (event_type, payload) VALUES
    ('user_signup', '{"user_id": 42, "source": "google", "plan": "free"}'),
    ('purchase', '{"user_id": 42, "amount": 29.99, "product": "pro_plan"}');

-- Query JSON fields
SELECT * FROM events
WHERE payload->>'user_id' = '42'
  AND payload->>'source' = 'google';

-- Index JSON fields for performance
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[] DEFAULT '{}'
);

INSERT INTO articles (title, tags)
VALUES ('PostgreSQL Tips', ARRAY['database', 'postgresql', 'sql']);

-- Query arrays
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);

INSERT INTO users (username, email, password_hash)
VALUES ('bob', 'bob@example.com', '$2b$10$xyz...')
RETURNING id, username, created_at;

INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@new-email.com', '$2b$10$new...')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email;

import pg from "pg";

const pool = new pg.Pool({
    host: "localhost",
    port: 5432,
    database: "myapp",
    user: "myuser",
    password: "mypassword"
});

// Parameterized query (prevents SQL injection)
const result = await pool.query(
    "SELECT * FROM users WHERE email = $1",
    ["alice@example.com"]
);

console.log(result.rows[0]);

import psycopg

conn = psycopg.connect("dbname=myapp user=myuser password=mypassword")

with conn.cursor() as cur:
    cur.execute(
        "SELECT * FROM users WHERE email = %s",
        ("alice@example.com",)
    )
    user = cur.fetchone()
    print(user)

conn.close()

-- Check table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1;

-- Vacuum (reclaim space from deleted rows)
VACUUM ANALYZE posts;