postgresql 快速入门-4

概述

本文基于pg 官方文档第4章“SQL语法"，归纳总结相应的知识点。主要包括：词法结构、值表达式、调用函数。

词法结构

标识符&关键词

• 虽然pg的标识符支持美元符号$（不能为首字母），但建议不要用，因为不符合SQL标准。若使用会降低可移植性。
• 标识符的长度不能超过 NAMEDATALEN-1 字节；默认情况下，NAMEDATALEN 的值为64。可以在src/include/pg_config_manual.h中修改 NAMEDATALEN 常量。
• unicode转义字符的示例：U&"d\0061t\+000061"。

  U&"d\0061t\+000061"  --4位16进制代码点,加号接上6位16进制代码点
  U&"d!0061t!+000061" UESCAPE '!' --使用其他转义字符来代替反斜线

•  Unicode转义语法只有在服务器编码为UTF8时才起效。当使用其他服务器编码时，只有在ASCII范围内（最高到\007F）的编码点才能被使用。
• pg将非受限名称总是被转换成小写形式。这点刚好和SQL标准的要求相反，SQL标准要求将非受限名称转换为大写形式。

常量

• 字符串常量是单引号包围，不是双引号。
• unicode转义的字符串常量，和标识符的unicode转义用法和规则相同。
• 字符串常量若要包含单引号'，有两种写法：'',或者\'。推荐使用''写法。原因请参考：backslash_quote
• 注意standard_conforming_strings会影响pg解析文本的行为。

注：

1，此章节中有这样一句话："4位和6位形式都可以被用来定义UTF-16代理对来组成代码点大于U+FFFF的字符"，有2处说的是6位，而有1处说的是8位（英文版）。感觉有点费解。为什么是8位？（个人感觉应该是6位）。查看了pg 12的文档，翻译并无问题，原文的确写的是8-digit（如下截图）；再查看当前最新版13的文档，已无这一段文字。

另外发现，12的中文版文档有两处写的是8位，其中一处原文是6位。不知道是翻译时笔误，还是被翻译人员特意修改的。

2，关于unicode与utf-8/16/32，可参考：https://www.cnblogs.com/icooper/p/4583684.html，https://blog.csdn.net/hongsong673150343/article/details/88584753，关于utf-16代理对，可参考：https://blog.csdn.net/htxhtx123/article/details/104569063等。

3，希望使用反斜线作为转义符的应用，应该被修改来使用转义字符串语法（E'...'），因为在 SQL 标准中普通字符串的默认行为是将反斜线视作一个普通字符。这个行为和许多程序语言的设计是反过来的。这些程序语言默认是将反斜线视为转义字符；而如果要视为普通字符，加上语言自定义的前缀，例如rust的r'....'。与此对应，pg也提供了美元符号引号的字符串常量语法（非SQL标准）。

• 美元符号引用的字符串常量语法示例：

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

• 位串常量两种指定方法：B'1001' 或者X'1FF'
• 数字常量：无小数点或指数时，猜测顺序：integer->bigint->numeric；有小数点或指数时：numeric；
• 指定任意类型的常量示例如下图。其中，要避免语法歧义，type 'string'语法只能被用来指定简单文字常量的类型。type 'string'语法上的另一个限制是它无法对数组类型工作，指定一个数组常量的类型可使用::或CAST()。

type 'string'   --有所扩展。SQL标准指定这种语法只用于一些数据类型，但是PostgreSQL允许它用于所有类型
'string'::type  --PG 历史用法
CAST ( 'string' AS type ) --遵循SQL 标准

操作符

• 操作符名的字符不能任意，只能来自以下：

+ - * / < > = ~ ! @ # % ^ & | ` ?

• 操作符名有一些限制。这些限制请参考： 4.1.3. 操作符

注：特殊字符、注释、操作符优先级内容，直接参考文档便可。本文略过。

值表达式

• 位置参数的示例：

CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;

• 域选择主要和组合类型相关。域选择时，一般来说要把表达式加上括号，除非当该表达式是仅从一个表引用或位置参数选择时，圆括号可以被忽略。如下示例：

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

• 聚集表达式。

pg提供了4类聚集函数：通用类聚集函数、统计类聚集函数、组内有序集聚集函数、组内假想集聚集函数。请参考9.20. 聚集函数。下面是3类聚集函数的列表（不含统计类）：

注：

1，上表中的bit_and，换个说法更易理解：所有非null值的按位与。如果全部都是null值，则返回null。bit_or也是类似。

2，pg的聚集函数有个行为要注意：除了count以外，其他聚集函数函数在没有行被选中时返回空值。尤其是sum函数在没有输入行时返回空值，而不是零。array_agg在这种情况返回空值而不是一个空数组。

• 窗口调用函数

同前文，这里列出窗口函数的文档链接：3.5 窗口函数 ，4.2.8. 窗口函数调用，9.21. 窗口函数，7.2.5. 窗口函数处理，select中的WINDOW 子句。

注：

对sql开发人员而言，聚集函数和窗口函数比较常用。需要理解和熟练运用。

• 类型转换

语法如下：

CAST ( expression AS type )
expression::type

• 排序规则表达式

参考阅读：

PostgreSQL 字符串 collate 与排序 源码分析

设置PostgreSQL排序规则（Collation）

• 标量子查询、数组构造器、行构造器：文档比较好理解，这里不再重复罗列。
• 表达式计算规则

注

1，pg的表达式计算也会有短路规则；但是和一些编程语言中布尔操作符从左至右的“短路”不同。

2，实际开发中，要确保计算顺序，可以考虑CASE 结构子句；要防止出现除零等异常。

调用函数

带参数的函数，在调用时如何指定各个参数的值？pg的做法和许多编程语言类似：命名参数、位置参数、（也可混合使用）。这里不展开。直接看文档4.3. 调用函数便可。