LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/postgresql
postgresql
UnverifiedPostgreSQL 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
| 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.
- Always use parameterized queries - Never concatenate user input into SQL strings
- Wrap modifications in transactions - Use BEGIN/COMMIT for multi-statement changes
- Always include WHERE clause - Never run UPDATE/DELETE without WHERE
- Test on non-production first - Validate queries on dev/staging before production
- Use CONCURRENTLY for indexes - Avoid locking tables during index operations
- Backup before migrations - Always have a restore point before schema changes
- 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.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 318k | Updated | Claude CodeCodex | Unverified | Install |
| 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: 196k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 117k | Updated | Claude CodeCodex | Unverified | Install |