postgres

PostgreSQL

This skill teaches you how to safely execute SQL queries against a PostgreSQL database using Matimo tools, with emphasis on parameterized queries, schema awareness, and safe patterns.

Tools You Will Use

Tool	Purpose
postgres-execute-sql	Execute any SQL statement against a connected Postgres database

---

Executing a Query

Use postgres-execute-sql to run SQL statements. The tool supports parameterized queries for safety.

Parameters

Parameter	Type	Description
sql	string	SQL statement to execute. Use $1, $2, etc. for parameterized values
params	array	Optional array of parameter values (matched by position to $1, $2, etc.)
schema	string	Optional schema name for table qualification

Critical: Always Use Parameterized Queries

NEVER interpolate user-provided values directly into SQL strings. Always use $1, $2 placeholders.

// ✅ SAFE — parameterized
{
  "sql": "SELECT * FROM users WHERE email = $1",
  "params": ["[email protected]"]
}

// ❌ DANGEROUS — SQL injection risk
{
  "sql": "SELECT * FROM users WHERE email = '[email protected]'"
}

This prevents SQL injection attacks regardless of the input content.

---

Read Operations

Select with Parameters

{
  "sql": "SELECT id, name, email FROM users WHERE id = $1",
  "params": [42]
}

Select with Multiple Conditions

{
  "sql": "SELECT * FROM orders WHERE status = $1 AND created_at > $2",
  "params": ["pending", "2025-01-01"]
}

Count Records

{
  "sql": "SELECT count(*) as total FROM users WHERE active = $1",
  "params": [true]
}

Pagination Pattern

{
  "sql": "SELECT id, name, email FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2",
  "params": [20, 0]
}

Increment the offset by the limit for each page: 0, 20, 40, 60...

Join Queries

{
  "sql": "SELECT u.name, o.total, o.status FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = $1 ORDER BY o.created_at DESC LIMIT $2",
  "params": ["completed", 10]
}