PostgreSQL Skill

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

---

Version Compatibility

Feature	Minimum Version
Core functionality	PostgreSQL 12+
pg_blocking_pids()	9.6+
REINDEX CONCURRENTLY	12+
pg_stat_progress_* views	12+
VACUUM PARALLEL	13+
pg_stat_wal	14+
pg_stat_checkpointer	17+

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

Command	Description
\l	List databases
\c dbname	Connect to database
\dt	List tables
\dt schema.*	List tables in schema
\d tablename	Describe table
\di	List indexes
\df	List functions
\du	List roles/users
\dn	List schemas
\x	Toggle expanded display
\timing	Toggle query timing
\i file.sql	Execute SQL file
\copy	Import/export CSV
\q	Quit

---

Resource Files

For detailed guidance, read these on-demand:

• deployment.md — Docker, Docker Compose, Kubernetes setups
• schema-management.md — Tables, indexes, migrations
• monitoring.md — System views, performance analysis
• administration.md — Permissions, backup, import/export

---

Input / Output

This skill defines no input parameters or structured output.