Master PostgreSQL SQL fundamentals - data types, tables, constraints, schema design
Provides PostgreSQL 16+ schema design patterns, data type recommendations, and constraint templates. Use when creating tables, adding constraints, or selecting optimal data types for columns.
/plugin marketplace add pluginagentmarketplace/custom-plugin-postgresql/plugin install ultrathink@pluginagentmarketplace-postgresqlThis skill inherits all available tools. When active, it can use any tool Claude has access to.
assets/config.yamlassets/schema.jsonreferences/GUIDE.mdreferences/PATTERNS.mdscripts/validate.pyAtomic skill for SQL foundations and schema design
Production-ready patterns for PostgreSQL 16+ data modeling, including type selection, constraint design, and schema organization.
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"
| 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 |
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()
);
-- 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);
| Rule | Pattern |
|---|---|
| Table names | ^[a-z][a-z0-9_]{2,62}$ |
| Column names | ^[a-z][a-z0-9_]{1,62}$ |
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 $$;
| Error | Cause | Solution |
|---|---|---|
42P07 | Table exists | Use IF NOT EXISTS |
23505 | Duplicate key | Check constraints |
42703 | Column not found | Verify names |
Skill("postgresql-fundamentals")
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.