PostgreSQL 03 - DDL

发表于 2024-07-30 更新于 2024-08-05 分类于 DB ， PostgreSQL 评论数：阅读次数：

PostgreSQL: The World's Most Advanced Open Source Relational Database.

DDL (Data Definition Language) 是 SQL (Structured Query Language) 的一部分，用于定义和管理数据库的结构。DDL 语句主要用于创建、修改和删除数据库对象，如表、索引、视图等。

创建和管理 PostgreSQL 数据库和模式的部分已经总结过了，本节主要总结表对象的管理。

创建表

关系数据库中的表非常像纸上的表格：它由行和列组成。列的数量和顺序是固定的，并且每列都有一个名称。行数是可变的 — 它反映了在给定时刻存储的数据量。SQL 不会对表中行的顺序做出任何保证。读取表时，除非明确请求排序，否则行将按未指定顺序显示。此外，SQL 不会为行分配唯一标识符，因此表中可能有多个完全相同行。

每列都有一个数据类型。数据类型限制了可以分配给一列的可能值集，并为存储在列中的数据分配语义，以便可用于计算。PostgreSQL 包含很多内置数据类型，用户还可以定义自己的数据类型。

使用 CREATE TABLE 语句创建一个新表：

CREATE TABLE products (
    id integer,
    name varchar,
    price numeric
);

至少应指明新表的表名，包含的列以及对应的类型。

默认值

可以为列分配一个默认值。当插入新行并且未为某些列指定值时，这些列将填充为各自的默认值。如果未明确声明默认值，则默认值为 NULL 值，NULL 值被视为表示未知数据。

在表定义中，默认值列在列数据类型之后，例如：

CREATE TABLE products (
    id integer,
    name varchar,
    price numeric DEFAULT 9.99
);

默认值可以是一个表达式，它将在插入默认值时进行计算 (而不是在创建表时)。一个常见的示例是 timestamp 列的默认值为CURRENT_TIMESTAMP，以便将其设置为行插入时间。另一个常见的示例是为每一行生成一个序列号。

生成列

生成列是一个特殊列，它总是根据其他列计算而来。因此，它对于列来说就像视图对于表一样。生成列有两种类型：存储的和虚拟的。存储的生成列在写入 (插入或更新) 时计算，并且占用存储空间，就像普通列一样。虚拟的生成列不占用存储空间，并且在读取时计算。因此，虚拟的生成列类似于视图，而存储的生成列类似于物化视图 (除了它总是自动更新)。PostgreSQL 目前仅实现存储的生成列。

创建生成列，在 CREATE TABLE 中使用GENERATED ALWAYS AS子句，例如：

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

无法直接写入生成列：在 INSERT 或 UPDATE 命令中，无法为生成列指定值，但可以使用DEFAULT占位，以确保生成列使用其默认的计算方式来生成值。

如果未提供其他值，则在首次插入行时，列默认值将被评估一次。每当行更改且无法覆盖时，生成列都会被更新。列默认值可能不会引用表中的其他列，生成表达式通常会这样做。列默认值可以使用不稳定的函数，例如random()或引用当前时间的函数。生成列不允许这样做。

生成列有以下限制：

生成表达式只能使用不可变函数，并且不能使用子查询或以任何方式引用当前行以外的内容。
生成表达式不能引用另一个生成列。
生成表达式不能引用系统列，tableoid除外。
生成列不能具有列默认值或标识定义。
生成列不能是分区键的一部分。
外部表可以有生成列。
对于继承和分区：
- 如果父列是生成列，则其子列也必须是生成列；但是，子列可以有不同的生成表达式。在插入或更新行期间实际应用的生成表达式是与行实际所在的表关联的表达式 (这与列默认值的行为不同：对于列默认值，应用查询中命名的表关联的默认值)。
- 如果父列不是生成列，则其子列也不应该为生成列。
- 对于继承表，如果在CREATE TABLE ... INHERITS中编写子列定义而不带任何GENERATED子句，则其GENERATED子句将自动从父级复制。ALTER TABLE ... INHERIT将坚持父列和子列在生成状态方面已经匹配，但不要求它们的生成表达式匹配。
- 类似地，对于分区表，如果在CREATE TABLE ... PARTITION OF中编写子列定义而不带任何GENERATED子句，则其GENERATED 子句将自动从父级复制。ALTER TABLE ... ATTACH PARTITION将坚持父列和子列在生成状态方面已经匹配，但不要求它们的生成表达式匹配。
- 在多重继承的情况下，如果一个父列是生成列，那么所有父列都必须是生成列。如果它们没有相同的生成表达式，那么必须明确指定子列的所需表达式。

使用生成列还需要考虑的因素：

生成列单独维护访问权限，与它们的基础列不同。因此，可以安排特定角色可以从生成列读取数据，但不能从基础列读取数据。
从概念上讲，生成列在BEFORE触发器运行后更新。因此在BEFORE触发器中对基础列进行的更改将反映在生成列中。但相反，不允许在BEFORE触发器中访问生成列。

约束

数据类型是一种限制表中可存储数据类型的方法。但是，对于许多应用程序而言，它们提供的约束太粗略。为此，SQL 允许对列和表定义约束。约束让用户可以根据需要对表中的数据进行尽可能多的控制。如果用户尝试将数据存储在违反约束的列中，则会引发错误。即使该值来自默认值定义，也适用此规则。

SQL 标准规定了列约束和表约束，列约束位于列之后，就跟默认值一样。列约束只应引用对应的列 (但是 PostgreSQL 不强制要求)。表约束独立于列，它是表定义中的一个单独条目。对于 PostgreSQL 来说，它们最终都是表约束。约束可以有一个名称，以便将来在修改或删除时引用，命名约束使用CONSTRAINT子句。

检查约束

检查约束是最通用的约束类型，它允许指定某个列中的值必须满足布尔值 (真值) 表达式。例如约束价格非负：

CREATE TABLE products (
    id integer,
    name varchar,
    price numeric CHECK (price > 0)
);

或者使用表约束，并命名该约束：

CREATE TABLE products (
    id integer,
    name varchar,
    price numeric,
    CONSTRAINT chk_price CHECK (price > 0)
);

应当注意，如果检查表达式计算结果为真或空值，则满足检查约束。由于大多数表达式在任何操作数为空时都会计算为空值，因此它们不会阻止受约束列中的空值。若要确保列不包含空值，可以使用非空约束。

非空约束

非空约束指定一列不得采用空值，非空约束必须是列约束。非空约束在功能上等效于检查约束CHECK (column_name IS NOT NULL)，但在 PostgreSQL 中创建显式非空约束更有效，缺点是无法为其命名。

CREATE TABLE products (
    id integer NOT NULL,
    name varchar NOT NULL,
    price numeric NOT NULL,
    CONSTRAINT chk_price CHECK (price > 0)
);

唯一约束

唯一约束确保表中所有行的一列或一组列中包含的数据是唯一的。唯一约束可以是列约束 (约束一列)，也可以为表约束 (约束一组列)，例如：

CREATE TABLE products (
    id integer NOT NULL,
    name varchar NOT NULL,
    price numeric NOT NULL,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT uq_id_name UNIQUE (id, name)
);

唯一约束将自动在约束中列出的列或列组上创建唯一的 B 树索引。如果表中存在多于一行，其中包含在约束中包含的所有列的值相等，则会违反唯一约束。默认情况下，两个 NULL 值是不相等的。这意味着即使存在唯一约束，也可以存储在至少一个受约束列中包含空值的重复行。可以通过添加子句NULLS NOT DISTINCT来更改此行为，例如：

CREATE TABLE products (
    id integer NOT NULL,
    name varchar NOT NULL,
    price numeric NOT NULL,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT uq_id_name UNIQUE (id, name) NULLS NOT DISTINCT
);

主键约束

主键约束表示一列或一组列可以用作表中行的唯一标识符。这要求值既唯一又非空。因此，下列定义等同：

CREATE TABLE products (
    id integer NOT NULL,
    name varchar NOT NULL,
    price numeric NOT NULL,
    CONSTRAINT uq_id UNIQUE (id)
);

CREATE TABLE products (
    id integer,
    name varchar NOT NULL,
    price numeric NOT NULL,
    CONSTRAINT pk_id PRIMARY KEY (id)
);

主键约束可以是列约束，也可以是表约束。添加主键将自动在主键中列出的列或列组上创建唯一的 B 树索引，并将强制将列标记为 NOT NULL (如果是列组，则其中的每个列都非空)。

一个表最多只能有一个主键 (可以有任何数量的唯一且非空约束，它们在功能上几乎是相同的东西，但只能将一个标识为主键)。关系数据库理论规定每个表都必须有一个主键，此规则不受 PostgreSQL 强制执行，但通常最好遵循它。

外键约束

外键约束指定一列或一组列中的值必须与另一张表中某一行的值匹配 (即引用完整性)。外键约束可以是列约束，也可以是表约束，例如：

CREATE TABLE orders (
    id integer PRIMARY KEY,
    product integer REFERENCES products (id),
    quantity integer
);

CREATE TABLE orders (
    id integer PRIMARY KEY,
    product integer,
    quantity integer,
    FOREIGN KEY (product) REFERENCES products (id),
);

可以省略引用列列表，写成... REFERENCES other_table，此时会将该表的主键作为引用列。

外键约束中，约束列必须与引用列数量类型匹配。引用列必须是唯一且非空的 (但不必是主键)，但是约束列本身可以是空，这种情况下表示约束列不关联外部表的任何一行。此外，外部表还可以是自己，即自引用外键。

在关系数据库理论中，实现一对多的关系时，通常对多的一方使用外键约束。实现多对多的关系时，通常会建立一个新表，并在新表中分别对双方使用外键约束，例如：

CREATE TABLE order_items (
    product_id integer REFERENCES products (id),
    order_id integer REFERENCES orders (id),
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

为了维护引用完整性，当用户试图从外部表中删除一个正在被引用的行时，系统会根据在定义外键约束时存在的ON DELETE子句进行处理：

删除失败：如果没有子句 (NO ACTION)。
删除失败：如果使用了ON DELETE RETRICT子句。
级联删除 (同时删除外键所在行)：如果使用了ON DELETE CASCADE子句。
删除成功 (但将外键列设置为 NULL)：如果使用了ON DELETE SET NULL子句。
删除成功 (但将外键列设置为默认值)：如果使用了ON DELETE SET DEFAULT子句。

NO ACTION 和 RESTRICT 的区别在于：NO ACTION 允许将检查推迟到事务的稍后时间，而 RESTRICT 不允许。

类似于 ON DELETE，还有ON UPDATE，当引用的列被更改 (更新) 时。除了 SET NULL 和 SET DEFAULT 不能指定列列表外，可能的操作是相同的。在这种情况下，CASCADE 表示引用的列的更新值应复制到引用行中。

修改表

修改表结构最直接的方法是删除原表然后重新建表，但当表被外部对象引用时，这种方式并不方便。另一种方式是使用ALTER TABLE命令，直接在原表的基础上进行修改。

添加列

使用ADD COLUMN colname coltype ...子命令向表中添加一列：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

在添加列时，还可以为其指定列约束，就跟 CREATE TABLE 一样。

从 PostgreSQL 11 开始，添加具有常量默认值的新列时不再需要更新表中的每一行。相反，将在下次访问该行时返回默认值，并在重写表时应用该默认值，即使在大型表上，也能使 ALTER TABLE 非常快速。但如果默认值不稳定 (如clock_timestamp())，则需要使用在执行ALTER TABLE时计算的值来更新每一行。

删除列

使用DROP COLUMN colname子命令从表中删除一列：

ALTER TABLE products DROP COLUMN description;

列中的任何数据都将消失，涉及该列的表约束也将被删除。但如果该列被另一张表的外部键约束引用，系统将不会静默地删除该约束，可以使用CASCADE来授权删除所有依赖于该列的内容：

ALTER TABLE products DROP COLUMN description CASCADE;

重命名列

使用RENAME COLUME colname TO newname子命令重命名列名：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

使用RENAME TO子命令重命名表名：

ALTER TABLE products RENAME TO products2;

添加约束

使用ADD ...子命令向表中添加表约束，例如：

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

添加非空约束 (不能写为表约束)，使用：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

约束将立即被检查，因此表数据必须满足约束才能被添加。

删除约束

删除约束时需要知道其名称。如果已经为它命名，那就很容易，否则系统会分配一个生成名称，需要手动找出它。元命令\d tablename在这里可能会有帮助。然后使用DROP CONSTRAINT consname子命令删除它：

ALTER TABLE products DROP CONSTRAINT some_name;

注意，如$2这样生成的约束名称，需要使用双引号扩起来使其成为一个有效的标识符。

与删除列一样，如果要删除其他内容所依赖的约束，需要添加CASCADE。

因为非空约束没有名称，因此删除非空约束需使用ALTER COLUMN colname DROP NOT NULL子命令：

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

修改列默认值

要为列设置新的默认值，使用ALTER COLUMN colname SET DEFAULT ...子命令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意，这不会影响表中的任何现有行，它只会更改未来INSERT命令的默认值。

要删除默认值，使用ALTER COLUMN colname DROP DEFAULT子命令：

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这实际上与将默认值设置为 NULL 相同。

修改列数据类型

要将列转换为不同的数据类型，使用ALTER COLUMN colname TYPE ...子命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10, 2);

只有当列中的每个现有条目都可以通过隐式转换转换为新类型时，此操作才成功。如果需要更复杂的转换，需要添加一个USING子句，该子句指定如何从旧值计算新值。

PostgreSQL 将尝试将列的默认值 (如果有) 转换为新类型，以及涉及该列的任何约束。但这些转换可能会失败，或者可能会产生令人惊讶的结果。在更改列的类型之前，通常最好删除列上的任何约束，然后在之后添加适当修改的约束。

删除表

使用 DROP TABLE 语句删除一个表：

DROP TABLE products;

删除一个不存在的表会引发错误，如果不确定表是否存在，使用：

DROP TABLE IF EXISTS products;

数据类型

PostgreSQL 内置了相当多的数据类型，也支持用户自定义类型。

数值类型

数值类型包括 2 字节、4 字节和 8 字节整数，4 字节和 8 字节浮点数以及可选精度小数。

名称	字节	说明	范围
smallint	2	小范围整数	-32768 至 +32767
integer	4	整数的典型选择	-2147483648 至 +2147483647
bigint	8	大范围整数	-9223372036854775808 至 +9223372036854775807
decimal	可变	用户指定的精度，精确	小数点前最多 131072 位数字，小数点后最多 16383 位数字
numeric	可变	用户指定的精度，精确	小数点前最多 131072 位数字，小数点后最多 16383 位数字
real	4	可变精度，不精确	6 位小数精度
double precision	8	可变精度，不精确	15 位小数精度
smallserial	2	小自增整数	1 至 32767
serial	4	自增整数	1 至 2147483647
bigserial	8	大自增整数	1 至 9223372036854775807

整数类型

类型 smallint，integer 和 bigint 存储不同范围的整数。如果试图存储超出允许范围的值，将导致错误。

integer 类型是最常见的选择，因为它在范围、存储大小和性能之间实现了最佳平衡。一般只有在磁盘空间有限的情况下才使用 smallint 类型。bigint 类型设计用于 integer 类型的范围不足时。

任意精度数

numeric 类型可以存储位数非常多的数字。特别适合用于存储货币金额和其他要求精确的数量。使用 numeric 值进行的计算 (如加法、减法和乘法) 会产生精确的结果。不过，与整数类型和浮点类型相比，numeric 值的计算速度非常慢。

numeric 的精度是整个数值中有效数字的总数，即小数点两侧的数字数。numeric 的刻度是小数部分中数字的数量，即小数点右侧的计数。因此，数字 23.5141 的精度为 6，刻度为 4。整数可以被认为具有零刻度。

numeric 的最大精度和最大刻度都可以配置，语法为：

NUMERIC(precision, scale)

精度必须为正数，而刻度可以为正数或负。如果不填写刻度NUMERIC(precision)，则选择刻度为 0。如果不填写精度和刻度NUMERIC，则会创建一个无约束的 numeric 列，其中可以存储任何长度的数字值 (不超过上表限制)。这种类型的列不会将输入值强制转换为任何特定刻度，而具有声明刻度的 numeric 列会将输入值强制转换为该刻度。

注意：在 numeric 类型声明中可以明确指定的最大精度为 1000。无约束 numeric 列则受上表限制。

如果要存储的值的刻度大于列的声明刻度，系统会将该值舍入到指定的小数位数。如果小数点左侧的位数超过声明精度减去声明刻度，则会引发错误。例如，声明为NUMERIC(3, 1)的列会将值舍入到 1 位小数，并且可以存储 -99.9 到 99.9 (包括 -99.9 和 99.9) 之间的值。

从 PostgreSQL 15 开始，允许刻度为负数，值会舍入到小数点左侧，精度仍然表示未舍入的数字的最大数量。因此，声明为NUMERIC(2, -3)的列会将值舍入到最接近的千位，并且可以存储 -99000 到 99000 (包括 -99000 和 99000) 之间的值。还允许刻度大于精度，此类列只能保存小数值，并且小数点右侧的零位数至少为声明标度减去声明精度。因此，声明为NUMERIC(3, 5)的列会将值舍入到 5 位小数，并且可以存储 -0.00999 到 0.00999 (包括 -0.00999 和 0.00999) 之间的值。

numeric 数值以物理方式存储，没有任何额外的前导或尾随零。因此，列的声明精度和刻度是最大值，而不是固定分配 (从这个意义上说，numeric 类型更类似于 varchar(n) 而不是 char(n))。实际存储要求是每组四个十进制数字两个字节，加上三到八个字节的开销。

除了普通数值外，numeric 类型还有几个特殊值：Infinity、-Infinity和NaN。

这些值改编自 IEEE 754 标准，分别表示无穷大、负无穷大和非数字。在 SQL 命令中将这些值写为常量时，必须用引号将它们引起来，例如UPDATE table SET x = '-Infinity'。在输入时，这些字符串以不区分大小写的方式识别。正负无穷大值也可以拼写为inf和-inf。

无穷大值的行为符合数学预期。例如，Infinity 加上任何有限值等于 Infinity，Infinity 加上 Infinity 也等于 Infinity。但 Infinity 减去 Infinity 等于 NaN，因为它没有明确的解释。此外，无穷大只能存储在不受约束的 numeric 列中，因为它在概念上超过了任何有限精度限制。

NaN 值用于表示未定义的计算结果。通常，任何带有 NaN 输入的操作都会产生另一个 NaN。唯一的例外是当操作的其他输入使得如果用任何有限或无限数字值替换 NaN，将获得相同的输出，然后，该输出值也用于 NaN。在大多数 NaN 的实现中，NaN 不等于其他任何值 (包括 NaN)。为了允许对 numeric 值进行排序并在基于树的索引中使用它们，PostgreSQL 将 NaN 值视为相等，并且大于所有非 NaN 值。

在舍入值时，numeric 类型将舍入值舍入到远离零，而在大多数计算机上 real 和 double precision 类型值被舍入到最接近的偶数。

浮点类型

数据类型 real 和 double precision 是不精确的、可变精度的数字类型。在当前支持的所有平台上，这些类型都是 IEEE 标准 754 二进制浮点算术 (分别为单精度和双精度) 的实现。不精确意味着某些值无法精确转换为内部格式，并且存储为近似值，因此存储和检索值可能会出现细微差异。

除了普通数字值之外，浮点类型还有几个特殊值：Infinity、-Infinity和NaN。

类似上面，不过它们的含义是 IEEE 754 中所规定的无穷大，负无穷大和非数字。正负无穷大常量也可以写成inf和-inf。此外，IEEE 754 规定 NaN 不应与任何其他浮点值 (包括 NaN) 相等。但为了允许对浮点值进行排序并在基于树的索引中使用它们，PostgreSQL 将 NaN 值视为相等，并且大于所有非 NaN 值。

PostgreSQL 还支持 SQL 标准符号float和float(p)来指定不精确的数字类型。在此，p 指定以二进制数字表示的最小可接受精度。PostgreSQL 接受 float(1) 到 float(24) 来选择 real 类型，而 float(25) 到 float(53) 选择 double precision。超出允许范围的 p 值会引发错误。float 未指定精度时，表示 double precision。

序列类型

序列类型 smallserial、serial 和 bigserial 不是真正的类型，而只是创建唯一标识符列的一种符号便利 (类似于某些其他数据库支持的AUTO_INCREMENT属性)。下列建表语句是等效的：

CREATE TABLE tablename (
    colname SERIAL
);

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

由于 smallserial、serial 和 bigserial 是使用序列实现的，因此即使未删除任何行，列中出现的数值序列中也可能存在空洞或间隙。即使包含该值的行的从未成功插入到表列中，从序列分配的值仍然会被用完。

当 serial 列被删除时，系统为其生成的序列也会被关联删除。可以在不删除 serial 列的情况下删除序列，但这会强制删除 serial 列的默认表达式。

字符类型

SQL 定义了两种主要字符类型：character varying(n) 和 character(n)，其中 n 是正整数。这两种类型都可以存储长度最长为 n 个字符 (不是字节) 的字符串。尝试将更长的字符串存储到这些类型的列中会导致错误 (除非多余的字符都是空格，在这种情况下，字符串将被截断为最大长度)。但如果将值显式强制转换为字符串值，则超长值将被截断为 n 个字符，而不会引发错误。如果要存储的字符串短于声明的长度，character 类型将用空格填充，character varying 类型则直接存储。

名称	描述
character varying(n), varchar(n)	可变长度，有长度限制
character(n), char(n), bpchar(n)	固定长度，空格填充
bpchar	可变长度，无限制，空格修剪
text	可变长度，无限制

char(n) 和 bpchar(n) 是 character(n) 的类型别名，varchar(n) 是 character varying(n) 的类型别名，单独的 varchar 和 bpchar 都是无长度限制的，而单独的 character 含义是 character(1)。text 类型是 PostgreSQL 提供的原生字符串类型，大多数对字符串进行操作的内置函数都声明为获取或返回 text，而不是 character varying。

可以在这些数据类型中存储的字符由数据库字符集决定，该字符集在创建数据库时选择。无论具体字符集如何，都不能存储代码为零的字符 (NUL 字符）。

除使用空白填充类型时存储空间增加以及在存储到长度受限的列时检查长度需要一些额外的 CPU 周期外，这三种类型之间没有性能差异。虽然在一些其他数据库系统中 character(n) 具有性能优势，但在 PostgreSQL 中没有这种优势。事实上 character(n) 通常是三种类型中最慢的，因为它的存储成本较高。在大多数情况下，应改用 text 或 character varying。

日期时间类型

PostgreSQL 支持全套的 SQL 日期和时间类型：

名称	字节	说明	低值	高值	精度
timestamp [(p)] [without time zone]	8	日期和时间，无时区	公元前 4713 年	公元 294276 年	1 微秒
timestamp [(p)] with time zone	8	日期和时间，有时区	公元前 4713 年	公元 294276 年	1 微秒
date	4	日期，无时间	公元前 4713 年	公元 5874897 年	1 天
time [(p)] [without time zone]	8	时间，无日期，无时区	00:00:00	24:00:00	1 微秒
time [(p)] with time zone	12	时间，无日期，有时区	00:00:00+1559	24:00:00-1559	1 微秒
interval [fields] [(p)]	16	时间间隔	-178000000 年	178000000 年	1 微秒

单独的 timestamp 和 time 类型不带时区，如需时区限制，则手动指定 with time zone 选项。可以为 time、timestamp 和 interval 类型指定一个精度 p，该值指定时间中秒数的保留的小数位数。p 值应该在 0 到 6 之间，如不指定，默认为 6 (即微妙精度)。

interval 类型有一个附加选项 fields，它可以为以下短语：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

注意，如果同时指定 fields 和 p，则 fields 必须包括SECOND，因为精度仅适用于秒。

在大多数情况下，使用 date、time、timestamp 和 timestamp with time zone 足以满足程序对日期时间的任何需求，time with time zone 类型没有实际价值。

输入格式

几乎所有合理格式都可以作为日期和时间的输入，包括 ISO 8601、SQL 兼容、传统的 POSTGRES 等。PostgreSQL 在处理日期时间输入方面比 SQL 标准要求的更灵活。注意，任何日期或时间文字输入都需要用单引号括起来，就像文本字符串一样。

除了可以直接使用字符串作为输入外，也可以使用 SQL 标准格式：

type [(p)] 'value'

type 可以是 timestamp, interval 或 time，p 指定精度，在 0 到 6 之间。

日期

推荐的输入格式是 ISO 6801，例如：'2004-01-20'。

或者使用 SQL 标准格式：DATE '2004-01-20'。

时间

推荐的输入格式是 ISO 6801，例如：'04:05:06.789'。

或者使用 SQL 标准格式：TIME '04:05:06.789'。

时间类型可以附加一个时区 (如果列类型不带时区将被忽略)：'04:05:06.789+08' (UTC 偏移)。

时间戳

推荐的输入格式是 ISO 6801，例如：'2004-01-20 04:05:06.789'。

或者使用 SQL 标准格式：TIMESTAMP '2004-01-20 04:05:06.789'。

可以附加一个时区 (如果列类型不带时区将被忽略)：'2004-01-20 04:05:06.789+08' (UTC 偏移)。

特殊值

PostgreSQL 支持将以下多种特殊日期时间作为输入值以方便使用。值infinity和-infinity在系统内部以特殊方式表示，并且将保持不变地显示，但其他值只是简单的符号缩写，在读取时将转换为普通日期时间值。所有这些值在作为 SQL 命令中的常量使用时需要用单引号引起来。

输入字符串	有效类型	说明
epoch	date、timestamp	1970-01-01 00:00:00+00 (Unix 系统时间零)
infinity	date、timestamp	晚于所有其他时间戳
-infinity	date、timestamp	早于所有其他时间戳
now	date、time、timestamp	当前事务的开始时间
today	date、timestamp	今天的午夜 (00:00)
tomorrow	date、timestamp	明天的午夜 (00:00)
yesterday	date、timestamp	昨天的午夜 (00:00)
allballs	time	00:00:00.00 UTC

以下 SQL 兼容函数也可用于获取相应数据类型的当前时间值：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP。注意，这些是 SQL 函数，在数据输入字符串中不被识别。

输出格式

日期时间类型的输出格式可以为以下四种之一：ISO 8601、SQL (Ingres)、POSTGRES (Unix Date) 或德语格式。默认格式为 ISO 格式。date 和 time 类型的输出通常仅为日期或时间部分。但是，POSTGRES 样式以 ISO 格式输出仅日期值。

在 ISO 格式中，时区始终显示为相对 UTC 的带符号数字偏移量。如果偏移量是整数小时数，则显示为 hh (仅小时)，如果偏移量是整数分钟数，则显示为 hh:mm，否则显示为 hh:mm:ss。第三种情况在任何现代时区标准中都不可能出现，但它可能出现在处理早于采用标准化时区的的时间戳时。在其他日期样式中，如果时区在当前时区中常用，则时区显示为字母缩写。否则，它以 ISO 8601 基本格式 (hh 或hh:mm) 显示为带符号数字偏移量。

用户可以使用SET datestyle命令，或修改 postgresql.conf 配置文件中的 DateStyle 参数，或设置服务器或客户端上的PGDATESTYLE环境变量来选择日期时间样式。

格式化函数to_char也可作为一种更灵活的方式来格式化日期时间输出。