name	Validate with Database
description	Connect to live PostgreSQL database to validate schema assumptions, compare pg_dump vs pgschema output, and query system catalogs interactively

Validate with Database

Use this skill to connect to the test PostgreSQL database, validate assumptions about schema behavior, and cross-validate between pg_dump and pgschema implementations.

When to Use This Skill

Invoke this skill when:

Validating how PostgreSQL actually stores or represents schema objects
Comparing pg_dump output with pgschema output
Testing a new feature implementation against real database
Debugging schema introspection issues
Verifying system catalog query results
Understanding how PostgreSQL formats specific DDL
Checking version-specific behavior (PostgreSQL 14-17)
Validating migration plans before implementing new features

Database Connection Information

Connection details are stored in .env file at project root:

PGHOST=localhost
PGDATABASE=employee
PGUSER=postgres
PGPASSWORD=testpwd1

Default connection:

Host: localhost
Port: 5432 (default)
Database: employee
User: postgres
Password: testpwd1

Connection Methods

Method 1: Using psql (Interactive Queries)

Basic connection:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee

One-off query:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "SELECT version();"

Execute multi-line query:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    t.tgname,
    CASE
        WHEN t.tgqual IS NOT NULL
        THEN pg_get_expr(t.tgqual, t.tgrelid, false)
        ELSE 'NO WHEN CLAUSE'
    END as when_clause
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'test_table'
ORDER BY t.tgname;
"

Method 2: Using pg_dump (Schema Export)

Dump entire database schema:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public

Dump specific table:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --table=employees

Dump only specific object types:

# Only triggers
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public | grep -A 20 "CREATE TRIGGER"

# Only indexes
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public | grep -A 10 "CREATE INDEX"

Method 3: Using pgschema (Project Tool)

Dump with pgschema:

./pgschema dump --host localhost --port 5432 --db employee --user postgres --schema public

Or using environment variables (from .env):

# .env is automatically loaded by pgschema
./pgschema dump --schema public

Dump to file:

./pgschema dump --schema public -o /tmp/schema_dump.sql

Method 4: Database Setup for Testing

Create a test database:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "DROP DATABASE IF EXISTS test_validation;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE test_validation;"

Create test schema objects:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d test_validation -c "
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER test_trigger
    BEFORE INSERT ON test_table
    FOR EACH ROW
    WHEN (NEW.name IS NOT NULL)
    EXECUTE FUNCTION my_trigger_func();
"

Common Validation Workflows

Workflow 1: Compare pg_dump vs pgschema Output

Purpose: Verify pgschema produces comparable output to pg_dump

Steps:

Dump with pg_dump:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public > /tmp/pg_dump_output.sql

Dump with pgschema:

./pgschema dump --schema public -o /tmp/pgschema_output.sql

Compare outputs:

# Side-by-side comparison
diff -u /tmp/pg_dump_output.sql /tmp/pgschema_output.sql

# Or use a better diff tool
code --diff /tmp/pg_dump_output.sql /tmp/pgschema_output.sql

Analyze differences:

Formatting differences (expected)
Missing objects (bugs to fix)
Different DDL structure (may need investigation)
Comments handling
Ordering differences

Workflow 2: Validate System Catalog Queries

Purpose: Test system catalog queries return expected data

Steps:

Create test object:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
CREATE TABLE test_triggers (
    id INTEGER PRIMARY KEY,
    data TEXT
);

CREATE OR REPLACE FUNCTION trigger_func() RETURNS TRIGGER AS \$\$
BEGIN
    RETURN NEW;
END;
\$\$ LANGUAGE plpgsql;

CREATE TRIGGER test_when_trigger
    BEFORE INSERT ON test_triggers
    FOR EACH ROW
    WHEN (NEW.data <> '')
    EXECUTE FUNCTION trigger_func();
"

Query system catalogs:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    t.tgname,
    t.tgtype,
    CASE
        WHEN t.tgqual IS NOT NULL
        THEN pg_get_expr(t.tgqual, t.tgrelid, false)
        ELSE NULL
    END as when_clause,
    pg_get_triggerdef(t.oid) as full_definition
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'test_triggers'
  AND t.tgisinternal = false;
"

Verify pgschema extracts same data:

./pgschema dump --schema public | grep -A 20 "test_when_trigger"

Cleanup:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
DROP TRIGGER IF EXISTS test_when_trigger ON test_triggers;
DROP TABLE IF EXISTS test_triggers;
DROP FUNCTION IF EXISTS trigger_func();
"

Workflow 3: Test Plan/Apply Workflow

Purpose: Validate pgschema plan and apply work correctly

Steps:

Create initial schema:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
DROP SCHEMA IF EXISTS test_workflow CASCADE;
CREATE SCHEMA test_workflow;
SET search_path TO test_workflow;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
"

Dump current state:

./pgschema dump --schema test_workflow -o /tmp/current_schema.sql

Modify schema file (edit /tmp/current_schema.sql):

-- Add a new column
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- NEW
);

Generate plan:

./pgschema plan --schema test_workflow --file /tmp/current_schema.sql

Review migration DDL - should show:

ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Apply migration:

./pgschema apply --schema test_workflow --file /tmp/current_schema.sql --auto-approve

Verify result:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "\d test_workflow.users"

Cleanup:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "DROP SCHEMA IF EXISTS test_workflow CASCADE;"

Workflow 4: Validate Specific DDL Formatting

Purpose: Understand how PostgreSQL formats specific constructs

Steps:

Create object with specific feature:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
CREATE TABLE test_pk_order (
    b INTEGER,
    a INTEGER,
    c INTEGER,
    PRIMARY KEY (a, b)  -- Note: different order than column definition
);
"

Check how PostgreSQL stores it:

# Use \d+ to see structure
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "\d+ test_pk_order"

See pg_dump format:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d postgres --schema-only --table=test_pk_order

Query system catalogs directly:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    c.relname as table_name,
    con.conname as constraint_name,
    pg_get_constraintdef(con.oid) as constraint_def
FROM pg_constraint con
JOIN pg_class c ON con.conrelid = c.oid
WHERE c.relname = 'test_pk_order';
"

Compare with pgschema:

./pgschema dump --schema public | grep -A 10 "test_pk_order"

Workflow 5: Cross-Version Testing

Purpose: Validate behavior across PostgreSQL versions 14-17

Steps:

Check current version:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT version();"

Run version-specific integration tests:

# Test against specific version
PGSCHEMA_POSTGRES_VERSION=14 go test -v ./cmd/dump -run TestDumpCommand_Employee
PGSCHEMA_POSTGRES_VERSION=17 go test -v ./cmd/dump -run TestDumpCommand_Employee

Check for version-specific features:

# PostgreSQL 15+ feature: UNIQUE NULLS NOT DISTINCT
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT version();
CREATE TABLE test_nulls (
    id INTEGER,
    email TEXT UNIQUE NULLS NOT DISTINCT
);
"

Useful System Catalog Queries

Inspect Tables and Columns

-- All tables in schema
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public';

-- Columns with types
SELECT
    a.attname as column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
    a.attnotnull as not_null,
    pg_get_expr(ad.adbin, ad.adrelid) as default_value,
    a.attgenerated as generated
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
WHERE a.attrelid = 'public.employees'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Inspect Constraints

-- All constraints on a table
SELECT
    con.conname as constraint_name,
    con.contype as constraint_type,
    pg_get_constraintdef(con.oid) as definition
FROM pg_constraint con
WHERE con.conrelid = 'public.employees'::regclass;

Inspect Indexes

-- All indexes on a table
SELECT
    i.relname as index_name,
    am.amname as index_type,
    pg_get_indexdef(idx.indexrelid) as definition,
    CASE
        WHEN idx.indpred IS NOT NULL
        THEN pg_get_expr(idx.indpred, idx.indrelid, true)
        ELSE NULL
    END as where_clause
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_class t ON t.oid = idx.indrelid
JOIN pg_am am ON i.relam = am.oid
WHERE t.relname = 'employees'
  AND t.relnamespace = 'public'::regnamespace;

Inspect Triggers

-- All triggers on a table
SELECT
    t.tgname as trigger_name,
    t.tgenabled as enabled,
    CASE t.tgtype::integer & 66
        WHEN 2 THEN 'BEFORE'
        WHEN 64 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END as timing,
    pg_get_triggerdef(t.oid) as full_definition,
    CASE
        WHEN t.tgqual IS NOT NULL
        THEN pg_get_expr(t.tgqual, t.tgrelid, false)
        ELSE NULL
    END as when_condition
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'employees'
  AND c.relnamespace = 'public'::regnamespace
  AND NOT t.tgisinternal;

Inspect Views and Materialized Views

-- Views
SELECT
    schemaname,
    viewname,
    definition
FROM pg_views
WHERE schemaname = 'public';

-- Materialized views
SELECT
    schemaname,
    matviewname,
    definition
FROM pg_matviews
WHERE schemaname = 'public';

Inspect Comments

-- Comments on tables
SELECT
    c.relname as table_name,
    d.description as comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND d.description IS NOT NULL;

-- Comments on columns
SELECT
    c.relname as table_name,
    a.attname as column_name,
    d.description as comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND d.description IS NOT NULL;

Troubleshooting Common Issues

Issue: Connection Refused

# Check if PostgreSQL is running
pg_isready -h localhost -p 5432

# Check if port is open
nc -zv localhost 5432

# Check PostgreSQL logs
tail -f /usr/local/var/postgresql@14/server.log  # macOS Homebrew

Issue: Authentication Failed

# Verify credentials
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "SELECT current_user;"

# Check pg_hba.conf settings
cat /usr/local/var/postgresql@14/pg_hba.conf

Issue: Database Doesn't Exist

# List all databases
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "\l"

# Create if missing
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE employee;"

Issue: Schema Not Found

# List all schemas
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "\dn"

# Create if missing
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "CREATE SCHEMA public;"

Test Data Setup

Load Sample Database

The project includes test data in testdata/dump/:

# Employee database
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "DROP DATABASE IF EXISTS employee;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE employee;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee < testdata/dump/employee/employee.sql

# Sakila database (if available)
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "DROP DATABASE IF EXISTS sakila;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE sakila;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d sakila < testdata/dump/sakila/sakila.sql

Create Minimal Test Database

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres <<EOF
DROP DATABASE IF EXISTS test_minimal;
CREATE DATABASE test_minimal;
\c test_minimal

CREATE SCHEMA app;

CREATE TABLE app.users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE app.posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT,
    published_at TIMESTAMP
);

CREATE INDEX idx_posts_user ON app.posts(user_id);
CREATE INDEX idx_posts_published ON app.posts(published_at) WHERE published_at IS NOT NULL;

COMMENT ON TABLE app.users IS 'Application users';
COMMENT ON COLUMN app.users.email IS 'User email address (must be unique)';
EOF

Quick Reference Commands

Connect to database:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee

Run query:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "SELECT * FROM employees LIMIT 5;"

Describe table:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "\d+ employees"

pg_dump schema only:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public

pgschema dump:

./pgschema dump --schema public

pgschema plan:

./pgschema plan --schema public --file schema.sql

Drop and recreate test database:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "DROP DATABASE IF EXISTS test_db; CREATE DATABASE test_db;"

Validation Checklist

When validating implementation:

Test database is running and accessible
Connection credentials from .env work
pg_dump produces expected output
pgschema produces comparable output
System catalog queries return expected data
DDL formatting matches PostgreSQL conventions
Plan generates correct migration DDL
Apply successfully executes migration
Final state matches expected schema
Tested across PostgreSQL versions (if version-specific)
Test database cleaned up after validation

Validate with Database

Install Skill

SKILL.md

Validate with Database

When to Use This Skill

Database Connection Information

Connection Methods

Method 1: Using psql (Interactive Queries)

Method 2: Using pg_dump (Schema Export)

Method 3: Using pgschema (Project Tool)

Method 4: Database Setup for Testing

Common Validation Workflows

Workflow 1: Compare pg_dump vs pgschema Output

Workflow 2: Validate System Catalog Queries

Workflow 3: Test Plan/Apply Workflow

Workflow 4: Validate Specific DDL Formatting

Workflow 5: Cross-Version Testing

Useful System Catalog Queries

Inspect Tables and Columns

Inspect Constraints

Inspect Indexes

Inspect Triggers

Inspect Views and Materialized Views

Inspect Comments

Troubleshooting Common Issues

Issue: Connection Refused

Issue: Authentication Failed

Issue: Database Doesn't Exist

Issue: Schema Not Found

Test Data Setup

Load Sample Database

Create Minimal Test Database

Quick Reference Commands

Validation Checklist