Practice PostgreSQL

Create a users table with the following columns: - id — auto-incrementing primary key - username — unique, not null, up to 50 chars - email — unique, not null - is_active — boolean, defaults to true - created_at — timezone-aware timestamp, defaults to the current time

CREATE TABLE users (
  -- your columns here
);

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  username    VARCHAR(50)  UNIQUE NOT NULL,
  email       TEXT         UNIQUE NOT NULL,
  is_active   BOOLEAN      NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

Create a posts table that references the users table from the previous exercise: - id — auto-incrementing primary key - user_id — foreign key referencing users(id), deletes cascade - title — not null text - body — text, nullable - published_at — timezone-aware timestamp, nullable

CREATE TABLE posts (
  id            SERIAL PRIMARY KEY,
  user_id       INTEGER      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title         TEXT         NOT NULL,
  body          TEXT,
  published_at  TIMESTAMPTZ
);

1. Create a non-unique index on posts.user_id to speed up lookups by user. 2. Create a non-unique index on posts.published_at to speed up date-range queries. 3. Run EXPLAIN ANALYZE on a query that fetches all posts for user_id = 1 ordered by published_at DESC. Write all three statements.

CREATE INDEX ON posts (user_id);
CREATE INDEX ON posts (published_at);

EXPLAIN ANALYZE
SELECT *
FROM   posts
WHERE  user_id = 1
ORDER  BY published_at DESC;

1. Add a metadata column of type JSONB to the posts table (allow null). 2. Update the post with id = 1 to set metadata to {"tags": ["postgres", "sql"], "views": 42}. 3. Write a query that selects the title and the views value (as text) from all posts where the metadata contains the key "views". Use the JSONB operators -> and ->>.

-- Step 1: add the column
ALTER TABLE posts ADD COLUMN metadata JSONB;

-- Step 2: set metadata for post 1
UPDATE posts
SET    metadata = '{"tags": ["postgres", "sql"], "views": 42}'::jsonb
WHERE  id = 1;

-- Step 3: query posts that have a 'views' key
SELECT title,
       metadata ->> 'views' AS views
FROM   posts
WHERE  metadata ? 'views';

Wrap the following operations in a single transaction: 1. Insert a new user with username = 'alice' and email = 'alice@example.com'. If a user with that email already exists, update username to the new value and set is_active = true. 2. Insert a post with title = 'Hello World' for the user whose email = 'alice@example.com'. 3. If any step fails, roll back everything. Write the complete transaction block.

BEGIN;

-- Upsert the user
INSERT INTO users (username, email, is_active)
VALUES ('alice', 'alice@example.com', true)
ON CONFLICT (email)
DO UPDATE SET
  username  = EXCLUDED.username,
  is_active = true;

-- Insert the post referencing the upserted user
INSERT INTO posts (user_id, title)
SELECT id, 'Hello World'
FROM   users
WHERE  email = 'alice@example.com';

COMMIT;

Given a table orders(id, customer_id, total, created_at), write a single query that returns: - customer_id - order_id (the id column) - total - rank_by_total — the rank of each order within its customer, ordered by total DESC (ties share the same rank) - running_total — cumulative sum of total for that customer, ordered by created_at ASC

SELECT
  customer_id,
  id AS order_id,
  total,
  -- rank_by_total here
  -- running_total here
FROM orders
ORDER BY customer_id, created_at;

SELECT
  customer_id,
  id AS order_id,
  total,
  RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_total,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY customer_id, created_at;

Given a table employees(id, name, manager_id) where manager_id is NULL for the CEO, write a recursive CTE that returns every employee under manager id = 1 (inclusive), along with their depth (0 for the root) and a path string like 'Alice > Bob > Carol'.

WITH RECURSIVE hierarchy AS (
  -- base case
  
  UNION ALL
  
  -- recursive case
)
SELECT depth, path FROM hierarchy ORDER BY path;

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 0 AS depth, name::text AS path
  FROM employees
  WHERE id = 1

  UNION ALL

  SELECT e.id, e.name, e.manager_id, h.depth + 1, h.path || ' > ' || e.name
  FROM employees e
  JOIN hierarchy h ON h.id = e.manager_id
)
SELECT depth, path FROM hierarchy ORDER BY path;

Given a posts(id, title, body) table: 1. Add a stored generated column fts of type tsvector that indexes both title and body using the 'english' configuration. 2. Create a GIN index on the new column. 3. Write a query that returns id and title for all posts matching the phrase 'postgres index', ordered by ts_rank descending.

-- Step 1: add generated column

-- Step 2: create GIN index

-- Step 3: search query

-- Step 1: add generated column
ALTER TABLE posts
  ADD COLUMN fts tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

-- Step 2: create GIN index
CREATE INDEX idx_posts_fts ON posts USING GIN (fts);

-- Step 3: search query
SELECT id, title,
       ts_rank(fts, query) AS rank
FROM posts,
     plainto_tsquery('english', 'postgres index') AS query
WHERE fts @@ query
ORDER BY rank DESC;