postgresql-fundamentals

pluginagentmarketplace's avatarfrom pluginagentmarketplace

Master PostgreSQL SQL fundamentals - data types, tables, constraints, schema design

⭐1stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides comprehensive guidance on PostgreSQL fundamentals, focusing on production-ready schema design, optimal data type selection, and robust constraint implementation. It helps developers build scalable and reliable database structures following PostgreSQL 16+ best practices, ensuring data integrity and performance from the start.

Use Cases

  • Database Schema Design: Architecting new database structures using modern patterns like identity columns and standardized audit fields.
  • Data Integrity Enforcement: Implementing foreign keys, unique constraints, and check constraints to maintain high-quality data across related tables.
  • Optimal Data Type Selection: Choosing the most efficient PostgreSQL types for specific use cases, such as using JSONB for flexible data or TIMESTAMPTZ for time-sensitive applications.
  • SQL Boilerplate Generation: Quickly generating validated, error-free SQL code for table creation and schema modifications to accelerate backend development.
namepostgresql-fundamentals
descriptionMaster PostgreSQL SQL fundamentals - data types, tables, constraints, schema design
version"3.0.0"
sasmp_version"1.3.0"
bonded_agent01-postgresql-fundamentals
bond_typePRIMARY_BOND
categorydatabase
difficultybeginner
estimated_time2h

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")