Postgresql13笔记

我把Postgresql二刷了一遍，以前的东西，比较粗，因此还需要更新，预计2月22日搞定（最近项目忙加上项目恶心，心情不好，耽误了）。

1.基础概念

1.1架构基础

在数据库术语里，PostgreSQL使用一种客户端/服务器的模型。一次PostgreSQL会话两部分组成。

• 一个服务器进程，它管理数据库文件、接受来自客户端应用与数据库的联接并且代表客户端在数据库上执行操作。 该数据库服务器程序叫做postgres。
• 客户端应用可能本身就是多种多样的

PostgreSQL服务器可以处理来自客户端的多个并发请求。 因此，它为每个连接启动（“forks”）一个新的进程。 从这个时候开始，客户端和新服务器进程就不再经过最初的 postgres进程的干涉进行通讯。 因此，主服务器进程总是在运行并等待着客户端联接， 而客户端和相关联的服务器进程则是起起停停（当然，这些对用户是透明的。)

PostgreSQL 使用 fork() 模型在一定程度上提高了进程的隔离性和并发性，但也带来了较大的资源消耗和管理开销。因此，对于小型到中型负载的应用，这种设计非常合适，但在超大并发负载下，可能会遇到性能瓶颈，特别是在内存和系统资源方面

1.2psql

运行PostgreSQL的交互式终端程序，它被称为psql， 它允许你交互地输入、编辑和执行SQL命令。

你可能需要启动psql来试验本教程中的例子。 你可以用下面的命令为mydb数据库激活它：
**psql mydb 。**如果你不提供数据库名字，默认使用操作系统当前用户账号名字。在前面使用createdb的小节里你应该已经了解了这种方式。

在psql中，你将看到下面的欢迎信息：

psql (13.1)
Type "help" for help.

mydb=>

最后一行也可能是：

mydb=#

这个提示符意味着你是数据库超级用户，最可能出现在你自己安装了 PostgreSQL实例的情况下。 作为超级用户意味着你不受访问控制的限制。

• \h : 获取各种PostgreSQL的SQL命令的帮助语法**
• \q :退出psql

1.3注意事项

• SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写
• Postgresql不存在短路与和短路或的情况

1.4java连接postgre

1.安装postgre数据库
2.引入依赖

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.2</version>
</dependency>

3.修改数据库连接配置

pring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: org.postgresql.Driver
        druid:
            # 主库数据源
            master:
                url: jdbc:postgresql://dockeros:15432/gulimall_admin?useSSL=false&stringtype=unspecified
                username: postgres
                password: 123456

1.5词法结构

SQL输入由一个命令序列组成。一个命令由一个记号的序列构成，并由一个分号（“;”）终结。

1.5.1标识符和关键字

下例中的SELECT、UPDATE或VALUES记号是关键词的例子，即SQL语言中具有特定意义的词。记号MY_TABLE和A则是标识符的例子。它们标识表、列或者其他数据库对象的名字，取决于使用它们的命令。因此它们有时也被简称为“名字”。键词和标识符具有相同的词法结构，这意味着我们无法在没有语言知识的前提下区分一个标识符和关键词。

SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');

系统中一个标识符的长度不能超过 NAMEDATALEN-1 字节，在命令中可以写超过此长度的标识符，但是它们会被截断。默认情况下，NAMEDATALEN 的值为64，因此标识符的长度上限为63字节。如果这个限制有问题，可以在src/include/pg_config_manual.h中修改 NAMEDATALEN 常量。

SQL标识符和关键词必须以一个字母（a-z，也可以是带变音符的字母和非拉丁字母）或一个下划线（_）开始。后续字符可以是字母、下划线（_）、数字（0-9）或美元符号（$）。注意根据SQL标准的字母规定，美元符号是不允许出现在标识符中的，因此它们的使用可能会降低应用的可移植性。SQL标准不会定义包含数字或者以下划线开头或结尾的关键词，因此这种形式的标识符不会与未来可能的标准扩展冲突 。

受限标识符或*被引号修饰的标识符:*它是由双引号（"）包围的一个任意字符序列。一个受限标识符总是一个标识符而不会是一个关键字。因此"select"可以用于引用一个名为“select”的列或者表，而一个没有引号修饰的select则会被当作一个关键词，从而在本应使用表或列名的地方引起解析错误。未被引号修饰的标识符总是折叠成小写

一种受限标识符的变体允许包括转义的用代码点标识的Unicode字符。这种变体以U&（大写或小写U跟上一个花号）开始，后面紧跟双引号修饰的名称。在引号内，Unicode字符可以以转义的形式指定：反斜线接上4位16进制代码点号码或者反斜线和加号接上6位16进制代码点号码。例如，标识符"data"可以写成：

U&"d\0061t\+000061"

如果希望使用其他转义字符来代替反斜线，可以在字符串后使用UESCAPE子句，例如：

U&"d!0061t!+000061" UESCAPE '!'

1.5.2常量

• 字符串常量：一个字符串常量是一个由单引号（'）包围的任意字符序列，为了在一个字符串中包括一个单引号，可以写两个相连的单引号，例如'Dianne''s horse'，两个只由空白及至少一个新行分隔的字符串常量会被连接在一起，并且将作为一个写在一起的字符串常量来对待。例如：

SELECT 'foo'
'bar';

等同于：

SELECT 'foobar';

需要注意的是：
1.**美元引用的字符串常量：**虽然用于指定字符串常量的标准语法通常都很方便，但是当字符串中包含了很多单引号或反斜线时很难理解它，因为每一个都需要被双写。要在这种情形下允许可读性更好的查询，PostgreSQL提供了另一种被称为“美元引用”的方式来书写字符串常量。一个美元引用的字符串常量由一个美元符号（$）、一个可选的另个或更多字符的“标签”、另一个美元符号、一个构成字符串内容的任意字符序列、一个美元符号、开始这个美元引用的相同标签和一个美元符号组成，语法是：$标签$常量$标签$，标签可以为空，但是需要注意的是标签是大小写敏感的。例如，这里有两种不同的方法使用美元引用指定字符串“Dianne’s horse”：$$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$

• 数字常量：如果一个不包含小数点和指数的数字常量的值适合类型integer（32 位），它首先被假定为类型integer。否则如果它的值适合类型bigint（64 位），它被假定为类型bigint。再否则它会被取做类型numeric。包含小数点和/或指数的常量总是首先被假定为类型numeric。一个数字常量初始指派的数据类型只是类型转换算法的一个开始点。在大部分情况中，常量将被根据上下文自动被强制到最合适的类型。必要时，你可以通过造型它来强制一个数字值被解释为一种指定数据类型。例如，你可以这样强制一个数字值被当做类型real（float4）：

REAL '1.23'  -- string style
1.23::REAL   -- PostgreSQL (historical) style

1.5.3注释

一段注释是以双横杠开始并且延伸到行结尾的一个字符序列，例如：

-- This is a standard SQL comment

另外，也可以使用 C 风格注释块：

/* multiline comment
 * with nesting: /* nested block comment */
 */

这里该注释开始于/*并且延伸到匹配出现的*/。这些注释块可按照 SQL 标准中指定的方式嵌套，但和 C 中不同。这样我们可以注释掉一大段可能包含注释块的代码。

在进一步的语法分析前，注释会被从输入流中被移除并且实际被替换为空白。

1.5.4位置参数

和linux的位置参数一样，用来指示一个由 SQL 语句外部提供的值。如下这里$1引用函数被调用时第一个函数参数的值。

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

1.6聚合函数

聚合函数就是将多个输入减少到一个单一输入的值。常用的语法如下:

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

总结下来就是只有两种：

aggregate_name(ALL/DISTINCT/空 expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

需要注意的是：

• aggregate_name值得是聚合函数的名称，例如max，string_agg，mode等。
• expression [ , … ]指的是表达式，例如：max(s.number)中的s.number,string_agg(oi.name,‘,’)中的oi.name,‘,’。
• order_by_clause:某些聚集函数（例如array_agg 和string_agg）依据输入行的排序产生结果。当使用这类聚集时，可选的order_by_clause可以被用来指定想要的顺序。rder_by_clause与查询级别的ORDER BY子句具有相同的语法
• WITHIN GROUP ( order_by_clause ):WITHIN GROUP 是与某些有序聚合函数一起使用的，它允许你在执行聚合时对结果进行排序。特别是对于像 percentile_cont() 和 mode() 这样的有序聚合函数，你必须指定 ORDER BY 来定义计算的顺序。

-- 写法
SELECT mode() WITHIN GROUP (ORDER BY object_id DESC) FROM object_instance 5
-- 写法1
SELECT string_agg(oi.instance_number, ',' ORDER BY oi.object_id) FROM object_instance oi WHERE oi.del_flag = '1' GROUP BY oi.object_id 
-- 写法3
SELECT count(*) FROM object_instance WHERE del_flag = '1' 

1.7窗口函数

窗口函数是在一个未分组的查询中应用一个聚合函数。特别是用在想要输出未分组的字段和分组处理的字段的场景下，常用语法如下:

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

1.7.1 window_definition

window_definition的语法是：

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

介绍一下这些都是什么意思

• existing_window_name:定义的窗口名称，就是将窗口的定义单独拎出来，然后用别名代替，而这个别名就是existing_window_name，如下SQL1和SQL2是等价的，existing_window_name常用在需要两个相同窗口定义的时候，如SQL3的情况：

--SQL1
SELECT oi.instance_number, RANK() OVER(PARTITION BY oi.object_id ORDER >BY oi.create_time) AS r
FROM object_instance oi
WHERE oi.del_flag = '1'
--SQL2
SELECT oi.instance_number, RANK() OVER(wys) AS r
FROM object_instance oi
WHERE oi.del_flag = '1'
WINDOW wys AS (PARTITION BY oi.object_id ORDER BY oi.create_time)
--SQL3
SELECT oi.instance_number, RANK() OVER(wys) AS r1, ROW_NUMBER() >OVER(wys) AS r2
FROM object_instance oi
WHERE oi.del_flag = '1'
WINDOW wys AS (PARTITION BY oi.object_id ORDER BY oi.create_time)

• PARTITION BY:根据表达式expression或者字段分区
• ORDER BY:根据表达式expression对分区内的数据排序
• frame_clause：指定构成窗口的行数据的范围，语法如下:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
RANGE：按值的大小来定义窗口范围，比如当前行及其附近值的行
ROWS：按行数来定义窗口范围，比如当前行前后多少行
GROUPS：当使用 GROUPS 时，窗口将按照排序值相同的行进行分组。这意味着，如果两行的排序值相同，它们会被视为一个组，窗口函数会基于整个组来进行计算，而不是仅仅按行数或按值的范围。

中frame_start和frame_end可以是下面形式中的一种

offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

看起来很复杂，但是在我的经验中，用的最多的就是：

窗口函数 OVER(PARTITION BY 字段1 ORDER BY 字段2)

其中字段1就是起分区的作用，也就是在未分组的查询上加上分组的效果，这样才能使用聚合函数，通俗的讲就是一个高级的GROUP。而字段2是对分区内的数据进行排序。

1.7.2FILTER

过滤，类似于where条件，只不过只能用在聚合函数上，不能用在专用窗口函数上（RANK 、 DENSE_ RANK 、 ROW_ NUMBER）

-- 正确（使用的聚合函数）
SELECT oi.instance_number, SUM() FILTER (WHERE oi.del_flag = '1') OVER(PARTITION BY oi.object_id ORDER BY oi.create_time) AS r
FROM object_instance oi
WHERE oi.del_flag = '1'
-- 错误（使用的专用窗口函数）
SELECT oi.instance_number, RANK() FILTER (WHERE oi.del_flag = '1') OVER(PARTITION BY oi.object_id ORDER BY oi.create_time) AS r
FROM object_instance oi
WHERE oi.del_flag = '1'

1.7.常用的窗口函数

函数	描述
row_number () → bigint	返回其分区内的当前行数，从1开始计数。
rank () → bigint	返回当前行的排名，包含间隔(意思是，a,b,c三人，a和b第一，则c就是第三)
dense_rank () → bigint	返回当前行的排名，不包括间隔(意思是，a,b,c三人，a和b第一，则c就是二)
percent_rank () → double precision	，用于计算某行在分组中的相对排名百分比。它的返回值范围是 [0, 1]，表示当前行在分组中的相对位置。
lead ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement	返回分区中在当前行之后offset行的value； 如果没有这样的行，则返回default(必须与value具有相同的类型)。 offset和default都是针对当前行求值的。如果省略，offset默认为1，default为NULL。
first_value ( value anyelement ) → anyelement	返回在窗口框架的第一行求得的value。
last_value ( value anyelement ) → anyelement	返回在窗口框架的最后一行求得的value。
nth_value ( value anyelement, n integer ) → anyelement	返回在窗口框架的第n行求得的value(从1开始计数);如果没有这样的行，则返回NULL。

1.8类型转换

• CAST(类型):语法遵从 SQL
• ==::==是PostgreSQL的历史用法。

1.9COLLATE

COLLATE 是 PostgreSQL 中用来指定字符串排序规则的关键字,它可以在查询、创建表或其他字符串操作中使用，允许你根据不同的语言、地区或文化习惯来定义字符串的比较和排序方式。通过 COLLATE，你可以确保在多语言环境中处理字符串时，按照期望的规则进行排序和比较。

-- 在查询时：在 ORDER BY 子句中指定排序规则，控制字符串排序的方式。
SELECT product_name
FROM products
ORDER BY product_name COLLATE "de_DE.UTF-8";

1.10数组构造器

一个数组构造器是一个能构建一个数组值并且将值用于它的成员元素的表达式。一个简单的数组构造器由关键词ARRAY、一个左方括号[、一个用于数组元素值的表达式列表（用逗号分隔）以及最后的一个右方括号]组成。但是用ARRAY构建的一个数组值的下标总是从一开始。例如：

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

多维的如下

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

SELECT ARRAY[[1,2],[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

1.11行构造器

一个行构造器是能够构建一个行值（也称作一个组合类型）并用值作为其成员域的表达式。一个行构造器由关键词ROW、一个左圆括号、用于行的域值的零个或多个表达式（用逗号分隔）以及最后的一个右圆括号组成。例如：

SELECT ROW(1,2.5,'this is a test');

1.12函数中实参和形参的对应关系

Postgresql中有三种方法来对实参和形参进行对应，例如创建如下函数

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

函数concat_lower_or_upper有两个强制参数，a和b。此外，有一个可选的参数uppercase，其默认值为false。a和b输入将被串接，并且根据uppercase参数被强制为大写或小写形式。

1.12.1位置记号法

在PostgreSQL中，位置记号法是给函数传递参数的传统机制。一个例子：

SELECT concat_lower_or_upper('Hello', 'World', true);
concat_lower_or_upper 
-----------------------
HELLO WORLD
(1 row)

所有参数被按照顺序指定。结果是大写形式，因为uppercase被指定为true。

1.12.2使用命名记号

在命名记号法中，每一个参数名都用=> 指定来把它与参数表达式分隔开。例如：

SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
concat_lower_or_upper 
-----------------------
hello world
(1 row)

再次，参数uppercase被忽略，因此它被隐式地设置为false。使用命名记号法的一个>优点是参数可以用任何顺序指定，例如：

SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true);
concat_lower_or_upper 
-----------------------
HELLO WORLD
(1 row)

SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
concat_lower_or_upper 
-----------------------
HELLO WORLD
(1 row)

为了向后兼容性，基于 “:=” 的旧语法仍被支持：

SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
concat_lower_or_upper 
-----------------------
HELLO WORLD
(1 row)

1.12.2使用混合记号

混合记号法组合了位置和命名记号法。不过，正如已经提到过的，命名参数不能超越位置参数。例如：

SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

在上述查询中，参数a和b被以位置指定，而uppercase通过名字指定。在这个例子中，这只增加了一点文档。在一个具有大量带默认值参数的复杂函数中，命名的或混合的记号法可以节省大量的书写并且减少出错的机会。

命名的和混合的调用记号法当前不能在调用聚集函数时使用（但是当聚集函数被用作窗口函数时它们可以被使用）。

2.表结构的操作

2.1创建表

CREATE TABLE 表名 {
	字段1 数据类型,
	字段2 数据类型,
	....
}

例子：

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

2.2删除表

DROP TABLE 表名

例子:

DROP TABLE my_first_table;

尝试移除一个不存在的表会引起错误。然而，在SQL脚本中在创建每个表之前无条件地尝试移除它的做法是很常见的，即使发生错误也会忽略之，因此这样的脚本可以在表存在和不存在时都工作得很好（如果你喜欢，可以使用DROP TABLE IF EXISTS变体来防止出现错误消息，但这并非标准SQL）。

2.3默认值

-- 常量默认值
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);
-- 序列作为默认值
CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);
-- 当前时间作为默认值
CREATE TABLE products (
    create_time timestamp DEFAULT CURRENT_TIMESTAMP,
    ...
);

2.4生成列

生成的列是一个特殊的列，它总是从其他列计算而来。因此说，它对于列就像视图对于表一样。生成列有两种:存储列和虚拟列。 存储生成列在写入(插入或更新)时计算，并且像普通列一样占用存储空间。虚拟生成列不占用存储空间并且在读取时进行计算。 如此看来，虚拟生成列类似于视图，存储生成列类似于物化视图(除了它总是自动更新之外)。 PostgreSQL目前只实现了存储生成列。我的理解就像VUE中的计算值。

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

2.5约束

SQL允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。即便是这个值来自于默认值定义，这个规则也同样适用。

2.5.1检查约束

一个检查约束是最普通的约束类型。它允许我们指定一个特定列中的值必须要满足一个布尔表达式。例如，为了要求正值的产品价格，我们可以使用：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

要指定一个命名的约束，请在约束名称标识符前使用关键词CONSTRAINT，然后把约束定义放在标识符之后（如果没有以这种方式指定一个约束名称，系统将会为我们选择一个）。

但是需要注意的是 检查约束只能对当前行数据进行约束，不能引用表中的其他行或新插入/更新的其他行。如下SQL，PG会直接报错的。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL CHECK (price > (SELECT AVG(price) FROM products)) -- ❌ PostgreSQL 不支持
);

2.5.2非空约束

一个非空约束仅仅指定一个列中不会有空值。语法如下：

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

2.5.3唯一约束

唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。语法如下：

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

要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

增加一个唯一约束会在约束中列出的列或列组上自动创建一个唯一B-tree索引。通常，如果表中有超过一行在约束所包括列上的值相同，将会违反唯一约束。但是在这种比较中，两个空值被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。这种行为符合SQL标准。

2.5.4主键约束

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。因此可以用以下两中语法定义：

-- 语法一
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
-- 语法二
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

主键也可以包含多于一个列，其语法和唯一约束相似：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

增加一个主键将自动在主键中列出的列或列组上创建一个唯一B-tree索引。并且会强制这些列被标记为NOT NULL。一个表最多只能有一个主键。关系数据库理论要求每一个表都要有一个主键。但PostgreSQL中并未强制要求这一点，但是最好能够遵循它。

2.5.5 外键约束

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。语法如下：

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

如果缺少列的列表，则被引用表的主键将被用作被引用列。语法如下:

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

与主键约束不一样，一个表可以有超过一个的外键约束。

• ON DELETE:
  • 限制删除：RESTRICT阻止删除一个被引用的行。NO ACTION表示在约束被检查时如果有任何引用行存在，则会抛出一个错误。这是外键约束的默认行为。
  • 级联删除：指定当一个被引用行被删除后，引用它的行也应该被自动删除。还有其他两种选项：SET NULL和SET DEFAULT。这些将导致在被引用行被删除后，引用行中的引用列被置为空值或它们的默认值。
• ON UPDATE:CASCADE意味着被引用列的更新值应该被复制到引用行中。

一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一个索引。因此在其上进行的一个引用行是否匹配的检查将会很高效。由于从被引用表中DELETE一行或者UPDATE一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立合适的索引也会大有益处。由于这种做法并不是必须的，而且创建索引也有很多种选择，所以外键约束的定义并不会自动在引用列上创建索引。

2.5.6排他约束

排他约束是一种约束机制，确保某个字段或字段组合的值在整个数据库表中是唯一的，并且在插入或更新数据时，其他事务不能修改或插入相同的数据。这种约束可以防止数据冲突和重复插入。

2. 排他约束的实现方式

CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);

2.6系统列

每一个表都拥有一些由系统隐式定义的system columns。做低代码平台的时候会用到。

系统列	数据类型	作用
ctid	tid	行的物理位置（块号+行号），用于快速定位数据行。
oid	oid	对象 ID（仅在启用 WITH OIDS 旧模式时存在）。
xmin	xid	插入该行的事务 ID，用于 MVCC 事务控制。
xmax	xid	删除或更新该行的事务 ID，如果行未被删除则为 0。
cmin	cid	当前事务中的命令序号，表示该行由事务内的哪个语句修改。
cmax	cid	当前事务中的命令序号（删除），如果该行被删除则存储删除的命令。
tableoid	oid	表的 OID，可用于分区表或继承表中区分行属于哪个子表。

2.7修改表

2.7.1添加列

ALTER TABLE products ADD COLUMN description text;

需要注意的是：从 PostgreSQL 11开始，添加一个具有常量默认值的列不再意味着在执行ALTER TABLE 语句时需要更新表的每一行。 相反，默认值将在下次访问该行时返回，并在表被重写时应用，从而使得ALTER TABLE即使在大表上也非常快。

2.7.2删除列

ALTER TABLE product DROP COLUMN sex;

列中的数据将会消失。涉及到该列的表约束也会被移除。然而，如果该列被另一个表的外键所引用，PostgreSQL不会安静地移除该约束。我们可以通过增加CASCADE来级联删除。

2.7.3增加约束

// 添加表约束
ALTER TABLE product ALTER COLUMN age SET NOT NULL;
// 添加列约束
ALTER TABLE product ADD CHECK ("name" <> '')

2.7.4删除约束

为了移除一个约束首先需要知道它的名称。如果在创建时已经给它指定了名称，那么事情就变得很容易。否则约束的名称是由系统生成的，我们必须先找出这个名称。psql的命令\d 表名将会对此有所帮助。

ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

2.7.5更改列的默认值

-- 添加默认值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
-- 删除默认值
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

2.7.6修改列的数据类型

ALTER TABLE product ALTER COLUMN sex TYPE VARCHAR(255);

只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。
PostgreSQL将尝试把列的默认值转换为新类型，其他涉及到该列的任何约束也是一样。但是这些转换可能失败或者产生奇特的结果。因此最好在修改类型之前先删除该列上所有的约束，然后在修改完类型后重新加上相应修改过的约束。

2.7.7重命名列

ALTER TABLE product RENAME COLUMN sex TO sex1;

2.7.8重命名表

ALTER TABLE products RENAME TO items;

2.8权限

一旦一个对象被创建，它会被分配一个所有者。所有者通常是执行创建语句的角色。对于大部分类型的对象，初始状态下只有所有者（或者超级用户,postgresql超级管理员为postgres）能够对该对象做任何事情。为了允许其他角色使用它，必须分配权限。

2.8.1权限分类

权限分类	作用
SELECT	允许 SELECT 从任何列、或特定的列、表、视图、物化视图、或其他类似表格的对象。 也允许使用 COPY TO. 还需要这个权限来引用UPDATE 或 DELETE中现有的列值。 对于序列，这个权限还允许使用currval 函数。对于大对象，此权限允许读取对象。
INSERT	允许将新行的 INSERT 加入表、视图等等。
UPDATE	允许 UPDATE 更新任何列、或指定列、表、视图等等。 对于序列，这个权限允许使用 nextval 和 setval 函数。对于大对象，此权限允许写入或截断对象。
DELETE	允许 DELETE 从表、视图等等中删除行. (实际上，任何有效的DELETE命令也需要SELECT权限，因为它必须引用表列来确定要删除的行。)
TRUNCATE	允许在表、视图等等上 TRUNCATE 。
REFERENCES	允许创建引用表或表的特定列的外键约束。
TRIGGER	允许在表、视图等等上创建触发器。
CREATE	对于数据库，允许在数据库中创建新的模式和发布，并允许在数据库中安装受信任的扩展。对于模式，允许在模式中创建新对象。要重命名现有对象，你必须拥有对象 and所包含模式的此权限。对于表空间，允许在表空间中创建表、索引和临时文件，并允许创建将表空间作为默认表空间的数据库。注意，取消该特权不会改变现有对象的存在或位置。
CONNECT	允许受让者连接到数据库。
TEMPORARY	允许在使用数据库时创建临时表。
EXECUTE	允许调用函数或过程，包括使用在函数之上实现的任何运算符。这是适用于函数和过程的唯一权限类型。
USAGE	对于程序语言，允许使用语言来创建该语言的函数。 这是适用于过程语言的唯一权限类型。

• 授权(向product1赋予UPDATE更新权限)

GRANT UPDATE ON product1 TO test;

• 撤销授权

REVOKE ALL ON product1 FROM PUBLIC;

2.9行安全性策略

行安全性策略是针对每一个用户限制那些行可以被普通的查询返回或者可以被数据修改命令插入、更新或删除。默认情况下，表不具有任何策略，所有用户都具有对表的访问特权。

2.9.1启用行安全性

需要注意的是表的创建者不服从行安全性策略(可以通过强制开启，使表的创建者服从行安全性策略()，默认采用否定策略，即所有的行都不可见而且不能修改。

ALTER TABLE 表名 ENABLE ROW LEVEL SECURITY;
-- 强制开启
ALTER TABLE 表名 FORCE ROW LEVEL SECURITY;

2.9.2创建/修改/删除表策略

-- 创建表
CREATE TABLE accounts (manager text, company text, contact_email text);
-- 开启行安全行策略
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
-- 创建策略
CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

2.10模式

PostgreSQL模式可以看成一个表的集合，一个模式可以包含视图、索引、数据类型、函数和操作符等。相同的对象名称可以被用于不同的模式中而不会出现冲突。和数据库不同，模式并不是被严格地隔离：一个用户可以访问他们所连接的数据库中的所有模式内的对象，只要他们有足够的权限。

模式的使用场景:

• 允许多个用户使用一个数据库并且不会互相干扰
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

2.10.1创建模式

CREATE SCHEMA 模式名;

创建一个由其他人所拥有的模式

CREATE SCHEMA schema_name AUTHORIZATION user_name;

2.10.2访问模式中的表

schema.table

也可以使用<database.schema.table>，但是目前它只是在形式上与SQL标准兼容。如果我们写一个数据库名称，它必须是我们正在连接的数据库。

2.10.3删除模式

DROP SCHEMA myschema;

要删除一个模式以及其中包含的所有对象，可用：

DROP SCHEMA myschema CASCADE;

2.10.4 公共模式

在前面的小节中，我们创建的表都没有指定任何模式名称。默认情况下这些表（以及其他对象）会自动的被放入一个名为“public”的模式中。任何新数据库都包含这样一个模式。因此，下面的命令是等效的：

CREATE TABLE products ( ... );

CREATE TABLE public.products ( ... );

2.10.5模式搜索路径

模式搜索路径（Search Path） 是一个配置参数，用于指定在查询数据库对象（如表、函数、视图等）时，PostgreSQL 应该按照什么顺序去查找这些对象所在的模式（Schema）。就是为了解决当你不显式设定模式名，将采用什么模式的疑问？

• 显示当前搜索路径

SHOW search_path;
-- 在默认设置下这将返回：
 search_path
--------------
 "$user", public

默认情况表示，会优先搜索当前用户同名的模式。如果不存在这个模式，该项将被忽略。第二个元素指向我们已经见过的公共模式。

• 修改模式搜索路径

-- 临时修改（仅对当前会话有效）
SET search_path TO schema1, schema2, public;
-- 永久修改（对当前数据库的所有会话有效）
ALTER DATABASE 数据库名 SET search_path TO schema1, schema2, public;
-- 针对特定用户或角色修改
ALTER ROLE 用户名 SET search_path TO schema1, schema2, public;

2.10.6pg_catalog

除public和用户创建的模式之外，每一个数据库还包括一个pg_catalog模式，它包含了系统表和所有内建的数据类型、函数以及操作符。无论你怎么设置搜索路径，系统都会自动先查pg_catalog，确保你能找到这些内置的工具。

SET search_path TO "public","pg_catalog";

2.10.6可移植性

同样，在SQL标准中也没有public模式的概念。为了最大限度的与标准一致，我们不应使用（甚至是删除）public模式。当然，某些SQL数据库系统可能根本没有实现模式，或者提供（很可能是有限制地）允许跨数据库访问的命名空间。如果需要使用这样的系统，为了获得最好的可移植性，最好不要使用模式。

2.11继承

PostgreSQL实现了表继承，并且是多继承，即一个表可以从0个或者多个其他表继承。如下要为城市建立一个数据模型。每一个州有很多城市，但是只有一个首府：

CREATE TABLE cities (
    name            text,
    population      float,
    elevation       int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

在这种情况下，capitals表继承了它的父表cities的所有列。州首府还有一个额外的列state用来表示它所属的州。在PostgreSQL中，一个表可以从0个或者多个其他表继承，而对一个表的查询则会查询该表的所有行加上它所有的后代表。如下将查询所有的城市（包括首都）

SELECT name, elevation
    FROM cities
    WHERE elevation > 500;

如果只想要查询首都，则使用ONLY关键字,SELECT、UPDATE和DELETE都支持ONLY关键词,写*也可以,为了兼容可以修改默认值的较老版本：

SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;
SELECT name, elevation
    FROM cities*
    WHERE elevation > 500;

继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。下面的INSERT语句将会失败：

INSERT INTO cities (name, population, elevation, state)
VALUES ('Albany', NULL, NULL, 'NY');

• 父表上的所有检查约束和非空约束都将自动被它的后代所继承，除非显式地指定了NO INHERIT子句。其他类型的约束（唯一、主键和外键约束）则不会被继承。
• 一个表可以从超过一个的父表继承，在这种情况下它拥有父表们所定义的列的并集。任何定义在子表上的列也会被加入到其中。如果在这个集合中出现重名列，那么这些列将被“合并”，这样在子表中只会有一个这样的列。重名列能被合并的前提是这些列必须具有相同的数据类型，否则会导致错误。可继承的检查约束和非空约束会以类似的方式被合并。
• 当有任何一个子表存在时，父表不能被删除。当子表的列或者检查约束继承于父表时，它们也不能被删除或修改。

2.12表分区

划分指的是在逻辑上一个大表分成一些小的物理上的片，划分的作用是：

• 某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
• 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
• 如果批量操作的需求是在分区设计时就规划好的，则批量装载和删除可以通过增加或者去除分区来完成。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE删除一个分区远快于批量操作。这些命令也完全避免了批量DELETE导致的VACUUM开销。
• 很少使用的数据可以被迁移到便宜且较慢的存储介质上。

PostgreSQL对下列分区形式提供了内建支持：

• 范围划分:表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
• 列表划分:通过显式地列出每一个分区中出现的键值来划分表。
• 哈希分区:通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。

3.表数据操作

3.1插入数据

3.1.1插入一行

-- 知道表中列的顺序
INSERT INTO products VALUES (1, 'Cheese', 9.99);
-- 不知道表中列的顺序
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);

3.1.2批量插入

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

3.1.3插入查询的结果

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

在一次性插入大量数据时，考虑使用COPY命令。它不如INSERT命令那么灵活，但是更高效。

3.2更新数据

3.2.1通过已知值更新

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

3.2.1通过查询条件更新

update 待更新表 a
set 待更新表_字段一 = b.字段一 ,
待更新表_字段二 = b.字段二
from 关联表 b 
where  a.关联字段= b.关联字段

3.3删除数据

3.3.1基础删除

DELETE FROM products WHERE price = 10;

3.3.2清空表

TRUNCATE 表名;

3.4从修改的行中返回数据

3.4.1插入的行返回数据

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id,firstname;

3.4.2修改的行返回数据

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

3.4.3删除的行返回数据

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

4.表查询

4.1From

FROM子句从一个用逗号分隔的表引用列表中的一个或更多个其它表中生成一个表。如果FROM子句后跟了多个表，即构造它们的行的笛卡尔积

4.1.1连接表

连接表是根据特定的连接类型的规则从两个其它表（真实表或生成表）中派生的表。目前支持内连接、外连接和交叉连接。

4.1.1.1交叉连接

将T1和T2的每一行进行组合（即笛卡尔积），连接表由所有T1的列和T2里面的列构成。如果两个表分别有 N 和 M 行，连接表将有 N * M 行。FROM T1 CROSS JOIN T2等效于FROM T1 INNER JOIN T2 ON TRUE（见下文）。它也等效于FROM T1,T2。

T1 CROSS JOIN T2

但是需要注意的是，CROSS JOIN的优先级是高于，的，因此FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition和FROM T1,T2 INNER JOIN T3 ON condition是不一样的

-- 案例1: 显式 CROSS JOIN + INNER JOIN
FROM T1 CROSS JOIN T2 
       INNER JOIN T3 ON condition
-- 等价于 (T1 × T2) ⋈ T3
--         ▲ 显式结合  ▲ 后续 JOIN

-- 案例2: 逗号混合 JOIN
FROM T1, T2 INNER JOIN T3 ON condition
-- 等价于 T1 × (T2 ⋈ T3)
--              ▲ 优先结合

因此第一个案例的condition是可以用T1的字段的，第二个案例的condition不可以用T1的字段的

4.1.1.2条件连接

条件类型	语法	说明
INNER JOIN	T1 INERT JOIN T2 ON 条件	返回T1通过条件与T2匹配的行，如果T2中没有匹配的行，则不显示。
LEFT OUTER JOIN	T1 LEFT OUTER JOIN T2 ON 条件	首先，执行一次内连接（用T1匹配T2）。如果T2中没有匹配的行，则用空值代替，此时，T1是主表。
RIGHT OUTER JOIN	T1 RIGHT OUTER JOIN T2 ON 条件	首先，执行一次内连接（用T2匹配T1）。如果T1中没有匹配的行，则用空值代替，此时，T2是主表。
FULL OUTER JOIN	T1 FULL OUTER JOIN T2 ON 条件	首先，执行一次内连接。然后，为 T1 中每一个无法在连接条件上匹配 T2 里任何一行的行返回一个连接行，该连接行中 T2 的列用空值补齐。同样，为 T2 中每一个无法在连接条件上匹配 T1 里任何一行的行返回一个连接行，该连接行中 T1 的列用空值补齐。

USING是个缩写符号,它允许你利用特殊的情况：连接的两端都具有相同的连接列名。它接受共享列名的一个逗号分隔列表，并且为其中每一个共享列构造一个包含等值比较的连接条件。例如用USING (a, b)连接T1和T2会产生连接条件ON T1.a = T2.a AND T1.b = T2.b。
更进一步，JOIN USING的输出会废除冗余列：不需要把匹配上的列都打印出来，因为它们必须具有相等的值。不过JOIN ON会先产生来自T1的所有列，后面跟上所有来自T2的列；而JOIN USING会先为列出的每一个列对产生一个输出列，然后先跟上来自T1的剩余列，最后跟上来自T2的剩余列。NATURAL是USING的缩写形式，例如SELECT * FROM t1 NATURAL INNER JOIN t2;会输出t1和t2的列，但是重复列只会生成一个。

为了解释这些问题，假设我们有一个表t1：
num | name
-----+------
   1 | a
   2 | b
   3 | c
和t2：

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz
然后我们用不同的连接方式可以获得各种结果：

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

ON子句中的一个约束在连接之前被处理，而放在WHERE子句中的一个约束是在连接之后被处理。这对内连接没有关系，但是对于外连接会带来麻烦。

4.1.2别名

• 表别名：FROM table_reference AS alias/FROM table_reference alias

如果一个表引用是一个子查询，则必须要使用一个别名

• 字段名：SELECT oid AS "id"

4.1.3LATERAL子查询

可以在出现于FROM中的子查询前放置关键词LATERAL。这允许它们引用前面的FROM项提供的列（如果没有LATERAL，每一个子查询将被独立计算，并且因此不能被其他FROM项交叉引用）。

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

有时候也会很特别地把LEFT JOIN放在一个LATERAL子查询的前面，这样即使LATERAL子查询对源行不产生行，源行也会出现在结果中。

4.2Where

WHERE子句的语法是WHERE search_condition这里的search_condition是任意返回一个boolean类型值的值表达式，在完成对FROM子句的处理之后，生成的虚拟表的每一行都会对根据搜索条件进行检查。 如果该条件的结果是真，那么该行被保留在输出表中；否则（也就是说，如果结果是假或空）就把它抛弃。

4.3GROUP BY和HAVING子句

在通过了WHERE过滤器之后，生成的输入表可以使用GROUP BY子句进行分组，然后用HAVING子句删除一些分组行。

SELECT oi.* 
FROM object_instance oi 
WHERE oi.del_flag = '1' 
GROUP BY oi.object_id HAVING oi.scope > 1.0

4.4GROUPING SETS、CUBE和ROLLUP

4.4.1GROUPING SETS

GROUPING SETS 允许在同一个查询中指定多个不同的分组方式，返回多个聚合结果。它的作用类似于执行多个 GROUP BY 查询，然后将结果合并在一起。如下：

-- 创建表
CREATE TABLE sales_data (
    region VARCHAR(50),
    product VARCHAR(50),
    sales INT
);

-- 插入数据
INSERT INTO sales_data (region, product, sales)
VALUES
    ('North', 'ProductA', 100),
    ('North', 'ProductB', 200),
    ('South', 'ProductA', 150),
    ('South', 'ProductB', 250),
    ('East', 'ProductA', 300),
    ('East', 'ProductB', 350),
    ('West', 'ProductA', 400),
    ('West', 'ProductB', 500);
-- 查询
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS (
    (region, product),  -- 按 region 和 product 分组
    (region),           -- 按 region 分组
    (product)           -- 按 product 分组
);
-- 具体地，GROUPING SETS 定义了 三种不同的分组方式：
-- 按 (region, product) 分组
region	product	SUM(sales)
North	ProductA	100
North	ProductB	200
South	ProductA	150
South	ProductB	250
East	ProductA	300
East	ProductB	350
West	ProductA	400
West	ProductB	500
-- 按 region 分组（忽略 product）
region	product	SUM(sales)
North	NULL	300
South	NULL	400
East	NULL	65
-- 按 product 分组（忽略 region）
region	product	SUM(sales)
NULL	ProductA	950
NULL	ProductB	1300

CUBE或ROLLUP子句中的元素可以是表达式或者 圆括号中的元素子列表。在后一种情况中，对于生成分组集的目的来说，子列 表被当做单一单元来对待。例如：

CUBE ( (a, b), (c, d) )
等效于

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)
并且

ROLLUP ( a, (b, c), d )
等效于

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

4.5DISTINCT

在处理完选择列表之后，结果表可以可选的删除重复行。我们可以直接在SELECT后面写上DISTINCT关键字来指定：SELECT DISTINCT .....
当然，也可以自定义重复条件。

SELECT DISTINCT ON (sd.region, sd.product) sd.*
FROM sales_data sd

4.6组合查询

两个查询的结果可以用集合操作并、交、差进行组合。语法是

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

• UNION有效地把query2的结果附加到query1的结果上（不过我们不能保证这就是这些行实际被返回的顺序）。此外，它将删除结果中所有重复的行， 就象DISTINCT做的那样，除非你使用了UNION ALL。
• INTERSECT返回那些同时存在于query1和query2的结果中的行，除非声明了INTERSECT ALL， 否则所有重复行都被消除。
• EXCEPT返回所有在query1的结果中但是不在query2的结果中的行（有时侯这叫做两个查询的差）。同样的，除非声明了EXCEPT ALL，否则所有重复行都被消除。

4.7行排序

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

排序表达式可以是任何在查询的选择列表中合法的表达式。一个例子是：

SELECT a, b FROM table1 ORDER BY a + b, c;

当多于一个表达式被指定，后面的值将被用于排序那些在前面值上相等的行。每一个表达式后可以选择性地放置一个ASC或DESC关键词来设置排序方向为升序或降序。ASC顺序是默认值。升序会把较小的值放在前面，而“较小”则由<操作符定义。相似地，降序则由>操作符定义。

NULLS FIRST和NULLS LAST选项将可以被用来决定在排序顺序中，空值是出现在非空值之前或者出现在非空值之后。默认情况下，排序时空值被认为比任何非空值都要大，即NULLS FIRST是DESC顺序的默认值。

4.8LIMIT和OFFSET

LIMIT和OFFSET允许你只检索查询剩余部分产生的行的一部分：

LECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

LIMIT ALL的效果和省略LIMIT子句一样，就像是LIMIT带有 NULL 参数一样。

OFFSET说明在开始返回行之前忽略多少行。OFFSET 0的效果和省略OFFSET子句是一样的，并且LIMIT NULL的效果和省略LIMIT子句一样，就像是OFFSET带有 NULL 参数一样。如果OFFSET和LIMIT都出现了， 那么在返回LIMIT个行之前要先忽略OFFSET行

需要注意的是：查询优化器在生成查询计划时会考虑LIMIT，因此如果你给定LIMIT和OFFSET，那么你很可能收到不同的规划（产生不同的行顺序）。因此，使用不同的LIMIT/OFFSET值选择查询结果的不同子集将生成不一致的结果，除非你用ORDER BY强制一个可预测的顺序。这并非bug， 这是一个很自然的结果，因为 SQL 没有许诺把查询的结果按照任何特定的顺序发出，除非用了ORDER BY来约束顺序。

4.9VALUES列表

VALUES提供了一种生成“常量表”的方法，它可以被使用在一个查询中而不需要实际在磁盘上创建一个表。语法是：

VALUES ( expression [, ...] ) [, ...]

每一个被圆括号包围的表达式列表生成表中的一行。列表都必须具有相同数据的元素（即表中列的数目），并且在每个列表中对应的项必须具有可兼容的数据类型。在默认情况下，PostgreSQL将column1、column2等名字分配给一个VALUES表的列。这些列名不是由SQL标准指定的，并且不同的数据库系统的做法也不同，因此通常最好使用表别名列表来重写这些默认的名字，像这样：

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

4.10WITH查询（公共表表达式）

WITH查询被称为公共表表达式（CTE），它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE。例如：

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

4.10.1递归查询

RECURSIVE修饰符将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。从而实现递归。例如找到一个产品的BOM清单

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

上述SQL是首先执行SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'这条SQL，这条SQL也被称为初始语句，执行后，把结果放在一个临时表中，作为WITH语句首次执行结果，然后再执行SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part这条语句，由于这条语句中的数据源来自WITH语句本身，因此会在第一次执行结果作为基础上继续查询。然后第三次以第二次查询作为基础继续查询，依次类推，直到SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part这个语句查询结果为空，则停止查询。

4.10.2WITH中的数据修改语句

可以在WITH中使用数据修改语句（INSERT、UPDATE或DELETE）。这允许你在同一个查询中执行多个不同操作。一个例子：

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

这个查询实际上将products中的行移动到products_log。WITH中的DELETE删除来自products的指定行，以RETURNING子句返回被删除的内容，然后主查询读该输出并将它插入到products_log。

4.10.3Like

LIKE 通常与通配符一起使用：

• %：匹配任意字符序列（包括空字符）。

• _：匹配单个字符。

5.常用数据类型

我列了一些我用的类型，并不全。

5.1整数

名字	存储尺寸	描述	范围
smallint	2字节	小范围整数	-32768 to +32767
integer	4字节	整数的典型选择	-2147483648 to +2147483647
bigint	8字节	大范围整数	-9223372036854775808 to +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

常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint类型。而只有在integer的范围不够的时候才使用bigint。

SQL只声明了整数类型integer（或int）、smallint和bigint。类型int2、int4和int8都是扩展，也在许多其它SQL数据库系统中使用。

5.2任意精度数字

类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。

一个numeric的precision（精度）是整个数中有效位的总数，也就是小数点两边的位数。numeric的scale（小数位数）是小数部分的数字位数，也就是小数点右边的部分。因此数字 23.5141 的精度为6而小数位数为4。可以认为整数的小数位数为零。numeric列的最大精度和最大小数位数都是可以配置的。要声明一个类型为numeric的列的语法是：NUMERIC(precision, scale)也可以使用NUMERIC(precision)来声明一个precision位的整数，而NUMERIC创建一个列时不使用精度或小数位数，则该列可以存储任何精度和小数位数的数字值，并且值的范围最多可以到实现精度的上限。显式指定类型精度时的最大允许精度为 1000，没有指定精度的NUMERIC默认为最高小数点前131072位，以及小数点后16383位。

如果一个要存储的值的小数位数比列声明的小数位数高，那么系统将尝试圆整（四舍五入）该值到指定的分数位数。 然后，如果小数点左边的位数超过了声明的精度减去声明的小数位数，那么抛出一个错误。

除了普通的数字值之外，numeric类型允许特殊值NaN， 表示“不是一个数字”。任何在 NaN上面的操作都生成另外一个NaN。为了允许numeric值可以被排序和使用基于树的索引，PostgreSQL把NaN值视为相等，并且比所有非NaN值都要大。

在对值进行圆整时，numeric类型会圆到远离零的整数，而（在大部分机器上）real和double precision类型会圆到最近的偶数上。例如：

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

5.3浮点类型

FLOAT基于 IEEE 标准（单精度 REAL，双精度 DOUBLE PRECISION），使用二进制浮点算术。无法精确表示某些十进制小数（如 0.1），可能导致舍入误差。适用科学计算、测量数据等可容忍微小误差的场景。除了普通的数字值之外

浮点类型还有几个特殊值：Infinity -Infinity NaN。这些分别代表 IEEE 754 特殊值“infinity”、“negative infinity”以及“not-a-number”， 如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号，例如UPDATE table SET x = ‘-Infinity’。 在输入时，这些字符串是以大小写不敏感的方式识别的。

PostgreSQL还支持 SQL 标准表示法float和float（p）用于声明非精确的数字类型。在这里，p指定以二进制位表示的最低可接受精度。 在选取real类型的时候，PostgreSQL接受float(1)到float(24)，在选取double precision的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误。没有指定精度的float将被当作是double precision。

5.4字符类型

名字	描述
character varying(n), varchar(n)	有限制的变长
character(n), char(n)	定长，空格填充
text	无限变长

SQL定义了两种基本的字符类型： character varying(n)和character(n)， 其中n是一个正整数。两种类型都可以存储最多n个字符长的串。试图存储更长的串到这些类型的列里会产生一个错误， 除非超出长度的字符都是空白，这种情况下该串将被截断为最大长度（这个看上去有点怪异的例外是SQL标准要求的）。 如果要存储的串比声明的长度短，类型为character的值将会用空白填满；而类型为character varying的值将只是存储短些的串。

varchar(n)和char(n)的概念分别是character varying(n)和character(n)的别名。没有长度声明词的character等效于character(1)。如果不带长度说明词使用character varying，那么该类型接受任何长度的串。

这三种类型之间没有性能差别，只不过是在使用填充空白的类型的时候需要更多存储尺寸，以及在存储到一个有长度约束的列时需要少量额外CPU周期来检查长度。虽然在某些其它的数据库系统里，character(n)有一定的性能优势，但在PostgreSQL里没有。事实上，character(n)通常是这三种类型之中最慢的一个，因为它需要额外的存储开销。在大多数情况下，应该使用text或者character varying。

5.4.1特殊字符类型

在PostgreSQL里另外还有两种定长字符类型。这两种类型都是用在内部系统中的。

类型	描述
char	1字节 单字节内部类型
name	64字节 用于对象名的内部类型

5.5日期/时间

名字	存储尺寸	描述	最小值	最大值	解析度
timestamp [ (p) ] [ without time zone ]	8字节	包括日期和时间（无时区）	4713 BC	294276 AD	1微秒
timestamp [ (p) ] with time zone	8字节	包括日期和时间，有时区	4713 BC	294276 AD	1微秒
date	4字节	日期（没有一天中的时间	4713 BC	5874897 AD	1日
time [ (p) ] [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒
time [ (p) ] with time zone	12字节	仅仅是一天中的时间（没有日期），带有时区	00:00:00+1459	24:00:00-1459	1微秒
interval	[ fields ] [ § ]	16字节	时间间隔	-178000000年	178000000年

SQL要求只写timestamp等效于timestamp without time zone，并且PostgreSQL鼓励这种行为。timestamptz被接受为timestamp with time zone的一种简写，这是一种PostgreSQL的扩展。

time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界。p允许的范围是从 0 到 6。

5.5.1常用的字符串

输入串	合法类型	描述
epoch	date, timestamp	1970-01-01 00:00:00+00（Unix系统时间0）
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

以上字符串可以直接转换为日期/时间类，使用如下

select 'today'::TIMESTAMP

在 SQL 中，像 now、today、tomorrow 和 yesterday 这样的字符串是被解析为特定的时间值。它们的解析可能会因为数据库会话的时间不同而产生意外的结果。当 SQL 语句被保存并在未来某个时刻执行时，这些相对时间的字符串（如 tomorrow）会被数据库在定义时解析成一个具体的时间值，而不是在执行时再次动态计算。这就意味着，如果你将 tomorrow 用在视图或函数中，执行这个视图或函数时，它返回的值可能并不是你期待的“明天”的时间。

5.5.1常用的常量

常量	描述
CURRENT_DATE	当前日期（如：2025-02-24）
CURRENT_TIME	当前时间（如：11:34:15.251469+00）
CURRENT_TIMESTAMP	当前日期（带时间）（如：22025-02-24 11:34:30.15445+00）
LOCALTIME	本地时间（如：11:34:52.65948）
LOCALTIMESTAMP	本地日期（带时间）（如：2025-02-24 11:35:46.852368）

使用方法

SELECT CURRENT_DATE

5.6日期/时间输出风格

风格声明	描述	例子
ISO	ISO 8601, SQL标准	1997-12-17 07:37:16-08
SQL	传统风格	12/17/1997 07:37:16.00 PST
Postgres	原始风格	Wed Dec 17 07:37:16 1997 PST
German	地区风格	17.12.1997 07:37:16.00 PST

ISO 8601指定使用大写字母T来分隔日期和时间。PostgreSQL在输入上接受这种格式，但是在输出时它采用一个空格而不是T，如上所示。和一些其他数据库系统一样

5.7布尔类型

PostgreSQL提供标准的SQL类型boolean，存储字节为 1字节。boolean可以有多个状态：“true（真）”、“false（假）”和第三种状态“unknown（未知）”，未知状态由SQL空值表示。
在SQL查询中，布尔常量可以表示为SQL关键字TRUE, FALSE,和 NULL.

boolean 类型的数据类型输入函数接受这些字符串表示“真”状态:true，yes，on，1
下面这些表示“假” 状态:false，no，off，0

5.8JSON类型

JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据。PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。

JSON和JSONB数据类型接受几乎完全相同的值集合作为输入，主要的区别在于JSON数据执行函数时需要重新解析该数据，而JSONB实在存储时将数据解析成一种二进制格式，在执行函数时就不需要解析，但是存储的时候就慢一点，同时JSONB也支持索引。

JSON类型是对输入的值进行文本存储不会检查JSON语法。 如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（ 处理函数会把最后的值当作有效值）。而JSONB不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有最后一个值会被保留。

通常，除非有特别特殊的需要，大多数应用应该 更愿意把 JSON 数据存储为JSONB。

5.8.1JSON数据的声明

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;

-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;

-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

需要注意的是，在jsonb中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

5.8.2JSON数据的包含

-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- 右边的数组不会被认为包含在左边的数组中，
-- 即使其中嵌入了一个相似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 得到假

-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 得到假

-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原则是被包含的对象必须在结构和数据内容上匹配包含对象，所以这个符号经常用在数组的包含上。

需要注意的是，pg在以下两种包含中，推荐我们使用第二种，说这样做效率高。

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

5.8.3JSON数据的存在

一个字符串（以一个text值的形式给出）是否出现在jsonb值顶层的一个对象键或者数组元素中，需要注意的是JSON数据的存在只能查顶层数据，但是可以通过->取到底层数据，然后再查询。

-- 字符串作为一个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- 字符串作为一个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- 得到假

-- 和包含一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假

-- 如果一个字符串匹配一个基本 JSON 字符串，它就被认为存在：
SELECT '"foo"'::jsonb ? 'foo';

-- 底层匹配
SELECT '{"foo": {"bar": "baz"}}'::jsonb -> 'foo' ? 'bar'

5.8.4JSONB索引

JSONB使用了GIN索引。GIN 是 “Generalized Inverted Index” 的缩写，意思是“通用倒排索引”。它主要用于处理包含多个值的数据类型，比如数组、全文搜索、JSONB 等。GIN 索引的核心思想是“倒排”，也就是通过值来快速找到包含该值的行。

JSONB的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询，顺便了解一下常用的操作符，常用的操作符如下：

• @>:包含,例如：‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb → t
• ?:文本字符串是否作为JSON值中的顶级键或数组元素存在,例如：{“a”:1, “b”:2}'::jsonb ? ‘b’ → t
• ?|:数组中的字符串是否有一项作为顶级键或数组元素存在,例如：‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘d’] → t
• ?&:文本数组中的所有字符串都作为顶级键或数组元素存在吗,例如：‘[“a”, “b”, “c”]’::jsonb ?& array[‘a’, ‘b’] → t
• ||:连接两个jsonb值。连接两个数组将生成一个包含每个输入的所有元素的数组。连接两个对象将生成一个包含它们键的并集的对象，当存在重复的键时取第二个对象的值。 所有其他情况都是通过将非数组输入转换为单个元素数组，然后按照两个数组的方式进行处理。 不递归操作:只有顶级数组或对象结构被合并。例如：‘[“a”, “b”]’::jsonb || ‘[“a”, “d”]’::jsonb → [“a”, “b”, “a”, “d”]，‘{“a”: “b”}’::jsonb || ‘{“c”: “d”}’::jsonb → {“a”: “b”, “c”: “d”}
• -:从JSON对象中删除键(以及它的值)，或从JSON数组中删除匹配的字符串值。例如：‘{“a”: “b”, “c”: “d”}’::jsonb - ‘a’ → {“c”: “d”}
• ->:从JSON对象中取一个JSON对象
• ->>:从JSON对象中去一个值

假设你有一个表，里面有一列是“标签”，每个标签是一个数组，比如：

id	tags
1	{food, travel}
2	{food, music}
3	{travel, art}

如果你想快速找到所有包含 food 标签的行，GIN 索引就能派上用场。它会建立一个“倒排表”，记录每个标签对应的行 ID，比如：

food  -> 1, 2
travel -> 1, 3
music  -> 2
art    -> 3

这样，当你查询 WHERE tags @> ‘{food}’ 时，数据库可以直接从索引中找到 food 对应的行 ID（1 和 2），而不需要扫描整个表。

5.8.4.1创建索引

CREATE INDEX 索引名称 ON 表名 USING gin (字段名);

需要注意的是，我为jdoc定义了索引，但是SQLSELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';是不会使用索引的，因为我只是对jdoc的值做了记录，但是没有对jdoc->'tags'所记录，可以对jdoc->'tags'做一个索引。

6.操作符

6.1逻辑运算符

与：AND
或：OR
非：NOT

6.2比较运算符

操作符	描述
datatype < datatype → boolean	小于
datatype > datatype → boolean	大于
datatype <= datatype → boolean	小于等于
datatype >= datatype → boolean	大于等于
datatype = datatype → boolean	等于
datatype <> datatype → boolean	不等于
datatype != datatype → boolean	不等于

<> 表示 “not equal” 的标准的SQL符号。 != 是一个别名, 在解析的早期阶段被转换为 <> 。

6.3常用的数学运算符

描述	例子
加	2 +3 → 5
减	2 -3 → -1
否定	-(-4) → 4
乘	2 *3 → 6
除(对于整型，除法将结果截断)	5.0 /2 → 2.5000000000000000,5 /2 → 2
取余	5 % 4 → 1
指数	2 ^ 3 → 8
绝对值	@ -5.0 → 5

6.4常用的字符串函数

链接

7.索引

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

7.1B-TREE索引

<，<=，=，>=，>，IS NULL，IS NOT NULL，BETWEEN，IN都可以用B-tree搜索。B-tree 是 PostgreSQL 的默认索引类型，它适用于大多数情况，特别是对数值、文本、日期类型的字段。语法如下：

CREATE INDEX idx_column_name ON table_name (column_name);

但是需要注意的是LIKE和~，ILIKE和~*想要使用B-tree索引，必须以非字母字符开始，即不受大小写转换影响的字符。

B-tree 是一种多叉平衡树，假设我们要查找编号是 20 的文件：从根节点开始查找：根节点可能存储了 10, 20, 30 这些数字。首先，比较你要找的数字（20）与根节点上的数字。如果 20 小于 10，就去左边的子节点；如果 20 大于 10 小于 30，就去中间的子节点；如果 20 大于 30，就去右边的子节点。子节点继续查找：继续按照同样的规则查找，直到找到 20 所在的节点。因为树是平衡的，所以你查找的路径是很短的，通常只需要几步就能找到目标数字。

7.2HASH索引

Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时，查询规划器将考虑使用一个Hash索引。下版将创建一个HASH索引

CREATE INDEX name ON table USING HASH (column);

7.3GIST索引

GiST（Generalized Search Tree，通用搜索树）是PostgreSQL中的一种索引类型。它不像普通的B树索引那样只能处理简单的比较（比如数字或字符串的大小），而是可以支持更复杂的数据类型和查询，比如几何图形、全文搜索、数组等。它用来支持使用下列操作符的索引化查询：

<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&

7.4GIN索引

GIN 索引是“倒排索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。

<@
@>
=
&&

7.5BRIN 索引

BRIN 索引（块范围索引的缩写）存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似，BRIN 可以支持很多种不同的索引策略，并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，被索引的数据对应于每个块范围的列中值的最小值和最大值。

<
<=
=
>=
>

7.6多列索引

一个索引可以定义在表的多个列上。例如，我们可以在major和minor上定义一个索引：CREATE INDEX test2_mm_idx ON test2 (major, minor);目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件pg_config_manual.h中修改，但是修改后需要重新编译PostgreSQL）。以下是创建一个多列索引。

7.7索引和排序的关系

除了简单地查询外，一个索引可能还需要将它们以指定的顺序排序。B-TREE可以对数据进行排序。使得查询中的ORDER BY不需要独立的排序步骤。

在ORDER BY与LIMIT N联合使用：没有索引的列排序将会处理所有的数据来确定最前面的n行，但如果有一个符合ORDER BY的索引，前n行将会被直接获取且根本不需要扫描剩下的数据。我们可以在创建B-tree索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序，例如：

CREATE INDEX test3_desc_index ON test3 (info NULLS FIRST, id DESC NULLS LAST);

一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则，你可能需要多个索引。

7.8组合多个索引

只有查询子句中在索引列上使用了索引操作符类中的操作符并且通过AND连接时才能使用单一索引。例如，给定一个(a, b) 上的索引，查询条件WHERE a = 5 AND b = 6可以使用该索引，而查询WHERE a = 5 OR b = 6不能直接使用该索引。

PostgreSQL具有组合多个索引（包括多次使用同一个索引）的能力来处理那些不 能用单个索引扫描实现的情况。系统能在多个索引扫描之间安排AND和OR条件。例如， WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样一个查询可以被分解成为四个独立的在x上索引扫描，每一个扫描使用其中一个条件。这些查询的结果将被“或”起来形成最后的结果。另一个例子是如果我们在x和y上都有独立的索引，WHERE x = 5 AND y = 6这样的查询的一种可能的实现方式就是分别使用两个索引配合相应的条件，然后将结果“与”起来得到最后的结果行。

7.9唯一索引

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。CREATE UNIQUE INDEX name ON table (column [, ...]);当前，只有B-tree能够被声明为唯一。PostgreSQL会自动为定义了一个唯一约束或主键的表创建一个唯一索引。

7.10表达式索引

一个索引列并不一定是表的一个列，也可以是从表的一列或多列计算而来的一个函数或者表达式。例如建立在lower(col1)函数结果之上的索引。

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。

7.11部分索引

部分索引是一个只为满足某些条件的行创建的索引，而不是对整个表的所有行进行索引。这意味着，只有符合指定 WHERE 条件 的行才会被索引。部分索引特别适用于查询时只关注表的某一部分数据的场景，从而节省了空间和提高了查询效率。需要注意是，部分索引并不是把数据进行分区，而是当SQL使用到了与部分索引相同的条件查询时，会使用部分索引查询。例子如下：

select * 
from object_instance oi 
where oi.del_flag = '1' and object_id = '8ce3f5948ade6fddc18c408e7035625f'

create index undeleted_data 
on object_instance (del_flag) 
where del_flag = '1'

7.12只用索引扫描和覆盖索引

只用索引扫描（Index Only Scan）和覆盖索引（Covering Index）是优化查询性能的两种常见技术。它们通过利用索引本身存储的全部信息，避免访问数据表，从而提高查询效率

7.12.1只用索引扫描（Index Only Scan）

只用索引扫描是指数据库在查询时，直接利用索引中的所有数据完成查询，而不需要回到数据表中去获取实际的行数据。也就是说，查询能够通过索引本身提供的所有信息来满足查询条件。

B-树索引总是支持只用索引的扫描。GiST 和 SP-GiST 索引只对某些操作符类支持只用索引的扫描。其他索引类型不支持这种扫描。GIN 索引是一个不支持只用索引的扫描的反例，因为它的每一个索引项通常只包含原始数据值的一部分。而且查询必须只引用存储在该索引中的列。如果查询需要的列不在索引中，那么数据库仍然需要从表中读取数据行

7.12.2索引覆盖

索引覆盖指的是PostgreSQL在执行查询时，如果查询的所有列都包含在某个索引中，那么它可以仅通锅访问索引来获取所需数据，而无需访问实际的数据表。也就是说，索引本身足够存储查询所需的所有信息，可以避免访问表中的数据。

7.12.3例子

假设我们有一个 users 表，包含 id, name, age, email 等字段，并且我们在 name 和 age 上建立了一个联合索引。

索引扫描： 查询条件只涉及 name 和 age 时，PostgreSQL可以使用索引扫描找到符合条件的行，但如果查询还需要 email 字段，那么它依然需要从表中获取 email 数据。

SELECT id, name, age, email FROM users WHERE name = 'Alice' AND age > 30;

即使PostgreSQL使用了索引扫描，查询结果的 email 字段依然需要从表中获取。

索引覆盖： 如果查询条件只涉及 name 和 age，并且查询结果只需要这两列，那么PostgreSQL可以通过索引覆盖直接返回结果，而不需要访问数据表。

SELECT name, age FROM users WHERE name = 'Alice' AND age > 30;

在这种情况下，PostgreSQL能够仅通过索引返回结果，从而避免了额外的表访问。

7.12.4操作符类和操作符族

• 操作符类:是与索引一起使用的一个数据结构，它定义了一组操作符的集合，这些操作符可以用于在索引中进行比较或排序。操作符类指定了哪些操作符（如 <, >, = 等）可以在某种特定类型的索引中使用。
• 操作符族:操作符族是一个包含多个操作符类的集合，每个操作符类支持不同的操作符和函数。这些操作符类用于不同的索引类型，并定义了如何使用这些操作符进行索引扫描和查询。操作符族的目的是使得索引在不同的数据类型和索引类型之间具有通用性和可扩展性。

7.13EXPLAIN

• EXPLAIN:EXPLAIN 会生成查询的执行计划，但不执行查询。包括查询的节点类型、操作顺序、预估的行数、预估的成本等。适合在不影响数据库性能的情况下，快速查看查询的执行计划。
• EXPLAIN ANALYZE:EXPLAIN ANALYZE 不仅生成执行计划，还会实际执行查询，并返回详细的执行统计信息。除了 EXPLAIN 的信息外，还包括实际执行时间、实际返回的行数、实际的内存使用情况等。

8.并发控制

Postgresql通过多版本并发控制（即MVCC）来控制数据的一致性，多版本并发控制指的是为每个事务提供数据的“快照”，事务在操作时看到的是其开始时的数据状态，而不是实时数据。这样，读写操作不会相互阻塞。

MVCC的实现原理是：每次开始一个事务（比如查询、更新数据），PostgreSQL 会给这个事务分配一个主键（叫 XID），而每行数据都有两个重要的标记xmin和xmax(xmin:这行数据的"出生证明"，记录是哪个事务插入了这行数据,xmax：这行数据的"死亡证明"，记录是哪个事务删除了这行数据)

• 更新数据其实是"复制一份",当你更新一行数据时，PostgreSQL 不会直接修改原来的数据，而是把原来的数据标记为“已删除”（设置 xmax)，插入一行新数据，新数据的 xmin 是当前事务的身份证号。
• 当你查询的时候，当你查询数据时，PostgreSQL 会根据你的“视角”来判断哪些数据是你能看到的，
  • 如果一行数据的 xmin 比你的身份证号小，并且这行数据没有被删除（xmax 是空的），那么你能看到这行数据。
  • 如果一行数据被删除了（xmax 有值），并且删除它的事务已经提交了，那么你就看不到这行数据了。

PostgreSQL 有一个叫 VACUUM 的清理工具，它会定期清理那些不再需要的旧数据，释放空间。

8.1事务级别

在认识到事务级别前，需要认识几个名词，如下：

名词	描述
脏读	一个事务读取了另一个并行未提交事务写入的数据
不可重复读	在同一个事务中，多次读取同一行数据时，结果不一致（因为其他事务修改了该行数据并提交），仅限于已存在的行。。
幻读	在同一个事务中，多次执行相同的查询时，返回的结果集不一致（因为其他事务插入或删除了符合查询条件的数据并提交），涉及结果集的变化（新增或删除的行）。
序列化异常	如果系统检测到两个事务的执行顺序可能导致不一致的结果，就会中止其中一个事务，并抛出序列化异常。

隔离级别	脏读	不可重复读	幻读	序列化异常
读未提交	允许，但不在 PG 中	可能	可能	可能
读已提交	不可能	可能	可能	可能
可重复读	不可能	不可能	允许，但不在 PG 中	可能
可序列化	不可能	不可能	不可能	不可能

在PostgreSQL中，内部只实现了三种不同的隔离级别，因为读未提交和读已提交相同。

8.2设置隔离级别

SET TRANSACTION 事务级别

• READ COMMITTED：读已提交。
• REPEATABLE READ：可重复读
• SERIALIZABLE：序列化异常

8.3读已提交

读已提交是PostgreSQL中的默认隔离级别。 当一个事务运行使用这个隔离级别时， 一个查询（没有FOR UPDATE/SHARE子句）只能看到查询开始之前已经被提交的数据， 而无法看到未提交的数据或在查询执行期间其它事务提交的数据。实际上，SELECT查询看到的是一个在查询开始运行的瞬间该数据库的一个快照。不过SELECT可以看见在它自身事务中之前执行的更新的效果，即使它们还没有被提交。

UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行时的行为和SELECT一样： 它们将只查询命令开始时已经被提交的行。 不过，在被找到时，这样的目标行可能已经被其它并发事务更新（或删除或锁住）。在这种情况下， 即将进行的更新将等待地一个更新事务提交或者回滚（如果它还在进行中）。如果第一个更新事务回滚，那么它的作用将被忽略并且第二个事务可以继续更新最初发现的行。 如果第一个更新事务提交，若该行被第一个更新者删除，则第二个更新事务将忽略该行，否则第二个更新者将试图在该行的已被更新的版本上应用它的操作。该命令的搜索条件（WHERE子句）将被重新计算来看该行被更新的版本是否仍然符合搜索条件。如果符合，则第二个更新者使用该行的已更新版本继续其操作。

• SELECT FOR UPDATE: 是 SQL（结构化查询语言）中用于锁定行的一种语法，通常用于事务处理中。它的作用是在查询数据的同时，将查询到的行锁定，以防止其他事务对这些行进行修改或删除，直到当前事务提交或回滚。例如：

-- 假设有一个订单表 orders，你想更新某个订单的状态，但需要先查询该订单是否满足某些条件
BEGIN; -- 开始事务
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
-- 此时，order_id = 1 的行被锁定
-- 如果满足条件，执行更新操作
UPDATE orders SET status = 'completed' WHERE order_id = 1;
COMMIT; -- 提交事务

• SELECT FOR SHARE:是 SQL 中用于锁定行的另一种语法，与 SELECT FOR UPDATE 类似，但它的锁定行为和用途有所不同。SELECT FOR SHARE 的主要目的是以共享锁（Shared Lock）的方式锁定数据，允许其他事务读取这些数据，但阻止其他事务对这些数据进行修改。例如：

-- 假设有一个 employees 表，你需要查询某个员工的工资信息，并确保在查询期间其他事务不会修改这个员工的工资
BEGIN; -- 开始事务
SELECT salary FROM employees WHERE employee_id = 1 FOR SHARE;
-- 此时，employee_id = 1 的行被共享锁锁定
-- 其他事务可以读取这条记录，但不能修改
COMMIT; -- 提交事务

在SELECT FOR UPDATE和SELECT FOR SHARE的情况下，这意味着把该行的已更新版本锁住并返回给客户端。

• SELECT .... ON CONFLICT DO UPDATE:是 PostgreSQL 中用于处理插入数据时可能发生的唯一约束冲突的语法。它通常与 INSERT 语句一起使用，允许在插入数据时，如果遇到唯一约束冲突（例如主键或唯一索引冲突），执行更新操作而不是抛出错误。例如：

INSERT INTO users (username, email, login_count)
VALUES ('alice', 'alice@example.com', 1)
ON CONFLICT (username) DO UPDATE
SET email = EXCLUDED.email,
    login_count = users.login_count + 1;

带有ON CONFLICT DO UPDATE子句的 INSERT行为类似。在读已提交模式，要插入的 每一行将被插入或者更新。除非有不相干的错误出现，这两种结果之一是肯定 会出现的。如果在另一个事务中发生冲突，并且其效果对于INSERT 还不可见，则UPDATE子句将会影响那个行，也就是说会发生不可重复读，不会发生幻读。

8.4可重复读隔离级别

在这个级别下，事务中的查询只能看到事务开始时的一个数据快照，即使其他事务在此期间提交了修改，也不会影响当前事务看到的数据。但是能够查询到在此次事务之前执行的更新，即使没有提交。

但是需要注意的，如果在事务执行前执行的修改和删除，那么当前事务将等待这些操作。

8.5可序列化隔离级别

可序列化个立即被提供了严格的事务隔离，序列化隔离级别就像给事务排好队，确保它们不会互相干扰，适合对数据一致性要求极高的场景，但可能会影响性能。

8.6显式锁定

PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问。 同样，大多数PostgreSQL命令会自动加上恰当的锁以保证被引用的表在命令的执行过程中不会以一种不兼容的方式删除或修改。

要检查在一个数据库服务器中当前未解除的锁列表，可以使用 pg_locks系统视图。

8.6.1表级锁

名称	描述
ACCESS SHARE	只与ACCESS EXCLUSIVE锁模式冲突。SELECT命令在被引用的表上获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获得这种锁模式。
ACCESS EXCLUSIVE	与所有模式的锁冲突（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE）。这种模式保证持有者是访问该表的唯一事务。由ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令获取。 很多形式的ALTER INDEX和ALTER TABLE也在这个层面上获得锁（见ALTER TABLE）。这也是未显式指定模式的LOCK TABLE命令的默认锁模式。
ROW SHARE	与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上取得一个这种模式的锁
ROW EXCLUSIVE	与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。命令UPDATE、DELETE和INSERT在目标表上取得这种锁模式（加上在任何其他被引用表上的ACCESS SHARE锁）。通常，这种锁模式将被任何修改表中数据的命令取得。
SHARE UPDATE EXCLUSIVE	与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发模式改变和VACUUM运行的影响。由VACUUM（不带FULL）、ANALYZE、 CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、 CREATE STATISTICS以及某些ALTER INDEX和 ALTER TABLE的变体获得
SHARE	与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发数据改变的影响。由CREATE INDEX（不带CONCURRENTLY）取得。
SHARE ROW EXCLUSIVE	与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。由CREATE TRIGGER和某些形式的 ALTER TABLE所获得
EXCLUSIVE	与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式只允许并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。由REFRESH MATERIALIZED VIEW CONCURRENTLY获得。

CREATE INDEX 不加CONCURRENTLY，创建索引时会锁定表，期间其他操作（如插入、更新）会被阻塞，直到索引创建完成。加上CONCURRENTLY，创建索引时不会锁表，其他操作可以继续执行，但创建速度会变慢。

只有一个ACCESS EXCLUSIVE锁阻塞一个SELECT（不带FOR UPDATE/SHARE）语句。

一旦被获取，一个锁通常将被持有直到事务结束。 但是如果在建立保存点之后才获得锁，那么在回滚到这个保存点的时候将立即释放该锁。 这与ROLLBACK取消保存点之后所有的影响的原则保持一致。

8.6.2行级锁

注意一个事务可能会在相同的行上保持冲突的锁，甚至是在不同的子事务中。 但是除此之外，两个事务永远不可能在相同的行上持有冲突的锁。行级锁不影响数据查询，它们只阻塞对同一行的写入者和加锁者。 行级锁在事务结束时或保存点回滚的时候释放，就像表级锁一样。

名称	描述
FOR UPDATE	FOR UPDATE会导致由SELECT语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除，一直到当前事务结束。也就是说其他尝试UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。反过来
FOR NO KEY UPDATE	行为与FOR UPDATE类似，不过获得的锁较弱：这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。
FOR SHARE	行为与FOR NO KEY UPDATE类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE。
FOR KEY SHARE	行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。

8.6.3死锁

显式锁定的使用可能会增加死锁的可能性，死锁是指两个（或多个）事务相互持有对方想要的锁。例如，如果事务 1 在表 A 上获得一个排他锁，同时试图获取一个在表 B 上的排他锁， 而事务 2 已经持有表 B 的排他锁，同时却正在请求表 A 上的一个排他锁，那么两个事务就都不能进行下去。PostgreSQL能够自动检测到死锁情况并且会通过中断其中一个事务从而允许其它事务完成来解决这个问题（具体哪个事务会被中断是很难预测的，而且也不应该依靠这样的预测）。

8.6.4咨询锁

PostgreSQL 中的咨询锁（Advisory Lock）是一种应用程序级别的锁机制，允许开发者在数据库层面实现自定义的锁定逻辑。与表锁或行锁不同，咨询锁不直接关联数据库中的特定数据，而是由应用程序根据业务需求来决定何时加锁和释放锁。
基于整数的咨询锁：

使用一个或两个整数作为锁的标识符。常用函数：

• pg_advisory_lock(key)：加锁，阻塞直到锁可用。
• pg_advisory_unlock(key)：释放锁。
• pg_try_advisory_lock(key)：尝试加锁，如果锁不可用则立即返回 false。
• pg_advisory_lock_shared(key)：加共享锁。
• pg_advisory_unlock_shared(key)：释放共享锁。

基于字符串的咨询锁：使用一个字符串作为锁的标识符。常用函数：

• pg_advisory_lock(text)：加锁。
• pg_advisory_unlock(text)：释放锁。
• pg_try_advisory_lock(text)：尝试加锁

9.并行查询

PostgreSQL 的并行查询是一种通过利用多核 CPU 来加速查询执行的机制。它可以将某些查询操作（如扫描、连接、聚合等）分解为多个并行任务，从而显著提高查询性能。以下是关于 PostgreSQL 并行查询的详细介绍：

PostgreSQL 的并行查询通过以下方式实现：

• 工作进程（Worker Processes）：主进程（Leader Process）将任务分配给多个后台工作进程（Worker Processes），这些进程并行执行任务。
• 数据分片：将数据分成多个部分，每个工作进程处理一部分数据。
• 结果合并：工作进程将处理结果返回给主进程，主进程负责合并结果并返回给客户端。

10.SQL优化

10.1不要使用select *

10.2小表驱动大表

10.3为group by添加索引

如果 GROUP BY 的列没有索引，数据库需要进行全表扫描，导致查询变慢。即使有索引，如果查询涉及多个列或复杂条件，索引可能无法有效使用。

10.4使用批量插入

10.5使用limit

10.6使用Union all代替Union

10.7join表不易过多

11.注意点

11.1postgresql中的类if-else语句

CASE WHEN 条件
THEN
	符合条件的值
ELSE
	不符合条件的值
END CASE

11.2docker进入postgresql

docker exec -it 容器名 /bin/bash; #进入docker
su postgres; #切换为postgres用户
psql -U 角色名 -d 数据库名称; #进入数据库

11.3修改已挂载视图表字段的属性

• 创建函数

create table if not exists deps_saved_ddl
(
  deps_id serial primary key,
  deps_view_schema name,
  deps_view_name name,
  deps_ddl_to_run text
);

create or replace function deps_save_and_drop_dependencies(p_view_schema name, p_view_name name) returns void as
$$
declare
  v_curr record;
begin
for v_curr in
(
  select obj_schema, obj_name, obj_type from
  (
  with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
  (
    select p_view_schema, p_view_name, null::char, 0
    union
    select dep_schema::name, dep_name::name, dep_type::char, recursive_deps.depth + 1 from
    (
      select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
	  rwr_cl.relkind dep_type,
      rwr_nsp.nspname dep_schema,
      rwr_cl.relname dep_name
      from pg_depend dep
      join pg_class ref_cl on dep.refobjid = ref_cl.oid
      join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
      join pg_rewrite rwr on dep.objid = rwr.oid
      join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
      join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
      where dep.deptype = 'n'
      and dep.classid = 'pg_rewrite'::regclass
    ) deps
    join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
    where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
  )
  select obj_schema, obj_name, obj_type, depth
  from recursive_deps
  where depth > 0
  ) t
  group by obj_schema, obj_name, obj_type
  order by max(depth) desc
) loop

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select distinct p_view_schema, p_view_name, indexdef
  from pg_indexes
  where schemaname = v_curr.obj_schema
  and tablename = v_curr.obj_name;

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select distinct tablename, rulename, definition
  from pg_rules
  where schemaname = v_curr.obj_schema
  and tablename = v_curr.obj_name;

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, 'COMMENT ON ' ||
  case
  when c.relkind = 'v' then 'VIEW'
  when c.relkind = 'm' then 'MATERIALIZED VIEW'
  else ''
  end
  || ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = 0
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
  from pg_class c
  join pg_attribute a on c.oid = a.attrelid
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || quote_ident(table_name) || ' TO ' || grantee
  from information_schema.role_table_grants
  where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;

  if v_curr.obj_type = 'v' then
    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name) || ' AS ' || view_definition
    from information_schema.views
    where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
  elsif v_curr.obj_type = 'm' then
    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name) || ' AS ' || definition
    from pg_matviews
    where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
  end if;

  execute 'DROP ' ||
  case
    when v_curr.obj_type = 'v' then 'VIEW'
    when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
  end
  || ' ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name);

end loop;
end;
$$
LANGUAGE plpgsql;

create or replace function deps_restore_dependencies(p_view_schema name, p_view_name name) returns void as
$$
declare
  v_curr record;
begin
for v_curr in
(
  select deps_ddl_to_run
  from deps_saved_ddl
  where deps_view_schema = p_view_schema and deps_view_name = p_view_name
  order by deps_id desc
) loop
  execute v_curr.deps_ddl_to_run;
end loop;
delete from deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;

• 执行修改sql

select deps_save_and_drop_dependencies('模式名', '表名');
alter table 表名 alter 字段名 type 字段大小;
select deps_restore_dependencies('模式名', '表名');

• 例子

SELECT deps_save_and_drop_dependencies ( 'public', 'object_instance' );
ALTER TABLE object_instance ALTER instance_number TYPE VARCHAR ( 255 );
SELECT deps_restore_dependencies ( 'public', 'object_instance' );

11.4docker中的postgresql，超级管理员密码忘记了

• 更改连接方式：在pg_hba.conf中修改如下设置，由md5改为trust
  
• 查看超级管理员账户名称相同的数据库是否存在，如果不存在，创建一个与超级管理员名称相同的数据库
• 执行shell

docker exec -it plm-postgresql /bin/bash #进入docker
su postgres #使用postgres用户
psql -U 超级管理员用户名 #进入超级管理员对应的数据
alter user 超级管理员用户名 with password 'XXXXXXX' #修改密码

11.5docker中的postgresql短期回滚数据（恢复误删除命令，需要保证wal文件完整）

docker exec -it plm-postgresql /bin/bash #进入docker
su postgres #使用postgres用户
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); # 查看当前事务号
pg_waldump 000000010000001600000049 00000001000000160000004D -r transaction  | grep  2023-07-28 | head -n 100 #查询你需要的事务号
pg_ctl stop -D /var/lib/postgresql/data #关闭postgres，Docker不行，需要删除data下的postmaster.pid文件
pg_resetwal -x 41574998 -f -D /var/lib/postgresql/data #回滚事务

11.6清空数据库

//删除public模式以及模式里面所有的对象
DROP SCHEMA public CASCADE;
//创建public模式
CREATE SCHEMA public;

11.7ON CONFLICT

ON CONFLICT 是 PostgreSQL 提供的一个功能，用于处理插入操作中可能出现的约束冲突，比如违反唯一索引或主键约束。这个功能允许你在插入操作中指定一个冲突解决策略，而不是默认的失败并抛出错误。

11.7.1 DO NOTHING

• 当发生冲突时，不执行任何操作，即不插入也不更新。
• 这可以用来确保数据的唯一性，如果尝试插入的数据已经存在，则不做任何改变

INSERT INTO your_table (id, column1) VALUES (1, 'value')
ON CONFLICT (id) DO NOTHING;

11.7.2DO UPDATE：

• 当发生冲突时，更新现有的行。
• 你可以指定更新哪些列以及如何更新它们。

INSERT INTO your_table (id, column1, column2) VALUES (1, 'new_value1', 'new_value2')
ON CONFLICT (id) DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2;

11.7.3条件更新

• 在 DO UPDATE 后面添加 WHERE 子句，根据条件来决定是否更新。
• 这允许你基于某些逻辑来决定是否应该更新记录。

INSERT INTO your_table (id, column1) VALUES (1, 'value')
ON CONFLICT (id) DO UPDATE SET column1 = EXCLUDED.column1 WHERE your_table.column1 <> EXCLUDED.column1;

11.8生成UUID

postgres@048da7c061a8:/$ psql postgres -d plm-doc
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

plm-doc=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION
plm-doc=# SELECT uuid_generate_v4();

12.搭建Postgresql集群（基于Docker）

本方案采用的是pgpool+postgresql搭建的，使用的是bitnami的镜像。Bitnami 是一家专注于简化开源软件部署和管理的公司，成立于 2011 年，2019 年被 VMware 收购，现为 VMware 旗下品牌。

搭建视频参考链接：https://www.bilibili.com/video/BV1zm4y1M7NR/?spm_id_from=333.337.search-card.all.click&vd_source=bf0bf19ed423304b05f9b54d0c176892
官方文档参考链接：https://github.com/bitnami/containers/blob/main/bitnami/postgresql-repmgr/docker-compose.yml

12.1设置hosts

每台机器上

[root@pg-0 pg-docker]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.172.105 pg-0
192.168.172.106 pg-1
192.168.172.107 pg-2

12.2搭建Master

• 上传文件夹

将master文件夹移动到服务器上

• 给文件赋予权限

chmod -R g+rwX ./postgresql/conf/ && chgrp -R root ./postgresql/conf/ && chmod -R g+rwX ./postgresql/logs/ && chgrp -R root ./postgresql/logs/ && chmod -R g+rwX ./postgresql/pgdata/ && chgrp -R root ./postgresql/pgdata/ && chmod -R g+rwX ./pgpool/conf/ && chgrp -R root ./pgpool/conf/

• 安装postgresql和pgpool

docker-compose up -d 

• 出现如图所示，则pg安装成功
  

• 出现如图所示，则pgpool安装成功
  

12.3搭建Slave

和Master一样，分别将Slave1文件夹和Slave2文件夹上传到对应的服务器，继续上图所示

12.4基础命令

12.4.1查看节点信息

在pgpool上执行show pool_nodes;

13物理机部署

13.1安装

• 1.下载安装yum源

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• 2.安装postgresql

yum install -y postgresql13-server

• 3.初始化并启动postgresql

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
R

• 4.将Postgresql的bin命令加入环境变量

vi /etc/profile
在文件末尾添加以下内容：
export PATH="/usr/pgsql-13/bin:$PATH"
使更改立即生效
source /etc/profile

• 5.允许所有用户连接

vi /var/lib/pgsql/13/data/postgresql.conf
# 找到 listen_addresses 配置项，并将其设置为'*'

vi /var/lib/pgsql/13/data/pg_hba.conf
# 添加如下行以允许所有机器使用密码连接：
host all all 0.0.0.0/0 md5

• 6.重启postgresql13

systemctl restart postgresql-13

13.2常用目录

• /var/lib/pgsql/13：PG的数据
• /usr/pgsql-13：PG的命令和第三方库

13.3修改postgres密码

[root@middle1 ~]# su - postgres
上一次登录：六 3月 15 14:15:30 CST 2025pts/1 上
-bash-4.2$ psql
psql (13.20)
输入 "help" 来获取帮助信息.

postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE

13.4配置主从复制

13.4.1修改主库

• 修改pg配置

sudo vi /var/lib/pgsql/13/data/postgresql.conf
配置以下参数：
listen_addresses = '*'                  # 允许所有 IP 连接
wal_level = replica                     # 启用 WAL 日志
max_wal_senders = 5                     # 允许最多 5 个复制连接
wal_keep_size = 32                  # 保留的 WAL 日志段数
hot_standby = on                        # 从库可以用于查询

• 修改 pg_hba.conf

sudo vi /var/lib/pgsql/13/data/pg_hba.conf
# 添加以下内容，允许从库连接
host    replication     all             192.168.1.20/32         md5

• 重启主库服务

sudo systemctl restart postgresql-13

• 创建复制用户

- 1.登录 PostgreSQL
sudo -u postgres psql
- 2.创建复制用户
CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'repl_password';
- 3.退出 PostgreSQL：
\q

13.4.2修改从库

• 如果从库已经初始化，需要先停止服务并清空数据目录：

sudo systemctl stop postgresql-13
sudo rm -rf /var/lib/pgsql/13/data/*

• 使用 pg_basebackup 初始化从库

sudo -u postgres pg_basebackup -h 192.168.1.10 -U repl_user -D /var/lib/pgsql/13/data -P -R --wal-method=stream
输入主库上创建的复制用户REPLICATION的密码

-h：主库 IP 地址。
-U：复制用户。
-D：从库数据目录。
-P：显示进度。
-R：生成 standby.signal 文件并配置 primary_conninfo。
--wal-method=stream：使用流复制。

• 编辑从库的 postgresql.conf：

sudo vi /var/lib/pgsql/13/data/postgresql.conf
# 确保以下参数已正确配置：
hot_standby = on

• 启动从库服务

sudo systemctl start postgresql-13

13.4.3验证主从复制

• 在主库上检查复制状态

- 登录主库
sudo -u postgres psql
- 查询复制状态：
SELECT * FROM pg_stat_replication;
# 如果看到从库的信息（如 client_addr 为 192.168.1.20），表示复制已成功

• 在从库上检查复制状态

- 登录从库
sudo -u postgres psql
- 查询复制状态
SELECT * FROM pg_stat_wal_receiver;
# 如果看到主库的信息（如 sender_host 为 192.168.1.10），表示复制已成功。

• 测试主从同步

- 在主库上创建测试数据：
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_table (name) VALUES ('test1'), ('test2');
- 在从库上查询数据：
SELECT * FROM test_table;
# 如果从库能够查询到主库插入的数据，表示主从复制正常工作。

14.二进制安装postgresq13(centos7)

• 1.关闭防火墙

systemctl stop firewalld

• 2.下载依赖

sudo yum groupinstall -y "Development Tools"
sudo yum install -y readline-devel zlib-devel

• 3.创建目录，下载postgresql13源代码

mkdir /purvar/pgsql13
cd /purvar/pgsql13
yum install wget
wget https://ftp.postgresql.org/pub/source/v13.21/postgresql-13.21.tar.gz
tar -xvf postgresql-13.21.tar.gz
cd postgresql-13.21

• 4.配置并编译安装：

./configure --prefix=/purvar/pgsql13
make
sudo make install

• 5.创建用户

groupadd postgres
useradd -g postgres postgres

• 6.创建数据目录并初始化

sudo mkdir -p /purvar/pgsql13/data
sudo chown postgres:postgres /purvar/pgsql13 -R
sudo -u postgres /purvar/pgsql13/bin/initdb -D /purvar/pgsql13/data

• 7.创建systemd服务文件：

su - root
sudo tee /etc/systemd/system/postgresql-13.service <<EOF
[Unit]
Description=PostgreSQL 13 database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/purvar/pgsql13/data
ExecStart=/purvar/pgsql13/bin/pg_ctl -D \${PGDATA} start
ExecStop=/purvar/pgsql13/bin/pg_ctl -D \${PGDATA} stop
ExecReload=/purvar/pgsql13/bin/pg_ctl -D \${PGDATA} reload

[Install]
WantedBy=multi-user.target
EOF

• 8.启动服务

sudo systemctl daemon-reload
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

• 9.配置环境变量

echo 'export PATH=/purvar/pgsql13/bin:$PATH' | sudo tee /etc/profile.d/pgsql13.sh
source /etc/profile.d/pgsql13.sh

• 10.验证安装

sudo -u postgres /purvar/pgsql13/bin/psql -c "SELECT version();"

• 11.修改密码

[root@middle1 ~]# su - postgres
上一次登录：六 3月 15 14:15:30 CST 2025pts/1 上
-bash-4.2$ psql
psql (13.20)
输入 "help" 来获取帮助信息.

postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE

• 12.修改配置,允许所有用户连接

cd /purvar/pgsql13/data/
vi postgresql.conf
# 找到 listen_addresses 配置项，并将其设置为'*'

vi pg_hba.conf
# 添加如下行以允许所有机器使用密码连接：
host all all 0.0.0.0/0 md5

• 13.重启postgresql13

su - root
systemctl restart postgresql-13

15.数据迁移

• 导出数据

sudo -u postgres pg_dumpall > full_backup.sql

• 导入数据

sudo -u postgres psql -f full_backup.sql

16.同一个服务，将一个数据库的内容复制到另一个空的数据库

pg_dump -U postgres 源数据库 | psql -U postgres 目标数据库;