postgres-queries

# PostgreSQL Queries Skill

Patterns for writing effective PostgreSQL queries.

## Basic Queries

### SELECT Patterns

```sql
-- Select with conditions
SELECT id, name, email
FROM users
WHERE status = 'active'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 10;

-- Select with joins
SELECT
  u.id,
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;
```

### INSERT Patterns

```sql
-- Single insert with returning
INSERT INTO users (name, email, status)
VALUES ('John Doe', 'john@example.com', 'active')
RETURNING id, created_at;

-- Multi-row insert
INSERT INTO users (name, email, status)
VALUES
  ('User 1', 'user1@example.com', 'active'),
  ('User 2', 'user2@example.com', 'pending'),
  ('User 3', 'user3@example.com', 'active')
RETURNING *;

-- Insert with conflict handling (upsert)
INSERT INTO users (email, name, status)
VALUES ('john@example.com', 'John Doe', 'active')
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW()
RETURNING *;
```

### UPDATE Patterns

```sql
-- Update with conditions
UPDATE users
SET
  status = 'inactive',
  updated_at = NOW()
WHERE last_login_at < NOW() - INTERVAL '1 year'
RETURNING id, email;

-- Update from another table
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE s.order_id = o.id
  AND s.shipped_at IS NOT NULL;

-- Conditional update
UPDATE products
SET
  price = CASE
    WHEN category = 'electronics' THEN price * 0.9
    WHEN category = 'clothing' THEN price * 0.8
    ELSE price
  END,
  updated_at = NOW()
WHERE on_sale = true;
```

### DELETE Patterns

```sql
-- Delete with returning
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;

-- Delete using subquery
DELETE FROM users
WHERE id IN (
  SELECT user_id
  FROM inactive_users
  WHERE days_inactive > 365
);
```

## Advanced Queri