postgresql-fundamentals

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Fundamentals Skill

PostgreSQL 基础技能

Atomic skill for SQL foundations and schema design

用于SQL基础和架构设计的原子技能

Overview

概述

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

适用于PostgreSQL 16+的生产就绪型数据建模模式，包括类型选择、约束设计和架构组织。

Prerequisites

前提条件

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

已安装PostgreSQL 16+
具备基础SQL知识
拥有带CREATE权限的数据库访问权限

Parameters

参数

yaml

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"

yaml

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`

使用场景	推荐类型	避免使用
主键	`BIGINT GENERATED ALWAYS AS IDENTITY`	`SERIAL`
货币	`NUMERIC(19,4)`	`FLOAT`
时间戳	`TIMESTAMPTZ`	`TIMESTAMP`
UUID	`UUID`	`VARCHAR(36)`
JSON数据	`JSONB`	`JSON`

Table Template

表模板

sql

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()
);

sql

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

约束模式

sql

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

sql

-- 外键
CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES other(id) ON DELETE CASCADE;
-- 检查约束
CONSTRAINT chk_positive CHECK (amount > 0);
-- 唯一约束
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}$`

规则	模式
表名称	`^[a-z][a-z0-9_]{2,62}$`
列名称	`^[a-z][a-z0-9_]{1,62}$`

Test Template

测试模板

sql

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 $$;

sql

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

错误	原因	解决方案
`42P07`	表已存在	使用IF NOT EXISTS
`23505`	键重复	检查约束条件
`42703`	列未找到	验证名称正确性

Usage

使用方法

Skill("postgresql-fundamentals")

Skill("postgresql-fundamentals")