# 类型是一份契约

> 选择多年后仍然正确、紧凑的列类型和排序规则，并了解哪些流行选择会悄悄导致 bug、膨胀或数据损坏。

---

LLMS 索引： [llms.txt](/llms.txt)

---

<span id="basics-chapter-12"></span>

这个错误已经让不止一位值班工程师从睡梦中惊醒：

```sql
select 2147483647::int + 1;
```

```text
ERROR:  integer out of range
```

这是 4 字节 `integer` 能容纳的最大值。当 `integer` ID 的序列达到它时，每次插入都会失败。不是部分失败：每一次都失败。修复它要重写这张表、它的索引，以及所有外键指向它的表。背后的决定只花了两秒，却是在多年以前、一次没人记得的迁移中做出的。

类型是你做出的最便宜的决定，却是最昂贵的修改对象。类型是一份契约：每行要花多少字节、哪些值根本不能存在，以及比较和算术如何运作。对于文本，排序规则也是这份契约的一部分，而且它是其中唯一可能在你毫无迁移的情况下自行改变的部分。

## 每个字节都要按行付费 {#every-byte-is-paid-per-row}

查询的成本大致取决于它触碰的页数，而一页容纳 8 KB。把一行从 125 字节缩小到 100 字节，每次扫描触碰的页数就大约少 20%。`pg_column_size()` 会告诉你任意值占多少空间，而对齐可能会把一个窄列填充到 8 字节（[列顺序就是空闲空间](/04-storage/#column-order-is-free-space)）。

保持比例：先保证正确，再保留改变主意的自由，最后才考虑字节数。

## 整数比你想象中更快耗尽 {#integers-run-out-sooner-than-you-think}

| 类型 | 字节数 | 范围 |
|---|---:|---|
| `smallint` | 2 | 约正负 32 千 |
| `integer` | 4 | 约正负 2.1 十亿 |
| `bigint` | 8 | 约正负 9.2 百京 |

<span id="tbl-12-1"></span>

**Table 12-1.** 整数比你想象中更快耗尽


### ID 使用 bigint {#ids-are-bigint}

每个主键以及引用主键的每个列都使用 `bigint`。回滚的插入、`on conflict` 尝试和丢弃的批量加载都会消耗序列值；相比之下，省下的 4 字节还不如 23 字节的[元组头](/43-glossary/#tuple-header)重要。以后把 `integer` 改成 `bigint`，要重写每个引用表；在大型系统上，这是一个持续数周的项目（[integer 改 bigint 的问题](/26-migrations/#basics-26-the-int-to-bigint-problem)）。

运行示例中的 `events` 表有 2,000,000 行，`integer` 还剩 2,145,483,647 个可用值。这听起来很安全，直到你算一算：一个每秒写入 1,000 行、还要把重试算进去的流水线，大约 25 天就会用完它。

拿不准时，选择更大的类型。没有人会因为列太宽而被叫醒。`bigint` 的唯一例外，是必须由数据库之外的系统预先生成的键，也就是 `uuid`（[数据库不总能生成 ID](/13-keys/#the-database-cant-always-mint-the-id)）。

## 金额用 numeric，绝不用浮点数 {#money-is-numeric-never-float}

浮点类型（`real`、`double precision`）存储的是二进制近似值，而大多数十进制小数都没有精确的二进制表示。把一百万个一角硬币加起来：

```sql
select sum(0.1::float8)  from generate_series(1, 1000000);
select sum(0.1::numeric) from generate_series(1, 1000000);
```

```text
 100000.00000133288
 100000.0
```

> [!EXAMPLE]- 动手验证 · lab `11-types`
>
> ```sql
> select lab.prove('a million float8 0.1s sum to 100000.00000133288',
>   (select sum(0.1::float8)::text from generate_series(1, 1000000)) = '100000.00000133288');
> select lab.prove('a million numeric 0.1s sum to exactly 100000.0',
>   (select sum(0.1::numeric)::text from generate_series(1, 1000000)) = '100000.0');
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

那个多出来的 `0.00000133288`，就是发票对账出现一分钱差异的起点。使用 `numeric(p, s)`，让其中的小数位数对应货币的最小单位，或者使用以分为单位的 `bigint`；无论选哪一种，都要有一个货币列。浮点数适合传感器读数、评分和坐标，因为这些场景可以接受很小的相对误差。

<span id="where-numeric-rounds-and-where-it-doesnt" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-where-numeric-rounds-and-where-it-doesnt" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：numeric 在哪里舍入，在哪里不舍入
>
> `numeric(12, 2)` 会在输入时舍入，但声明的小数位数只在存储或转换值时生效，不适用于算术运算：
>
> ```sql
> select 1234.567::numeric(12, 2), 2.5::numeric(4, 2) * 1.15;
> ```
>
> ```text
>  numeric | ?column?
> ---------+----------
>  1234.57 |   2.8750
> ```
>
> 何时舍入、如何舍入由你决定，这对金额来说是正确的：银行家舍入、按行还是按发票舍入，都属于业务规则。`numeric` 是变长类型（`pg_column_size` 对 `1` 返回 8 字节，对 `123456789.12` 返回 14 字节），速度比整数运算慢；只有在汇总数亿行时，这才重要。到了那种规模，以分为单位的整数更合适。

## 使用 text，把长度写进检查约束 {#use-text-and-put-the-length-in-a-check}

`text` 和 `varchar(n)` 的存储方式相同：`'hello'` 无论哪一种都占 9 字节。`char(n)` 会填充空格，`char(50)` 会占 54 字节。`varchar(n)` 就是附加了长度检查的 `text`，而问题就在这个附加检查上。长度规则会改变，写进类型的规则看起来像存储决策，实际上却是验证规则。把它写成约束：

```sql
email text not null check (length(email) <= 320)
```

检查约束有名字，可以在不触碰列类型的情况下替换，可以先以 `not valid` 添加、之后再验证而不持有长时间锁（[添加 CHECK NOT VALID，再验证它](/26-migrations/#add-a-check-not-valid-then-validate-it)），还可以表达长度无法表达的事情，比如“不能为空”。`varchar(255)` 是带有 MySQL 理由的 MySQL 习惯（索引前缀限制），而这里不存在这种限制。

<span id="charn-pads-then-forgets-the-padding" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-charn-pads-then-forgets-the-padding" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：char(n) 会填充，然后忘掉填充
>
> ```sql
> select 'a '::char(3) = 'a'::char(3), length('a  '::char(3)), 'a '::char(3) || 'b';
> ```
>
> ```text
>  ?column? | length | ?column?
> ----------+--------+----------
>  t        |      1 | ab
> ```
>
> 比较时会忽略尾随空格，拼接时会剥掉尾随空格。即便是 ISO 国家代码，也应该使用 `text`，并加上 `check (length(code) = 2)`。公平地说，很多年来，提高 `varchar(n)` 的 `n` 一直只需修改元数据；降低它仍然需要实际处理。

## 排序规则决定什么算相等 {#collations-decide-what-equal-means}

**排序规则**是比较和排序文本的一组规则。它决定 `'a' < 'B'` 是否成立、`'_draft'` 排在哪里，以及 `'Ada@Example.com'` 是否等于 `'ada@example.com'`。每个文本索引都按排序规则的顺序建立，因此排序规则和数据一样，都会牢牢写入索引。

同一台服务器上，六个相同的词在四种排序规则下的排序结果：

```sql
select string_agg(x, ' ' order by x collate "C")          as c,
       string_agg(x, ' ' order by x collate "en_US.utf8") as libc_en,
       string_agg(x, ' ' order by x collate "unicode")    as icu,
       string_agg(x, ' ' order by x collate "pg_c_utf8")  as builtin
from (values ('apple'), ('Banana'), ('cherry'),
             ('_draft'), ('Zebra'), ('éclair')) v(x);
```

```text
 c        Banana Zebra _draft apple cherry éclair
 libc_en  apple Banana cherry _draft éclair Zebra
 icu      _draft apple Banana cherry éclair Zebra
 builtin  Banana Zebra _draft apple cherry éclair
```

（横放后才能容纳。）`C` 和 builtin 按代码点排序：大写字母在前，`é` 排在 `z` 后面。glibc 的 `en_US` 几乎忽略标点；ICU 把标点放在最前面。四种排序规则，三种顺序：同一份数据，却对“接下来是什么”给出三种答案。

### 选择顺序永不变化的排序规则提供者 {#pick-a-collation-provider-whose-order-never-changes}

| 提供者 | 规则来自 | 何时变化 |
|---|---|---|
| libc | 操作系统 C 库（glibc） | 升级操作系统时 |
| ICU | ICU 库 | 升级 ICU 时 |
| builtin | Postgres 自身 | 排序顺序永不变化 |

<span id="tbl-12-2"></span>

**Table 12-2.** 选择顺序永不变化的排序规则提供者


除非有人另行选择，你的数据库使用 libc，因为 `initdb` 会复制操作系统区域设置（本例是在 glibc 2.41 上使用 `en_US.utf8`）。builtin 提供者在 PostgreSQL 17 中加入。我们新数据库的默认设置是 builtin `C.UTF-8`，按代码点排序，永不变化：

```sql
create database app template template0
  locale_provider builtin builtin_locale 'C.UTF-8';
```

字节比较、能经受每次操作系统升级的排序顺序，以及一个能服务于 `like 'abc%'` 的普通[B-树](/43-glossary/#b-tree)（[前缀搜索需要正确的操作符类](/18-more-indexes/#prefix-search-needs-the-right-operator-class)）。人们会阅读排序结果的地方，就明确要求它：`order by name collate "unicode"`。需要时使用语言学排序，其他地方保持稳定。

<span id="builtin-sorts-like-c-but-knows-a-capital-letter" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-builtin-sorts-like-c-but-knows-a-capital-letter" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：builtin 像 C 一样排序，但认识大写字母
>
> ```sql
> select upper('éclair' collate pg_c_utf8) as builtin,
>        upper('éclair' collate "C")       as c;
> ```
>
> ```text
>  builtin | c
> ---------+--------
>  ÉCLAIR  | éCLAIR
> ```
>
> PostgreSQL 18 增加了 `pg_unicode_fast`，它还会执行完整的 Unicode 大小写映射（`ß` 会折叠为 `ss`）。实验会检查排序示例中的全部四种顺序。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > create temp table words as
> >   select x from (values ('apple'), ('Banana'), ('cherry'),
> >                         ('_draft'), ('Zebra'), ('éclair')) v(x);
> > select lab.prove('C sorts by code point: Banana Zebra _draft apple cherry éclair',
> >   (select string_agg(x, ' ' order by x collate "C") from words)
> >     = 'Banana Zebra _draft apple cherry éclair');
> > select lab.prove('builtin pg_c_utf8 sorts exactly like C',
> >   (select string_agg(x, ' ' order by x collate "pg_c_utf8") from words)
> >     = (select string_agg(x, ' ' order by x collate "C") from words));
> > select lab.prove('glibc en_US puts _draft after cherry: apple Banana cherry _draft éclair Zebra',
> >   (select string_agg(x, ' ' order by x collate "en_US.utf8") from words)
> >     = 'apple Banana cherry _draft éclair Zebra');
> > select lab.prove('ICU (unicode, root locale) puts _draft first: _draft apple Banana cherry éclair Zebra',
> >   (select string_agg(x, ' ' order by x collate "unicode") from words)
> >     = '_draft apple Banana cherry éclair Zebra');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

### 对电子邮件，用 lower() 作为唯一键 {#for-emails-make-lower-the-unique-key}

上面的每种排序规则都是**确定性的**：只有字节相同的字符串才相等。因此，`where email = 'USER1@EXAMPLE.COM'` 在 `user1@example.com` 已经存在时什么也找不到；`unique (account_id, email)` 也会欣然接受 `Ada@x.com` 和 `ada@x.com`，于是用户以为相同的账户就变成了两个。四种工具可以解决它：

- **`lower()` 加唯一表达式索引。** 到处都能用；查询必须像索引一样准确写出 `lower(email)`（[索引恰好保存你交给它的东西](/17-btree/#an-index-stores-exactly-what-you-gave-it)）。
- **`citext`。** 一个会替你调用 `lower()` 的 contrib 类型。
- **非确定性 ICU 排序规则**（PostgreSQL 12+），不同字节可以比较为相等。它最正确，但只有从 PostgreSQL 18 开始 `like` 才能用于它，而且永远不能通过 B-树使用。
- **`casefold()`**（PostgreSQL 18+），符合 Unicode 规则的 `lower()`。在 ICU 或 `pg_unicode_fast` 排序规则下，`STRASSE` 和 `Straße` 会匹配；在 libc 或 builtin `C.UTF-8` 下，`ß` 仍然是 `ß`。

对于电子邮件和用户名，我们的默认选择是使用带唯一表达式索引的 `lower()`：无聊，但可预测。

> [!NOTE] 来自 MySQL 的读者
>
> MySQL 的默认排序规则忽略大小写和重音；Postgres 的确定性排序规则不会。导入前，为每个列选择工具（[字符串按精确字符比较](/11-not-mysql/#strings-compare-exactly)）。

<span id="a-case-insensitive-collation-and-the-scan-it-costs-you" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-a-case-insensitive-collation-and-the-scan-it-costs-you" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：不区分大小写的排序规则，以及它让你付出的扫描成本
>
> ```sql
> create collation case_insensitive (
>   provider = icu, locale = 'und-u-ks-level2', deterministic = false
> );
> create table users_ci (
>   id bigint primary key, account_id bigint not null,
>   email text collate case_insensitive not null,
>   unique (account_id, email)
> );
> insert into users_ci select id, account_id, email from users;
> insert into users_ci values (1000001, 2, 'USER1@EXAMPLE.COM');
> ```
>
> ```text
> ERROR:  duplicate key value violates unique constraint
>         "users_ci_account_id_email_key"
> ```
>
> `ks-level2` 忽略大小写；`ks-level1` 连重音也忽略。相等判断会使用索引，这次大写查询只需要 7 页。从 PostgreSQL 18 开始 `like` 才能工作，而且只能进行扫描：
>
> ```text
>  Aggregate (actual rows=1.00 loops=1)
>    Buffers: shared hit=1 read=833 dirtied=1
>    ->  Seq Scan on users_ci (actual rows=1.00 loops=1)
> ```
>
> 为了找一行，却要读取全部 834 页。正则表达式和 `ilike` 则会直接失败。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove('the case-insensitive unique constraint rejects USER1@EXAMPLE.COM',
> >   lab.try($$ insert into users_ci values (1000001, 2, 'USER1@EXAMPLE.COM') $$)
> >     like '23505:%users_ci_account_id_email_key%');
> > select lab.prove('but it is a sequential scan of all 834 pages',
> >   'Seq Scan' = any(lab.nodes($$ select count(*) from users_ci where email like 'USER4242@%' $$))
> >   and lab.buffers($$ select count(*) from users_ci where email like 'USER4242@%' $$) >= 834);
> > select lab.prove('regular expressions fail: nondeterministic collations are not supported',
> >   lab.try($$ select count(*) from users_ci where email ~ 'x' $$)
> >     like '0A000: nondeterministic collations are not supported%');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

### 操作系统升级可能破坏索引 {#an-os-upgrade-can-corrupt-your-indexes}

文本上的 B-树会按照插入当天排序规则认定的顺序保存键。规则改变后，搜索会用新规则沿着旧顺序行走，走进错误的分支。不会报错，只是再也找不到那些行。

这不是假设。glibc 2.28（2018 年）重写了大多数区域设置的排序规则数据，而随之发布的操作系统版本（包括 Debian 10、RHEL 8 和 Ubuntu 18.10）可能会重新排列任何使用 libc 排序规则的索引中的键（`C` 或 `POSIX` 除外），无论该索引是原地升级还是作为物理副本恢复的。位于更新操作系统镜像上的物理副本也有同样的问题。

我们在一个临时数据库中模拟了这个问题。主键不再能找到表中仍然存在的 `'Zebra'`。接着它又接受了第二个 `'Zebra'`。然后，通常的修复办法 `reindex` 在这个重复值上失败了。先是漏掉行，然后是破坏修复过程的重复值。

<span id="watch-an-index-lose-a-row-it-still-holds" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-watch-an-index-lose-a-row-it-still-holds" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：看着索引丢掉它仍然保存的行
>
> 让目录中的排序规则指向另一套规则，这正是从 Postgres 一侧看操作系统升级的样子（仅限临时数据库）：
>
> ```sql
> create collation app_en (provider = libc, locale = 'en_US.utf8');
> create table tags (name text collate app_en primary key);
> insert into tags values ('apple'), ('Banana'), ('cherry'), ('_draft'), ('Zebra'),
>                         ('éclair'), ('delta'), ('Echo'), ('fig'), ('Golf');
> insert into tags select 'tag' || g from generate_series(1, 5000) g;
> insert into tags select 'Tag' || g from generate_series(1, 5000) g;
>
> update pg_collation
> set collcollate = 'C.utf8', collctype = 'C.utf8', collversion = null
> where collname = 'app_en';
> ```
>
> 在新会话中，`name = 'Zebra'` 什么也不返回，`name >= 'Tag1' and name < 'Tag2'` 的计数为 0；关闭索引扫描后，则分别找到 `Zebra` 和 1,111 行。然后：
>
> ```text
> insert into tags values ('Zebra');
> INSERT 0 1
> reindex index tags_pkey;
> ERROR:  could not create unique index "tags_pkey"
> DETAIL:  Key (name)=(Zebra) is duplicated.
> ```
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > -- in the lab's own throwaway clone, after the pg_collation update and a reconnect
> > select lab.prove('after the change, the index finds no Zebra',
> >   (select count(*) from tags where name = 'Zebra') = 0);
> > select lab.prove('with index scans disabled, the same queries find Zebra and 1,111 rows',
> >   (select count(*) from tags where name = 'Zebra') = 1
> >   and (select count(*) from tags where name >= 'Tag1' and name < 'Tag2') = 1111);
> > select lab.prove('the primary key accepts a second Zebra',
> >   lab.try($$ insert into tags values ('Zebra') $$) = 'ok');
> > select lab.prove('reindex fails: could not create unique index "tags_pkey"',
> >   lab.try($$ reindex index tags_pkey $$) like '23505: could not create unique index "tags_pkey"%');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

### 在它造成伤害前发现问题 {#detect-it-before-it-bites}

Postgres 会记录每个排序规则对应的库版本（从 PostgreSQL 15 开始，还会记录数据库默认排序规则的版本），并在使用时发现版本变化就发出警告。应用不会把这个警告显示给任何人，所以每次操作系统或镜像发生变化后都要主动检查：

```sql
select collname, collversion as recorded, pg_collation_actual_version(oid) as actual
from pg_collation where collversion <> pg_collation_actual_version(oid);
```

```text
 collname | recorded | actual
----------+----------+--------
 app_en   | 2.28     | 2.41
```

把任何不匹配都当作“在信任之前先重建”。修复重复值，在该排序规则下对每个文本索引执行 `reindex`（并发执行），用 `amcheck` 检查（它会按今天的规则验证每个索引的顺序），然后用 `alter collation ... refresh version` 记录新版本。

> [!DANGER] 只有重建索引后才能刷新版本
>
> `refresh version` 什么也修复不了，它只会让警告消失。把操作系统升级、基础镜像更新和新副本镜像都当成排序规则事件；或者使用 builtin 提供者，从此不再操心。

<span id="the-warning-the-exposed-indexes-and-amcheck" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-the-warning-the-exposed-indexes-and-amcheck" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：警告、暴露的索引和 amcheck
>
> 使用过时排序规则的会话会得到：
>
> ```text
> WARNING:  collation "app_en" has version mismatch
> DETAIL:  The collation in the database was created using version 2.28,
> but the operating system provides version 2.41.
> ```
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove('the version check finds app_en recorded at 2.28 against the library''s 2.41',
> >   (select (collversion, pg_collation_actual_version(oid)) = ('2.28', '2.41')
> >    from pg_collation
> >    where collversion <> pg_collation_actual_version(oid) and collname = 'app_en'));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

> 对于数据库默认排序规则，比较 `datcollversion` 与 `pg_database_collation_actual_version(oid)`。从 PostgreSQL 15 开始，数据库会在每次新连接时发出警告；我们修改了一个临时数据库的目录行，让它声称使用了较旧的 glibc：
>
> ```text
> WARNING:  database "w_edges" has a collation version mismatch
> DETAIL:  The database was created using collation version 2.31,
> but the operating system provides version 2.41.
> HINT:  Rebuild all objects in this database that use the default collation
> and run ALTER DATABASE w_edges REFRESH COLLATION VERSION,
> or build PostgreSQL with the right library version.
> ```
>
> 执行 `alter database w_edges refresh collation version` 后返回 `NOTICE: changing version from 2.31 to 2.41`，除此之外什么也没有修复。先按提示重建，再刷新。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove(
> >   'the recorded collation version (2.31) no longer matches the library',
> >   (select datcollversion <> pg_database_collation_actual_version(oid)
> >    from pg_database where datname = current_database()));
> > select lab.prove(
> >   'REFRESH COLLATION VERSION only rewrites the recorded number',
> >   (select datcollversion = pg_database_collation_actual_version(oid)
> >    from pg_database where datname = current_database()));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)
>
> 受影响的索引，是那些使用 libc 或 ICU 排序规则的文本索引：
>
> ```sql
> select i.indexrelid::regclass as index_name, i.indrelid::regclass as table_name
> from pg_index i
> join pg_class c on c.oid = i.indexrelid
> join pg_namespace n on n.oid = c.relnamespace
> where n.nspname not in ('pg_catalog', 'information_schema')
>   and exists (
>     select 1 from unnest(i.indcollation::oid[]) as k(coll)
>     join pg_collation pc on pc.oid = k.coll
>     where pc.collprovider <> 'b' and pc.collname not in ('C', 'POSIX'))
> order by 2, 1;
> ```
>
> 默认排序规则显示为提供者 `d`，因此即便在使用 builtin 的数据库中，这个查询也会列出默认排序规则的索引。接着用 `amcheck` 按今天的规则验证索引顺序，只需持有轻量锁：
>
> ```text
> select bt_index_check('tags_pkey', true);
> ERROR:  item order invariant violated for index "tags_pkey"
> ```
>
> 在升级切换前，对恢复出的副本运行一次；升级后，再对大型文本索引运行一次。

## 时间戳：始终使用 timestamptz {#timestamps-always-timestamptz}

用 `timestamptz` 存储时间点；凡是已经发生的事情，都不要使用不带时区的 `timestamp`。本章几乎没有哪条规则的例外比它更少。

名称会误导人。`timestamptz` 不存储时区；两种类型都是 8 字节。`timestamptz` 表示一个绝对时刻，以 UTC 保存。`timestamp` 表示挂钟读数：“22:51”是哪个城市的时间？两个应用服务器在同一时刻插入，一个会话使用纽约时间（连接池配置错误，或某台 cron 机器忘了设置），另一个会话使用 UTC：

```sql
create table tz_demo (label text, ts timestamp, tstz timestamptz);
set timezone = 'America/New_York';
insert into tz_demo values ('new york', now(), now());
set timezone = 'UTC';
insert into tz_demo values ('utc', now(), now());
```

```text
  label   |             ts             |             tstz
----------+----------------------------+-------------------------------
 new york | 2026-10-05 22:51:37.32279  | 2026-10-06 02:51:37.32279+00
 utc      | 2026-10-06 02:51:37.327957 | 2026-10-06 02:51:37.327957+00
```

`timestamptz` 两次记录的是同一个时刻。`timestamp` 记录的是相隔四小时的两个读数，而所有基于它的“最近一小时”仪表盘现在都悄悄错了。你无法修复它：时区从未被存储。

让每个连接都使用 `timezone = UTC`，发送时间戳时带上明确的偏移量，在边界处完成转换。

`now()` 是事务开始的时刻，因此一个持续一小时的批处理会给每一行盖上相同的时间。`clock_timestamp()` 才是挂钟时间（[日期/时间函数](https://www.postgresql.org/docs/18/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT)）。

唯一诚实的例外，是必须经受时区规则变化的未来本地时间（“2027-03-01 当地时间 09:00 开门”）：存储 `timestamp` 加时区名称。

<span id="daylight-saving-one-hour-one-day-and-24-hours-differ" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-daylight-saving-one-hour-one-day-and-24-hours-differ" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：夏令时：一小时、一天和 24 小时并不相同
>
> 2026 年 3 月 8 日，纽约从 02:00 跳到了 03:00：
>
> ```sql
> set timezone = 'America/New_York';
> select '2026-03-08 01:30'::timestamptz + interval '1 hour'   as plus_hour,
>        '2026-03-08 01:30'::timestamptz + interval '1 day'    as plus_day,
>        '2026-03-08 01:30'::timestamptz + interval '24 hours' as plus_24h;
> ```
>
> ```text
>        plus_hour        |        plus_day        |        plus_24h
> ------------------------+------------------------+------------------------
>  2026-03-08 03:30:00-04 | 2026-03-09 01:30:00-04 | 2026-03-09 02:30:00-04
> ```
>
> `1 day` 保持挂钟时间；`24 hours` 增加 86,400 秒。这只对命名的会话时区有效，对固定偏移量无效。普通 `timestamp` 对第一个结果会给出 02:30，而这个时间在纽约根本不存在。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove('timestamp records the two inserts four hours apart; timestamptz under a second apart',
> >   (select max(ts) - min(ts) between interval '4 hours' and interval '4 hours 1 second'
> >       and max(tstz) - min(tstz) < interval '1 second' from tz_demo));
> > set timezone = 'America/New_York';
> > select lab.prove('across the 2026-03-08 DST jump: +1 hour is 03:30, +1 day is 01:30 next day, +24 hours is 02:30',
> >   ('2026-03-08 01:30'::timestamptz + interval '1 hour')::text   = '2026-03-08 03:30:00-04'
> >   and ('2026-03-08 01:30'::timestamptz + interval '1 day')::text    = '2026-03-09 01:30:00-04'
> >   and ('2026-03-08 01:30'::timestamptz + interval '24 hours')::text = '2026-03-09 02:30:00-04');
> > select lab.prove('a plain timestamp returns 02:30, a time that never existed in New York',
> >   ('2026-03-08 01:30'::timestamp + interval '1 hour')::text = '2026-03-08 02:30:00');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

<span id="now-stands-still-for-the-whole-transaction" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-now-stands-still-for-the-whole-transaction" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：now() 在整个事务中保持不变
>
> 在一个事务中两次读取 `now()` 和 `clock_timestamp()`，中间执行两秒的 `pg_sleep`：
>
> ```text
>               now              |             clock
> -------------------------------+-------------------------------
>  2026-10-06 03:47:07.379127+00 | 2026-10-06 03:47:07.379301+00
> ...
>  2026-10-06 03:47:07.379127+00 | 2026-10-06 03:47:09.381908+00
> ```
>
> `statement_timestamp()` 是第三种选择：当前语句开始的时刻。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > begin;
> > select pg_sleep(0.05) is null as slept \gset
> > select lab.prove('inside a transaction now() stays at its start; clock_timestamp() moves on',
> >   clock_timestamp() - now() >= interval '50 milliseconds');
> > commit;
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

## 时间戳胜过布尔值；检查约束胜过新类型 {#a-timestamp-beats-a-boolean-a-check-beats-a-new-type}

`date` 用来表示真正的日期，比如生日，而不是“创建于何时”这种你只是在展示时转换为日期的时间点。查询时间范围时使用半开区间：`>= '2026-10-01' and < '2026-11-01'`（[日期范围是半开的](/22-queries/#date-ranges-are-half-open)），并记住 `'2026-01-31'::date + interval '1 month'` 是 2 月 28 日。

在增加一个布尔值前，先问问自己是否将来会想知道它**何时**变成了真。`archived_at timestamptz` 用 `is not null` 回答“它已归档吗？”，还能回答“何时归档”，代价是多 7 个字节。对于审计关心的状态（已验证、已归档、已取消），时间戳更好。

要把列限制在一组值中，我们的默认选择是 `text` 加检查约束，比如运行示例中的 `check (plan in ('free', 'team', 'enterprise'))`。每个工具都理解它，而且可以在线替换。如果值本身还携带数据，就使用查找表。

### 枚举很难收回 {#enums-are-hard-to-take-back}

枚举占 4 字节，并按声明顺序排序：这对于真正稳定且有序的集合（比如日志严重性）很合适。但有两个陷阱。没有 `drop value`，所以要废弃一个值，就得创建新类型并重写表。而且，在事务中添加的值要等事务提交后才能使用；这会破坏那些在同一事务中添加值并填充行的迁移工具（`ERROR: unsafe use of new value "audit" of enum type event_kind`）。

## 用 uuid 存储 UUID {#store-uuids-as-uuid}

`uuid` 占 16 字节；作为 `text` 要占 40 字节，每个索引也会随之变大（[慎选键，少后悔](/13-keys/#basics-chapter-13)对此有测量）。

所有了解自身领域的类型也是如此。`inet` 和 `cidr` 会验证地址，并用一个操作符回答“这个 IP 是否在那个网络中”。范围类型（`tstzrange`、`daterange`）让“不能有两个预订重叠”成为一个[排他约束](/14-relationships/#an-exclusion-constraint-makes-overlaps-impossible)。而 `create domain email_address as text check (...)` 则把反复出现的规则只写一次。

## 数组适合随行生灭的列表 {#arrays-are-for-lists-that-live-and-die-with-the-row}

数组适合属于一行的小列表：标签、通知渠道。当元素其实是伪装成列表的行时，数组就不合适：它们引用其他表（元素上没有外键），你一次只更新一个元素（每次更新都会[重写整行](/06-mvcc/#basics-chapter-6)），或者列表会无限增长。项目上的用户 ID 数组，其实就是一张尚未创建的成员关系表。

## 使用 jsonb，把要查询的键放进独立列 {#use-jsonb-and-give-the-keys-you-query-their-own-columns}

`json` 存储文本，每次访问时都要重新解析；`jsonb` 只解析一次，并支持[GIN](/43-glossary/#gin)索引。使用 `jsonb`，只在需要精确保留输入时使用 `json`，比如要验证签名的 webhook 请求体。

`jsonb` 不是免费的。每一行都会重复保存每个键：`events.payload` 每行要花 63 到 69 字节来拼出 `"status"`、`"duration_ms"` 和 `"region"`，大约是类型化列所需空间的五倍。而且规划器几乎没有文档内部的统计信息（[大多数文档都应该放在 jsonb 列中](/15-models/#most-documents-belong-in-a-jsonb-column)）。要用于过滤、连接、排序或约束的键，都应该有自己的列。

<span id="what-jsonb-does-to-your-input" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-what-jsonb-does-to-your-input" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：jsonb 会如何处理你的输入
>
> ```sql
> select '{"a":1, "a":2, "b" : [1,2]}'::json  as j,
>        '{"a":1, "a":2, "b" : [1,2]}'::jsonb as jb;
> ```
>
> ```text
>               j              |          jb
> -----------------------------+-----------------------
>  {"a":1, "a":2, "b" : [1,2]} | {"a": 2, "b": [1, 2]}
> ```
>
> 它也不更小：`{"status":"ok","duration_ms":120,"region":"iad"}` 作为 `json` 占 52 字节，作为 `jsonb` 占 68 字节。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove('events.payload values take 63 to 69 bytes each',
> >   (select min(pg_column_size(payload)) >= 63 and max(pg_column_size(payload)) <= 69 from events));
> > select lab.prove('the same facts as typed columns would take about a fifth of the space',
> >   (select avg(pg_column_size(payload))
> >           / avg((octet_length(payload->>'status') + 1) + 4 + (octet_length(payload->>'region') + 1))
> >    from (select payload from events tablesample system (5) repeatable (42)) e) between 5 and 6);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

## 生成列默认是虚拟的（PostgreSQL 18+） {#generated-columns-are-virtual-by-default-postgresql-18}

PostgreSQL 18 默认让生成列成为**虚拟列**：读取时计算，不占空间，也不可能建立索引。**存储列**在写入时计算。展示用的简单值使用虚拟列；需要过滤或排序的值使用存储列，比如 `tsvector`（[存储 tsvector，不要给表达式建索引](/23-search/#store-the-tsvector-dont-index-the-expression)）。PostgreSQL 18 之前，语法必须写 `stored`，因此已有迁移和升级后的列都安全；现在新增的变化是，不写关键字的新 DDL 会得到虚拟列。需要存储列时，就明确写出 `stored`。

<span id="virtual-by-default-and-why-you-cant-index-it" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-12-virtual-by-default-and-why-you-cant-index-it" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：默认是虚拟列，以及为什么不能给它建索引
>
> ```sql
> create table g_demo (
>   id           bigint generated always as identity primary key,
>   price_cents  bigint not null,
>   qty          int    not null,
>   total_cents  bigint generated always as (price_cents * qty),
>   total_stored bigint generated always as (price_cents * qty) stored
> );
> create index on g_demo (total_cents);
> create index on g_demo (total_stored);
> ```
>
> ```text
> ERROR:  indexes on virtual generated columns are not supported
> CREATE INDEX
> ```
>
> 表达式必须是不可变的：不能使用 `now()`，也不能引用其他表。
>
> > [!EXAMPLE]- 动手验证 · lab `11-types`
> >
> > ```sql
> > select lab.prove('in PostgreSQL 18 the default is virtual (v); stored is s',
> >   (select array_agg(attgenerated::text order by attnum) from pg_attribute
> >    where attrelid = 'g_demo'::regclass and attgenerated <> '') = array['v', 's']);
> > select lab.prove('indexes on virtual generated columns are not supported; on stored ones they are',
> >   lab.try($$ create index on g_demo (total_cents) $$)
> >     = '0A000: indexes on virtual generated columns are not supported'
> >   and lab.try($$ create index on g_demo (total_stored) $$) = 'ok');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/11-types.sql)

## 四种类型得不偿失 {#four-types-cost-more-than-they-save}

### serial 是序列，不是类型 {#serial-is-a-sequence-not-a-type}

`serial` 不是类型。它是一个序列，加上一个 `nextval()` 默认值，作用于 4 字节 `integer`；这正是本章开头用到的类型，而这个序列永远不会知道手工插入的 ID：

```sql
create table s_demo (id serial primary key, v text);
insert into s_demo (id, v) values (1, 'manual');
insert into s_demo (v) values ('auto');
```

```text
ERROR:  duplicate key value violates unique constraint "s_demo_pkey"
```

相反，`bigint generated always as identity` 会拒绝手工指定的 ID（[让数据库为行编号，永远不要相信顺序](/13-keys/#let-the-database-number-rows-and-never-trust-the-order)）。

此外还要避免 `money`（由服务器区域设置格式化，不知道货币种类）、`char(n)`（见上文）和 `timetz`（没有日期的偏移量无法知道夏令时；文档本身也不推荐它）。

## 本章要点 {#the-short-version}

- 每个 ID 以及每个引用 ID 的列都使用 `bigint`。
- 金额使用 `numeric` 或以分为单位的 `bigint`，并另设货币列。绝不用浮点数。
- 使用带检查约束的 `text`，不要使用 `varchar(n)`。绝不用 `char(n)`。
- 默认排序规则区分大小写。对于电子邮件，使用基于 `lower(email)` 的唯一索引。
- 把每次操作系统、glibc 或 ICU 升级都当作排序规则事件：检查版本，重建文本索引，然后 `refresh version`。或者使用 builtin `C.UTF-8`（PostgreSQL 17+）。
- 每个时间点都使用 `timestamptz`，会话使用 UTC，输入带明确的偏移量。
- 优先使用 `archived_at` 而不是 `is_archived`，使用带检查约束的 `text` 而不是枚举，使用 `jsonb` 而不是 `json`，并为要查询的 JSON 键建立独立列。
- 使用 identity，不使用 `serial`。在 PostgreSQL 18 中，除非写出 `stored`，否则生成列都是虚拟的。

## 延伸阅读 {#further-reading}

- [Data Types](https://www.postgresql.org/docs/18/datatype.html)，PostgreSQL 文档。所有内置类型及其存储大小和输入规则。
- [Collation Support](https://www.postgresql.org/docs/18/collation.html)，PostgreSQL 文档。提供者、确定性和非确定性排序规则。
- [amcheck](https://www.postgresql.org/docs/18/amcheck.html)，PostgreSQL 文档。`bt_index_check` 验证什么，以及它持有的锁。
- [Locale data changes](https://wiki.postgresql.org/wiki/Locale_data_changes)，PostgreSQL Wiki。哪些 glibc 和发行版版本改变了排序顺序。
- [How To Correct and Identify Indexes Affected by the GNU C 2.28 Update](https://www.crunchydata.com/blog/glibc-collations-and-data-corruption)，Crunchy Data。实践者关于如何查找并重建受影响索引的过程。
- [Case-insensitive pattern matching in PostgreSQL](https://www.cybertec-postgresql.com/en/case-insensitive-pattern-matching-in-postgresql/)，Laurenz Albe，CYBERTEC。深入比较 `lower()`、`citext` 和非确定性排序规则的取舍。
- [Don't Do This](https://wiki.postgresql.org/wiki/Don%27t_Do_This)，PostgreSQL Wiki。社区列出的类型错误清单，以及每种错误何时可以接受。