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