Postgresql 中文操作指南

5.2. Default Values #

可以为列分配默认值。当创建新行且未为某些列指定值时，这些列将填充各自的默认值。数据操作命令还可以明确请求将列设置为其默认值，而无需知道该值是什么。（有关数据操作命令的详细信息，请参见 Chapter 6）。

A column can be assigned a default value. When a new row is created and no values are specified for some of the columns, those columns will be filled with their respective default values. A data manipulation command can also request explicitly that a column be set to its default value, without having to know what that value is. (Details about data manipulation commands are in Chapter 6.)

如果未显式声明默认值，则默认值为 Null 值。这通常是有意义的，因为 Null 值可以被视为表示未知数据。

If no default value is declared explicitly, the default value is the null value. This usually makes sense because a null value can be considered to represent unknown data.

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

In a table definition, default values are listed after the column data type. For example:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

默认值可以是表达式，该表达式将在插入默认值时进行评估（创建表时）。一个常见的示例是让 timestamp 列的默认值为 CURRENT_TIMESTAMP，以便在插入行时将其设置为插入时间。另一个常见的示例是为每行生成“序列号”。在 PostgreSQL 中，通常通过类似以下方式来实现：

The default value can be an expression, which will be evaluated whenever the default value is inserted (not when the table is created). A common example is for a timestamp column to have a default of CURRENT_TIMESTAMP, so that it gets set to the time of row insertion. Another common example is generating a “serial number” for each row. In PostgreSQL this is typically done by something like:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

其中 nextval() 函数从 sequence object 提供连续值（参见 Section 9.17）。该结构设置十分常见，因此为此配备了专门的简写方式：

where the nextval() function supplies successive values from a sequence object (see Section 9.17). This arrangement is sufficiently common that there’s a special shorthand for it:

CREATE TABLE products (
    product_no SERIAL,
    ...
);

有关 SERIAL 简写的进一步讨论，请参见 Section 8.1.4。

The SERIAL shorthand is discussed further in Section 8.1.4.