数据库学习教程

    基础与 psql

    安装与启动

    # macOS（Homebrew）
brew install postgresql@16 && brew services start postgresql@16

# Windows（winget）
winget install PostgreSQL.PostgreSQL.16

# Docker（推荐）
docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=demo \
  -p 5432:5432 postgres:16
    psql -h 127.0.0.1 -p 5432 -U postgres -d demo

# 会话内常用元命令
\l          # 列出数据库
\dt         # 列出当前库的表
\d orders   # 查看表结构
\du         # 查看角色
\q          # 退出
    Tip

    psql 支持 \x 切换扩展显示（一行多列的数据更易读）、\timing 显示每条语句耗时、\e 用编辑器写多行 SQL。熟练这些元命令能显著提升效率。

    Schema：命名空间

    PG 与 MySQL 的「database」概念不同：一个数据库内可有多个 Schema，用于组织对象与做权限隔离。

    CREATE SCHEMA app;

CREATE TABLE app.users (
    id         BIGSERIAL PRIMARY KEY,           -- 自增（等价 GENERATED ... AS IDENTITY）
    email      TEXT        NOT NULL UNIQUE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 设置搜索路径，之后可省略 schema 前缀
SET search_path TO app, public;
    Warning

    PG 是 database → schema → table 三层结构，而 MySQL 是 database → table 两层。别把 PG 的「database」当成 MySQL 的「database」，跨库查询在 PG 中需用 dblink/postgres_fdw 或直接切换连接。

    数据类型

    类别类型说明
    数值SMALLINT/INTEGER/BIGINT整数
    数值NUMERIC(p,s)精确小数（同 DECIMAL）
    数值REAL/DOUBLE PRECISION浮点，慎用于金额
    文本TEXT变长，无需指定长度
    文本VARCHAR(n)/CHAR(n)需限长时使用
    布尔BOOLEAN原生真布尔
    时间TIMESTAMPTZ带时区，推荐使用
    时间TIMESTAMP不带时区
    时间DATE/TIME/INTERVAL日期/时间/间隔
    二进制BYTEA字节串
    半结构JSON/JSONB见进阶篇
    特殊UUID全局唯一 ID
    特殊ARRAY数组
    特殊ENUM枚举类型
    范围INT4RANGE/TSTZRANGE区间
    CREATE TABLE app.events (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uid        UUID        NOT NULL DEFAULT gen_random_uuid(),
    tags       TEXT[]      NOT NULL DEFAULT '{}',
    payload    JSONB       NOT NULL DEFAULT '{}',
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
    Tip

    时间一律用 TIMESTAMPTZ 并存 UTC。它把时刻规范化为 UTC 存储，读取时按会话时区呈现，能避免大部分时区错乱问题。gen_random_uuid() 在 PG 13+ 内置（pgcrypto 也可提供）。

    用 Go 连接 PostgreSQL

    推荐使用 pgx（性能与类型支持最好）。可直接用 pgx 原生接口，也可用其 stdlib 适配 database/sql。

    go get github.com/jackc/pgx/v5
    package main

import (
    "context"
    "log"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

type Event struct {
    ID         int64
    Tags       []string
    OccurredAt time.Time
}

func main() {
    dsn := "postgres://app:secret@127.0.0.1:5432/demo?sslmode=disable"
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    pool, err := pgxpool.New(ctx, dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    if err := pool.Ping(ctx); err != nil {
        log.Fatal("连接失败:", err)
    }

    // PostgreSQL 占位符为 $1、$2
    var e Event
    err = pool.QueryRow(ctx,
        "SELECT id, tags, occurred_at FROM app.events WHERE id = $1", 1,
    ).Scan(&e.ID, &e.Tags, &e.OccurredAt)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("%+v", e)
}
    Warning

    PG 的占位符是 $1、$2（不是 ?）。使用 database/sql + pgx/stdlib 时同样是 $n；GORM 的 PG 驱动会自动转换 ?，但手写 SQL 时务必注意。

    小结

    • PG 是 database → schema → table 三层结构，search_path 控制默认解析顺序。
    • 类型系统丰富：数组、JSONB、UUID、范围类型一应俱全；时间优先 TIMESTAMPTZ。
    • Go 首选 pgx（pgxpool 连接池）；占位符为 $n。
    • 密码等敏感信息通过环境变量注入。