由 John Doe 三月 6, 2025
摘要：在本文中，我们将了解 PostgreSQL 中某些数据类型的一些注意事项。
目录
日期/时间存储
不要使用不带时区的 timestamp
不要使用timestamp类型来存储时间戳，而是使用timestamptz（也称为timestamp with time zone）。
为什么不呢？
timestamptz记录单个时刻。不管类型名的意思，它并不存储时间戳，只是一个时间点，表示为自 UTC 时间 2000 年 1 月 1 日以来的微秒数。您可以用任何时区插入值，它将存储该值描述的时间点。默认情况下，它将以您当前的时区显示时间，但您可以使用at time zone以其他时区来显示它。
因为它存储了一个时间点，所以它可以对涉及不同时区输入的时间戳执行正确的算术运算，包括在同一位置的夏令时不同侧的时间戳变化的时候。
timestamp（也称为timestamp without time zone）不能做到这些，它只存储您给它的日期和时间。你可以把它想象成一张日历和一个时钟的照片，而不是一个时间点。如果没有时区信息，你不知道它记录的时间。因此，来自不同位置的时间戳之间，或夏季和冬季的时间戳之间的算术运算，可能会给出错误的答案。
因此，如果要存储的是时间点，而不是时钟的照片，请使用 timestamptz。
应该什么时候用？
如果你以一种抽象的方式处理时间戳，或者只是从应用程序中保存和检索它们，而你不打算对它们进行算术运算，那么 timestamp 可能是合适的。
不要使用不带时区的 timestamp 来存储 UTC 时间
遗憾的是，将 UTC 值存储在timestamp without time zone列中，通常是从其他缺少可用时区支持的数据库继承而来的做法。
请改用timestamp with time zone。
为什么不呢？
因为数据库无法知道列值的预期时区是 UTC。
这使得许多原本有用的时间计算变得复杂。例如，“按照 u.timezone 给出的时区，最近一个午夜” 将变为：
date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'
而 “按 u.timezone 时区，x.datecol时间以前的午夜” 则变为：
date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)
AT TIME ZONE u.timezone AT TIME ZONE 'UTC'
应该什么时候用？
要兼容不支持时区的数据库的要求，优先于所有其他考虑因素的情况下。
不要使用 timetz
不要使用timetz类型。你可能要的是timestamptz。
为什么不呢？
甚至文档中也告诉您它只是为了 SQL 兼容性而支持的。
类型
time with time zone是 SQL 标准定义的，但是该定义显示出了一些会影响实用性的性质。在大多数情况下，date、time、timestamp without time zone和timestamp with time zone的组合，就应该能提供任何应用所需的全范围的日期/时间功能了。
应该什么时候用？
不要使用。
文本存储
不要使用 char(n)
不要使用类型char(n)。你可能要的是text。
为什么不呢？
您插入到char(n)字段中的任何字符串，都会用空格填充到声明的宽度。这可能不是你真正想要的。
文档提到了：
类型
character的值物理上都用空格填充到指定的长度*n*， 并且以这种方式存储和显示。不过，尾随的空格被当作是没有意义的，并且在比较两个character类型值时不会考虑它们。在空格有意义的排序规则中，这种行为可能会产生意料之外的结果，例如SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)会返回真（即便区域设置C会认为一个空格比新行更大）。当把一个character值转换成其他的一种字符串类型时，尾随的空格会被移除。请注意，在character varying和text值里， 结尾的空格语意上是有含义的，并且在使用模式匹配（如LIKE和正则表达式）时也会被考虑到。
空格填充确实浪费了空间，但不会使对它的操作更快；事实上，由于在许多情况下需要去除空格，操作反而会更慢。
需要注意的是，从存储的角度来看，char(n)不是一种固定宽度的类型。实际字节数会有所不同，因为字符可能占用多个字节，因此存储的值无论如何都会被视为可变长度（即使存储空间中包含了填充的空格）。
应该什么时候用？
当您移植非常老旧的使用了固定宽度字段的软件时。或者在你阅读完上面文档中的片段，并认为这正好是你需要的类型的时候。
即使对于定长的标识符，也不要使用 char(n)
有时人们会认为 “我的值必须始终正好是 N 个字符长”，而坚持要使用char(n)，例如，来自其他系统的国家代码、哈希值或标识符。即使在这些情况下，使用char(n)也仍然是一个坏主意。
请使用text，或text上的域类型，带上CHECK(length(VALUE)=3)或CHECK(VALUE ~ '^[[:alpha:]]{3}$')，或者其他类似的。
为什么不呢？
因为char(n)不会拒绝太短的值，它只是静默地用空格填充它们。因此，相对于带有检查确切长度的约束的text，使用它并没有什么实际的好处。额外的好处是，这类检查还可以验证值的格式是否正确。
请记住，使用char(n)替代varchar(n)没有任何性能上的优势。事实上，情况恰恰相反。会出现的一个特殊问题是，如果您尝试将char(n)字段与驱动程序已明确指定类型text或varchar的参数进行比较，则可能会意外地无法使用索引进行比较。这可能很难定位，因为它不会在手动执行查询的时候显示出来。
应该什么时候用？
不要使用。
不要默认使用 varchar(n)
默认情况下，不要使用类型varchar(n)。请改用varchar（没有长度限制）或text。
为什么不呢？
varchar(n)是一个可变长度的文本字段，如果您尝试在其中插入长度超过 n 个字符（不是字节）的字符串，则会引发错误。
varchar（不带(n)）或text是类似的，但没有长度限制。如果将相同的字符串插入到这三种字段类型中，它们会占用完全相同的空间量，并且您也无法测量出性能上的任何差异。
如果您确实需要的是具有长度限制的文本字段，那么varchar(n)很好，但是如果您选择了一个任意的长度，比如为一个姓名字段选择了 varchar(20)，那么将来有一个名字很长的人要注册您的服务时，您将会面临业务出错的风险。
有些数据库没有可以存放任意长文本的类型，或者即使有，也不如 varchar(n) 那么方便、高效或有很好的支持。这些数据库中的用户，在他们实际想要用text时，经常会使用类似varchar(255)的类型。
如果你需要约束字段中的值，你可能还需要比最大长度更具体的东西，可能还有最小长度，或者一组有限的字符，和一个检查约束，可以检查所有这些事情以及最大字符串长度。
应该什么时候用？
你确实想用的时候。如果你想要的是一个文本字段，如果你在其中插入太长的字符串会引发错误，并且你不想使用显式的检查约束，那么 varchar(n) 是一个非常好的类型。只是不要不假思索地自动使用它。
此外，varchar 类型是定义在 SQL 标准中的，与 text 类型不同，因此它可能是编写可移植性极高的应用程序的最佳选择。
其他数据类型
不要使用 money
数据类型money实际上不太适合存储货币值。numeric 或 integer（极少的情况下）可能更好。
为什么不呢？
它是一个定点类型，实现为系统底层的 int，因此使用它进行算术运算会很快。但它处理不了一美分的分数部分（或其他货币的等价物），它的四舍五入的行为可能不是你想要的结果。
它不存储具有该值的货币类型，而是假设所有 money 列都包含由数据库的 lc_monetary 区域设置指定的货币。如果您出于任何原因更改了 lc_monetary 设置，则所有 money 列都将会包含错误的值。这意味着，如果您在设置 lc_monetary 为 ’en_US.UTF-8’ 时插入 ‘$10.00’，然后 lc_monetary 更改了，您检索的值可能会变成 ‘10,00 Lei’ 或 ‘¥1,000’。
将值存储为 numeric，可能在相邻列中带上货币类型，这样可能会更好。
应该什么时候用？
如果您只需要处理单一的货币类型，不用处理零碎的美分，只对数据值进行加法和减法运算，那么 money 类型可能是正确的选择。
不要使用 serial
对于新的应用程序，应当改用标识列。
为什么不呢？
serial 类型有一些奇怪的行为，这些行为使表结构、依赖项和权限管理变得不必要地繁琐。
应该什么时候用？
- 如果您需要支持版本 10 以前的 PostgreSQL。
- 需要结合使用表继承的某些情况下。
- 更一般地说，如果您要以某种方式对多个表使用相同的序列时，尽管在这些情况下，显式声明可能比 serial 类型更可取。