Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
インストール方法を見るインストールする前に、エージェントに与えられる指示の中身を確認できます。
The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload
patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,
and worked DDL examples are in references/details.md; open it when a section below points there.
BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.NUMERIC for exact decimal arithmetic).snake_case.UNIQUE NULLS NOT DISTINCT (...) (PG15+) to restrict to one NULL.NUMERIC(2,0) fails, unlike databases that silently truncate or round.CLUSTER is a one-off reorganization, not maintained on later inserts.BIGINT GENERATED ALWAYS AS IDENTITY; UUID for distributed or opaque IDs, generated with uuidv7() (PG18+) or gen_random_uuid().BIGINT unless storage is critical; DOUBLE PRECISION over REAL; NUMERIC(p,s) for money and exact decimals.TEXT, with CHECK (LENGTH(col) <= n) when a limit is needed; BYTEA for binary. Case-insensitive lookups: expression index on LOWER(col), or CITEXT when a constraint must be case-insensitive.TIMESTAMPTZ, DATE, INTERVAL. now() is transaction start; clock_timestamp() is wall clock.BOOLEAN NOT NULL unless tri-state is required.CREATE TYPE ... AS ENUM only for small, stable sets; evolving business values get TEXT + CHECK or a lookup table.references/details.md.| Avoid | Use instead |
|---|---|
timestamp (without time zone) | timestamptz |
char(n), varchar(n) | text (+ CHECK on length if needed) |
money | numeric |
timetz | timestamptz |
timestamptz(0) or any precision | timestamptz |
serial | generated always as identity |
ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Index the referencing column. Use DEFERRABLE INITIALLY DEFERRED for circular dependencies checked at commit.NULLS NOT DISTINCT (PG15+). Prefer NULLS NOT DISTINCT unless duplicate NULLs are wanted.NOT NULL: price NUMERIC NOT NULL CHECK (price > 0).EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) stops double-booking. Needs a GiST-capable type.=, <, >, BETWEEN, ORDER BY).WHERE a = ? AND b > ? uses (a,b); WHERE b = ? does not). Most selective columns first.CREATE INDEX ON tbl (id) INCLUDE (name, email) for index-only scans.CREATE INDEX ON tbl (user_id) WHERE status = 'active'.CREATE INDEX ON tbl (LOWER(email)); the query must use the same expression.PARTITION BY RANGE (created_at); TimescaleDB automates it with retention and compression), LIST for discrete values, HASH for even distribution without a natural key.CHECK constraints; declarative partitioning (PG10+) creates them for you.CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}',
theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
references/details.md holds the material this file only names:
TEMPORARY, UNLOGGED) and row-level security.pg_trgm, citext, timescaledb, postgis, pgvector, and more).jsonb_path_ops and extracted B-tree columns.まだレビューはありません。使ってみた感想をお寄せください。
概要と使いどころ
Implement WCAG 2.2 compliant interfaces with mobile accessibility, inclusive design patterns, and assistive technology support. Use when auditing accessibility, implementing ARIA patterns, building for screen readers, or ensuring inclusive user experiences.
日本語の概要は準備中です。原文の説明を表示しています。
Use after generating code, after accepting AI suggestions, or when reviewing AI-written modules. Also use when code works but feels brittle, when error handling seems thin, when orphaned resources or missing cleanup are suspected, or when the agent claims done but hidden debt may exist. Catches the specific failure patterns AI agents produce that humans would not.
日本語の概要は準備中です。原文の説明を表示しています。
Build production Apache Airflow DAGs with best practices for operators, sensors, testing, and deployment. Use when creating data pipelines, orchestrating workflows, or scheduling batch jobs.
日本語の概要は準備中です。原文の説明を表示しています。
Migrate from AngularJS to Angular using hybrid mode, incremental component rewriting, and dependency injection updates. Use when upgrading AngularJS applications, planning framework migrations, or modernizing legacy Angular code.
日本語の概要は準備中です。原文の説明を表示しています。
Understand anti-reversing, obfuscation, and protection techniques encountered during software analysis. Use this skill when analyzing malware evasion techniques, when implementing anti-debugging protections for CTF challenges, when reverse engineering packed binaries, or when building security research tools that need to detect virtualized environments.
日本語の概要は準備中です。原文の説明を表示しています。
Master REST and GraphQL API design principles to build intuitive, scalable, and maintainable APIs that delight developers. Use when designing new APIs, reviewing API specifications, or establishing API design standards.
日本語の概要は準備中です。原文の説明を表示しています。