databases

Databases Skill (PostgreSQL)

Portable core database skill focused on PostgreSQL. MongoDB documentation lives under .cursor/skills/optional/databases-mongodb/ and activates when AGENTS.md §2 Database includes MongoDB.

When to Use This Skill

Use when:

• Designing relational schemas and data models
• Writing SQL (joins, CTEs, window functions)
• Optimizing indexes and query performance
• Implementing database migrations
• Configuring backups, roles, and replication
• Analyzing slow queries with EXPLAIN
• Administering PostgreSQL deployments

When to Choose PostgreSQL

• Strong consistency: ACID transactions critical
• Complex relationships: many-to-many joins, referential integrity
• SQL ecosystem: reporting tools, BI systems
• Data integrity: strict schema validation, constraints
• Complex queries: window functions, CTEs, analytical workloads

Best for: Financial systems, e-commerce transactions, ERP, CRM, data warehousing, analytics

Quick Start

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql

# Connect
psql -U postgres -d mydb

# Basic operations
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users WHERE age >= 18;
UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE name = 'Alice';

Common Operations

Create/Insert

INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
INSERT INTO users (name, email) VALUES ('Alice', NULL), ('Charlie', NULL);

Read/Query

SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE email = '[email protected]' LIMIT 1;

Update

UPDATE users SET age = 25 WHERE name = 'Bob';
UPDATE users SET status = 'active' WHERE status = 'pending';

Delete

DELETE FROM users WHERE name = 'Bob';
DELETE FROM users WHERE status = 'deleted';

Indexing

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);