PostgreSQL Fundamentals Skill Skill

PostgreSQL Fundamentals Skill

Atomic skill for SQL foundations and schema design

Overview

Production-ready patterns for PostgreSQL 16+ data modeling, including type selection, constraint design, and schema organization.

Prerequisites

PostgreSQL 16+ installed
Basic SQL knowledge
Database access with CREATE privileges

Parameters

parameters:
  operation:
    type: string
    required: true
    enum: [create_table, add_constraint, select_type, design_schema]
  table_name:
    type: string
    pattern: "^[a-z][a-z0-9_]*$"
  schema:
    type: string
    default: "public"

Quick Reference

Data Type Selection

| Use Case | Recommended | Avoid | |----------|-------------|-------| | Primary key | BIGINT GENERATED ALWAYS AS IDENTITY | SERIAL | | Monetary | NUMERIC(19,4) | FLOAT | | Timestamps | TIMESTAMPTZ | TIMESTAMP | | UUID | UUID | VARCHAR(36) | | JSON data | JSONB | JSON |

Table Template

CREATE TABLE schema_name.table_name (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Constraint Patterns

-- Foreign key
CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES other(id) ON DELETE CASCADE;
-- Check
CONSTRAINT chk_positive CHECK (amount > 0);
-- Unique
CONSTRAINT uq_email UNIQUE (email);

Validation Rules

| Rule | Pattern | |------|---------| | Table names | ^[a-z][a-z0-9_]{2,62}$ | | Column names | ^[a-z][a-z0-9_]{1,62}$ |

Test Template

DO $$ BEGIN
    DROP TABLE IF EXISTS test_users;
    CREATE TABLE test_users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
    ASSERT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'test_users');
    DROP TABLE test_users;
END $$;

Troubleshooting

| Error | Cause | Solution | |-------|-------|----------| | 42P07 | Table exists | Use IF NOT EXISTS | | 23505 | Duplicate key | Check constraints | | 42703 | Column not found | Verify names |

Usage

Skill("postgresql-fundamentals")

Agent Skills: PostgreSQL Fundamentals Skill

Skill Files