sql

-- Fetch next page after (created_at, id)
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $3;

-- Create table with tsvector column
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  author VARCHAR(100),

-- Partition-based archival strategy

-- Create partitioned table
CREATE TABLE logs (
  id BIGSERIAL,
  level VARCHAR(20),

-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin WITH LOGIN PASSWORD 'secure_password';

-- Grant permissions to roles

-- Create hierarchical table (org chart)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  title VARCHAR(100),
  manager_id INT REFERENCES employees(id),

WITH ordered_events AS (
  SELECT
    customer_id,
    event_time,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_time DESC) AS event_rank,

-- Basic transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Create test database
CREATE DATABASE myapp_test;

-- Test isolation with transactions
/*
beforeEach(async () => {

-- PostgreSQL Declarative Partitioning (10+)

-- Create partitioned table by date range
CREATE TABLE measurements (
  id BIGSERIAL,
  sensor_id INT NOT NULL,

-- Primary key (unique, not null identifier)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- SCHEMA DESIGN CHECKLIST

-- 1. Use appropriate data types
CREATE TABLE users_optimized (
  id SERIAL PRIMARY KEY,               -- Auto-increment
  uuid UUID DEFAULT gen_random_uuid(), -- UUID for external IDs

module ExistenceChecks
  extend ActiveSupport::Concern

  class_methods do
    def has_any?(conditions = {})
      relation = conditions.present? ? where(conditions) : all