Skip to content

LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/postgresql

postgresql

Unverified

PostgreSQL database agent: deploy locally (Docker/K8s), manage schemas, run queries, analyze performance, configure permissions, and seed data.

PostgreSQL Skill

SQL-native PostgreSQL operations via psql. No Python dependencies required.


Version Compatibility

FeatureMinimum Version
Core functionalityPostgreSQL 12+
pg_blocking_pids()9.6+
REINDEX CONCURRENTLY12+
pg_stat_progress_* views12+
VACUUM PARALLEL13+
pg_stat_wal14+
pg_stat_checkpointer17+

Recommended: PostgreSQL 14+


Connection

Environment Variables

export PGHOST=localhost
export PGPORT=5432
export PGUSER=app
export PGPASSWORD=secret
export PGDATABASE=myapp

Connect via psql

# Using environment variables
psql

# Using connection string
psql "postgresql://app:secret@localhost:5432/myapp"

# Docker
docker exec -it postgres psql -U app -d myapp

# Kubernetes
kubectl exec -it postgres -- psql -U postgres

Seed Data

Basic Insert

INSERT INTO app.users (email, name) VALUES
    ('alice@example.com', 'Alice'),
    ('bob@example.com', 'Bob')
RETURNING id, email;

Upsert

INSERT INTO app.users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = NOW();

Generate Test Data

INSERT INTO app.users (email, name)
SELECT
    'user' || n || '@example.com',
    'User ' || n
FROM generate_series(1, 1000) AS n;

COPY from CSV

\copy app.users (email, name) FROM 'users.csv' WITH (FORMAT csv, HEADER true);

Query Execution

Parameterized Queries

CRITICAL: Always use parameterized queries to prevent SQL injection.

-- In psql with variables
\set user_id 123
SELECT * FROM app.users WHERE id = :user_id;

-- Prepared statements
PREPARE get_user(bigint) AS
    SELECT * FROM app.users WHERE id = $1;
EXECUTE get_user(123);

Transactions

BEGIN;
    UPDATE app.accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE app.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- With savepoint
BEGIN;
    INSERT INTO app.orders (user_id, total) VALUES (1, 99.99);
    SAVEPOINT before_items;
    INSERT INTO app.order_items (order_id, product_id) VALUES (1, 999);
    -- Oops, rollback just the items
    ROLLBACK TO before_items;
COMMIT;

Safety Guidelines

CRITICAL: Follow these rules to prevent data loss and security issues.

  1. Always use parameterized queries - Never concatenate user input into SQL strings
  2. Wrap modifications in transactions - Use BEGIN/COMMIT for multi-statement changes
  3. Always include WHERE clause - Never run UPDATE/DELETE without WHERE
  4. Test on non-production first - Validate queries on dev/staging before production
  5. Use CONCURRENTLY for indexes - Avoid locking tables during index operations
  6. Backup before migrations - Always have a restore point before schema changes
  7. Limit query results - Use LIMIT during exploration to avoid memory issues

psql Quick Reference

CommandDescription
\lList databases
\c dbnameConnect to database
\dtList tables
\dt schema.*List tables in schema
\d tablenameDescribe table
\diList indexes
\dfList functions
\duList roles/users
\dnList schemas
\xToggle expanded display
\timingToggle query timing
\i file.sqlExecute SQL file
\copyImport/export CSV
\qQuit

Resource Files

For detailed guidance, read these on-demand:


Input / Output

This skill defines no input parameters or structured output.

writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 318kUpdated Claude CodeCodexUnverifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall