Postgresql 中文操作指南

10.1. Overview #

SQL 是一种强类型的语言。也就是说，每个数据项都有一个关联的数据类型，该类型决定了该数据项的行为和允许的用法。PostgreSQL 具有一个可扩展类型系统，该类型系统比其他 SQL 实现更为通用和灵活。因此，PostgreSQL 中的大多数类型转换行为由常规规则（而不是临时启发式）控制。这允许使用混合类型表达式，即使与用户定义类型一起使用也是如此。

SQL is a strongly typed language. That is, every data item has an associated data type which determines its behavior and allowed usage. PostgreSQL has an extensible type system that is more general and flexible than other SQL implementations. Hence, most type conversion behavior in PostgreSQL is governed by general rules rather than by ad hoc heuristics. This allows the use of mixed-type expressions even with user-defined types.

PostgreSQL 扫描器/解析器将词法元素分为五种基本类别：整数、非整数、字符串、标识符和关键字。大多数非数字类型常量首先被归类为字符串。SQL 语言定义允许使用字符串指定类型名称，并且这种机制可以在 PostgreSQL 中用于让解析器沿着正确路径启动。例如，查询：

The PostgreSQL scanner/parser divides lexical elements into five fundamental categories: integers, non-integer numbers, strings, identifiers, and key words. Constants of most non-numeric types are first classified as strings. The SQL language definition allows specifying type names with strings, and this mechanism can be used in PostgreSQL to start the parser down the correct path. For example, the query:

SELECT text 'Origin' AS "label", point '(0,0)' AS "value";

 label  | value
--------+-------
 Origin | (0,0)
(1 row)

具有两个类型为 text 和 point 的文字常量。如果不为字符串文字指定类型，则最初分配占位符类型 unknown，以便在稍后的阶段根据以下描述进行解析。

has two literal constants, of type text and point. If a type is not specified for a string literal, then the placeholder type unknown is assigned initially, to be resolved in later stages as described below.

有四种基本 SQL 构造需要在 PostgreSQL 解析器中使用不同的类型转换规则：

There are four fundamental SQL constructs requiring distinct type conversion rules in the PostgreSQL parser:

  • Function calls

    • Much of the PostgreSQL type system is built around a rich set of functions. Functions can have one or more arguments. Since PostgreSQL permits function overloading, the function name alone does not uniquely identify the function to be called; the parser must select the right function based on the data types of the supplied arguments.

  • Operators

    • PostgreSQL allows expressions with prefix (one-argument) operators, as well as infix (two-argument) operators. Like functions, operators can be overloaded, so the same problem of selecting the right operator exists.

  • Value Storage

    • SQL INSERT and UPDATE statements place the results of expressions into a table. The expressions in the statement must be matched up with, and perhaps converted to, the types of the target columns.

  • UNION, CASE, and related constructs

    • Since all query results from a unionized SELECT statement must appear in a single set of columns, the types of the results of each SELECT clause must be matched up and converted to a uniform set. Similarly, the result expressions of a CASE construct must be converted to a common type so that the CASE expression as a whole has a known output type. Some other constructs, such as ARRAY[] and the GREATEST and LEAST functions, likewise require determination of a common type for several subexpressions.

系统目录会存储有关哪些转换或 casts 存在于哪些数据类型之间以及如何执行这些转换的信息。用户可以使用 CREATE CAST 命令添加其他转换。（这通常通过定义新的数据类型来完成。内置类型之间的转换集经过精心设计，最好不要进行更改。）

The system catalogs store information about which conversions, or casts, exist between which data types, and how to perform those conversions. Additional casts can be added by the user with the CREATE CAST command. (This is usually done in conjunction with defining new data types. The set of casts between built-in types has been carefully crafted and is best not altered.)

解析器提供的附加启发式方法允许在具有隐式转换的类型组之间更好地确定适当的类型转换行为。数据类型分为若干基本 type categories，包括 boolean、numeric、string、bitstring、datetime、timespan、geometric、network 和用户定义的类型。（有关列表，请参阅 Table 53.65；但请注意也可以创建自义类型类别。）每个类别中可能存在一个或多个 preferred types，当有多种可能的类型可供选择时，这些类型会被优先选择。通过精心选择首选类型和可用的隐式转换，可以确保用有用的方式解决模棱两可的表达式（具有多个备选解析解决方案的表达式）。

An additional heuristic provided by the parser allows improved determination of the proper casting behavior among groups of types that have implicit casts. Data types are divided into several basic type categories, including boolean, numeric, string, bitstring, datetime, timespan, geometric, network, and user-defined. (For a list see Table 53.65; but note it is also possible to create custom type categories.) Within each category there can be one or more preferred types, which are preferred when there is a choice of possible types. With careful selection of preferred types and available implicit casts, it is possible to ensure that ambiguous expressions (those with multiple candidate parsing solutions) can be resolved in a useful way.

设计所有类型转换规则时都考虑了以下几个原则：

All type conversion rules are designed with several principles in mind: