PostgreSQL官网：https://www.postgresql.org
连接数据库
psql -h host -p port -U username -d database
- host：数据库主机名或 IP 地址。
- port：数据库端口号（默认为 5432）。
- username：连接数据库所使用的用户名。
- database：数据库名称。
断开psql客户端
\q或按Ctrl+D组合键
清空屏幕
\! clear;
查看支持的所有命令
?
查看所有sql命令帮助
\h
查看某个具体的sql命令
\h sql命令
查看所有数据库
\l或\list
切换数据库
\c database
查看当前数据库所有表、视图、序列
\d
查看表定义
\d 表名
查看表大小
\dt+ 表名
查看索引
\di
查看索引大小
\di+ 索引名
查看表空间
\db
查看所有schema
\dn
查看所有角色或用户
\du或\dg
设置查询结果的输出模式(相当于mysql的\G)
\x
导出数据库
pg_dump 是 PostgreSQL 自带的备份工具，可以用于导出数据库和表的数据和结构。
pg_dump -h host -p port -U username -d database -f database.sql
- host：数据库主机名或 IP 地址。
- port：数据库端口号（默认为 5432）。
- username：连接数据库所使用的用户名。
- database：要导出的数据库名称。
- database.sql：导出的 SQL 脚本文件。
--inserts：将数据转储为INSERT命令，而不是COPY
如果只想导出数据库的结构而不包含数据，可以使用以下命令：
pg_dump -h host -p port -U username -d database -s -f database.sql
-s 参数表示只导出数据库的结构而不包括数据。
如果只想导出数据库的数据而不包含结构，可以使用以下命令：
pg_dump -h host -p port -U username -d database -a -f database.sql
-a 参数表示只导出数据而不导出表结构。
导出某张表
pg_dump -h host -p port -U username -d database -t table_name -f table_name.sql
- host：数据库主机名或 IP 地址。
- port：数据库端口号（默认为 5432）。
- username：连接数据库所使用的用户名。
- database：要导出的数据库名称。
- table_name：要导出的表名。
- table_name.sql：导出的 SQL 脚本文件。
如果只想导出表的数据而不导出表结构，可以使用以下命令：
pg_dump -h host -p port -U username -d database -t table_name -a -f table_name.sql
-a 参数表示只导出数据而不导出表结构。
如果只想导出表的结构而不导出表数据，可以使用以下命令：
pg_dump -h host -p port -U username -d database -t table_name -s -f table_name.sql
-s 参数表示只导出结构而不包括数据。
如果想同时导出多张表，用多个-t 表名即可。
pg_dump -h host -p port -U username -d database -t table_a_name -t table_b_name
-s -f table_name.sql
将两个经纬度字段（latitude和longitude）转换为Geometry并赋值给一个新的字段location：
UPDATE your_table SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE condition;
其中，your_table表示要更新的表名，condition是指定需要更新记录的条件。这里使用了ST_MakePoint函数来创建点对象，参数分别是经度和纬度；然后通过ST_SetSRID函数设置空间参考系统标识符为4326（WGS84地理坐标系）。最后，将生成的Geometry对象赋值给location字段。传入的geometry参数的 SRID 必须是 4326 或 4490 ，否则返回null。
geom转换成经纬度
SELECT ST_AsText(geom) FROM table;
生成UUID
UUID（Universal Unique Identifier）或者 GUID（Globally Unique Identifier）是一个 128 比特的数字，可以用于唯一标识每个网络对象或资源。由于它的生成机制，一个 UUID 可以保证几乎不会与其他 UUID 重复，因此常常用于生成数据库中的主键值。
PostgreSQL 提供了一个用于加/解密的扩展模块 pgcrypto，其中的 gen_random_uuid() 函数可以用于返回一个 version 4 的随机 UUID。首先，输入以下命令启用该模块（gen_random_uuid() 从 PostgreSQL 13 开始成为了一个内置函数）：
CREATE EXTENSION pgcrypto;
生成uuid:
SELECT gen_random_uuid();
该函数返回的数据类型为 uuid。如果想要生成没有中划线（-）的 UUID 字符串，可以使用 REPLACE 函数：
SELECT replace(gen_random_uuid()::text,'-','');
根据关联表更新另一张表数据
关联表形式：
UPDATE 待更新表 t1
SET 待更新表字段 = t2.字段,
待更新表字段 = t2.字段
FROM
关联表 t2
WHERE
t1.关联字段 = t2.关联字段
--例：街镇表根据编号关联地区表，更新街镇名称
UPDATE town t1
SET town_name = t2."name"
FROM
region t2
WHERE
t1.town_code = t2.code
子查询形式：
UPDATE 待更新表 t1
SET (待更新表字段, 待更新表字段) =
(SELECT t2.字段, t2.字段 FROM 关联表 t2 WHERE t1.关联字段 = t2.关联字段)
注意点：
1.SET后的待更新字段不要加表别名（如t1），会识别成列名，报（column "表别名" of relation "待更新表名" does not exist）错误。
2.子查询形式字段要一一对应，顺序不能乱。
INSERT SELECT
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
target_table是要插入数据的目标表，source_table是要从中选择数据的源表。您可以通过选择源表中的列，并将它们映射到目标表的对应列来指定要插入的数据，还可以添加WHERE子句来过滤源表中的数据。
将查询结果导出csv文件
copy (select * from test where create_time >= '2024-01-01') to '/tmp/data/test.csv' with csv header;
判断字符串是否是数字
PostgreSQL支持正则表达式，通过~操作符可以匹配字符串是否符合特定的模式，要检查一个字符串是否全由数字组成，可以使用^[0-9]+$这样的正则表达式:
^表示字符串的开始
[0-9] 表示任何一个数字字符
+表示前面的字符（这里是数字）出现一次或多次
$ 表示字符串的结束
示例：
SELECT column_name
FROM table_name
WHERE column_name ~ '^[0-9]+$';
逻辑删除设置唯一索引
假设users表设置未删除的username唯一，del_flag（0-未删除，1-已删除）
CREATE UNIQUE INDEX uk_users_username ON users (username) WHERE del_flag = 0;
