postgres-authoring-cli

name

postgres-authoring-cli

description

Execute authoring SQL (DDL, DML, schema design, stored procedures, functions, triggers, indexing, partitioning) against Azure Database for PostgreSQL Flexible Server from agentic CLI environments. Use when the user wants to: (1) create/alter/drop tables, (2) insert/update/delete data, (3) create stored procedures or functions in PL/pgSQL, (4) design indexes and partitions, (5) create triggers, (6) manage constraints, (7) bulk load data with COPY. Triggers: "create table in postgres", "insert data", "create function", "create index", "alter table", "upsert postgres", "create trigger", "partition table", "bulk load postgres", "stored procedure postgres", "COPY INTO postgres".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Authoring — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Entra ID or native auth
Server Management	COMMON-CLI.md § Server Management	az postgres flexible-server
SQL via psql	COMMON-CLI.md § SQL via psql	Connect, query, execute files
Extensions	COMMON-CLI.md § Extensions Management	Enable and install extensions
Schema Design	POSTGRES-AUTHORING-CORE.md § Schema Design	Data types, DDL patterns
DML Operations	POSTGRES-AUTHORING-CORE.md § DML Operations	INSERT, UPDATE, DELETE, upsert
Stored Procedures & Functions	POSTGRES-AUTHORING-CORE.md § Stored Procedures and Functions	PL/pgSQL
Indexing Strategies	POSTGRES-AUTHORING-CORE.md § Indexing Strategies	B-tree, GIN, GiST, BRIN
Partitioning	POSTGRES-AUTHORING-CORE.md § Partitioning	Range, list, hash
Gotchas	COMMON-CORE.md § Gotchas	Common issues and fixes

---

Tool Stack

Tool	Role	Install
psql	Primary: Execute DDL/DML SQL. Standard PostgreSQL CLI client.	apt-get install postgresql-client / brew install libpq / winget install PostgreSQL.psql
az CLI	Auth (az login), server management, extension configuration.	Pre-installed in most dev environments
jq	Parse JSON from az commands	Pre-installed or trivial

Agent check — verify before first operation:

psql --version 2>/dev/null || echo "INSTALL: apt-get install postgresql-client OR brew install libpq"

---

Connection

Discover Server FQDN

RG="your-resource-group"
SERVER="your-server-name"

# Get FQDN
FQDN=$(az postgres flexible-server show \
  --resource-group $RG --name $SERVER \
  --query "fullyQualifiedDomainName" -o tsv)

echo "FQDN: $FQDN"

Connect

psql "host=$FQDN port=5432 dbname=your-database user=your-admin sslmode=require"

---

Agentic Workflows

Start here — always discover the schema before any write operation.

Step 1: Discover Existing Schema

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;"

Step 2: Inspect Target Table

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'your_table' ORDER BY ordinal_position;"

Step 3: Execute Authoring Operation

Generate and execute the DDL/DML based on discovered schema.

---

Must

• Discover schema before writing DDL/DML
• Use GENERATED ALWAYS AS IDENTITY instead of SERIAL
• Use timestamptz instead of timestamp
• Use JSONB instead of JSON
• Use IF NOT EXISTS / IF EXISTS for idempotent DDL
• Use CREATE OR REPLACE for functions and procedures
• Include appropriate indexes for foreign keys
• Use sslmode=require in all connections
• Use parameterized queries where possible

Prefer

• Partial indexes for selective queries
• Declarative partitioning for large tables
• INSERT ... ON CONFLICT for upsert patterns
• CTEs over subqueries for readability
• \COPY (client-side) over COPY (server-side) for data loading
• Transaction blocks for multi-statement operations
• CONCURRENTLY option for index creation on production tables

Avoid

• SERIAL (use identity columns)
• timestamp without timezone
• JSON type (use JSONB)
• char(n) (use text or varchar)
• Creating indexes without CONCURRENTLY on large production tables
• VACUUM FULL during peak hours
• Dropping columns (prefer adding new ones and deprecating)
• Hardcoded connection strings or passwords