概念
Postgresql是一个基于Postgres的对象关系数据库管理系统(ORDBMS)，由加州大学伯克利分校计算机学系开发，是原始Berkeley代码的衍生。Postgresql支持很大一部分的SQL标注标准和提供许多现代特性。
部署
# POSTGRES_USER不设置默认是 postgres
# POSTGRES_DB不设置默认跟POSTGRES_USER名称一致,设置小写遵循数据库命名规范。
# warnning: 如果/blessing/postgresql/data下有pgdata数据了。
# 删除容器重新启动新容器。数据库名称将不会有变化。
mkdir -p /blessing/postgresql/data
docker run -itd \
--restart=always \
-p 5432:5432 \
--name postgres \
-e POSTGRES_PASSWORD=DawnSilverGravel \
-e POSTGRES_DB=silver_gravel \
-e POSTGRES_USER=SilverGravel \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v /blessing/postgresql/data:/var/lib/postgresql/data \
postgres:15
# 如果是安装postgis扩展 postgres:15 -> postgis/postgis:15-3.4
部分数据类型与Java类型的关系
| 类型名 | 别名 | 描述 | Java对应类型 |
|---|---|---|---|
| smallint | int2 | 短整型 | Short |
| smallserial | serial2 | 自动增长短整型 | Short |
| integer | int、int4 | 整型 | Integer |
| serial | serial4 | 自动增长整型 | Integer |
| bigint | int8 | 长整型 | Long |
| bigserial | serial8 | 自动增长长整型 | Long |
| real | float4 | 单精度 | Float |
| double precision | float8 | 双精度 | Double |
| numeric[(p,s)] | decimal[(p,s)] | 可选择精度类型 | BigDecimal |
| character[(n)] | char[()] | 定长字符串 | String |
| character varying[(n)] | varchar[(n)] | 可变长度字符串 | String |
| text | 可变长度字符串 | String | |
| boolean | bool | 逻辑类型true/false | Boolean |
| time | 时间（不包含时区） | LocalTime | |
| date | 日期 | LocalDate | |
| timestamp | 日期时间（不包含时区） | LocalDateTime | |
| Array 如 integer[] | 数组类型 | Integer[] | |
| geometry | 几何类型 | String (存取数据需要指定的函数进行转换) |
Table
约束 Constraint
数据类型是一种限制可存储在表中的数据类型的一种方法，而这种方法提供的约束过于粗略，为此，SQL 允定义列和表的约束， 约束对表中的数据进行更多控制， 如果用户尝试将不合规则的数据存储在列中，这会违反约束，会引发错误。 即使该值来自默认值，这也适用价值定义 。
| 约束 | 描述 |
|---|---|
| not null | 值非空约束 |
| unique | 唯一约束确保一列或一组列中包含的数据在各个列中是唯一的表中的所有行，可有空值 |
| check | 检查约束，规定值的区间 |
| primary key | 主键约束，指示一列或一组列可以用作表中行的唯一标识符，值唯一且不可为空值 |
| foreign key | 外键约束，指定一列（或一组列）中的值必须与出现在另一个表的某些行中的值,指定值唯一 |
| exclusion | 排除约束确保如果任意两行在指定的列或表达式上进行比较使用指定的运算符，这些运算符比较中至少有一个将返回 false 或 null |
创建表
create table if not exists t_product
(
id integer primary key,
product_name varchar(15) unique not null check ( product_name != '' ),
create_time timestamp not null
);
create table if not exists t_product
(
id integer,
product_name varchar(15),
product_code varchar(10) not null default '',
create_time timestamp not null,
primary key (id), -- 自定义命名：constraint product_id_key primary key(id)
constraint name_code_unique unique (product_name, product_code),-- 为一组唯一约束命名，primary key,check,foreign key 类似
constraint name_check check ( product_name != '')-- check ( product_name!='') 同时自定义命名
);
-- 外键存在，主表删除数据需要将关联删除数据才可以删除
-- 在创建外键时，可以使用references t_product (id) on delete cascade
-- 关联表的数据同时删除
create table if not exists t_cascade
(
id serial primary key,
product_id integer references t_product (id) on delete cascade -- 可级联删除表数据
);
create table if not exists t_restrict
(
id serial primary key,
product_id integer,
constraint foreign_product_id foreign key (product_id) references t_product (id) on delete restrict -- 不可级联删除表数据
);
删除表
drop table if exists t_product cascade; -- 有外键需要使用 cascade 删除外键约束
drop table if exists t_cascade;
注释
-- 添加数据库注释
comment on database postgres is '学习数据库';
-- 添加表注释
comment on table t_product is '产品表';
-- 添加列注释
comment on column t_product.id is '主键ID';
修改 Table
-- 添加列并设置非空约束和默认值
alter table t_product
add column remark varchar(255) not null default 'SilverGravel';
-- 修改默认值
alter table t_product
alter column remark set default '拔剑四顾心茫然';
-- 添加约束,添加检查约束
alter table t_product
add constraint check_string check (t_product.remark != '');
-- 重命名约束
alter table t_product
rename constraint check_string to check_str;
-- 重命名 remark 为 description
alter table t_product
rename remark to description;
-- 修改列类型， description类型
alter table t_product
alter column description type varchar(20);
-- 删除约束 删除检查约束
alter table t_product
drop constraint check_str;
-- 删除默认值
alter table t_product
alter column description drop default;
-- 删除列
alter table t_product
drop column description;
-- 重命名 table
alter table t_product
rename to t_product_1;
alter table t_product_1
rename to t_product;
模式 Schema
Postgresql 有模式(schema)的概念，一个数据可有一个或多个模式，模式又包含表、其他类型的命名对象，包括数据类型、函数和运算符。 相同的对象名称可以在不同的模式中使用而不发生冲突。模式不严格分离，用户可以使用。数据库默认模式为
public，模式主要应用于:
- 允许多个用户使用同一个数据库而不互相干扰
- 将数据库对象组织成逻辑组，使其更易于管理
- 可以将第三方程序放入一个单独的模式中，这样就不会产生命名冲突
-- 查看当前模式，默认为public
-- 默认为 "$user", public
-- 其中 $user 当前用户同名
SHOW search_path;
-- 设置模式，第一个元素作为之后添加的表等对象的默认模式
-- 会话级别，开了另一个控制台就会使用默认的 search_path
-- 如果第一个元素模式不存在，则顺延到下一个
SET search_path TO silver,public;
create schema silver;
create table t_user
(
id serial primary key,
name varchar(45)
);
drop schema if exists silver;
-- 如果模式下有对象如表，则需要级联删除
drop schema if exists silver cascade;
CURD
alter table t_product
add column if not exists remark varchar(255) not null default 'SilverGravel';
alter table t_product
add column if not exists product_name varchar(10) not null default '';
-- insert
-- 指定顺序
insert into t_product(id, product_code, product_name, remark, create_time)
values (5,'code','古诗三百首','毕竟几人真得鹿',current_timestamp)
returning *;
-- 按字段顺序
insert into t_product
values (6,'古诗三百首','Silver',current_timestamp,'梦回吹角连营')
returning *;
create table if not exists t_extra_data
(
id serial primary key,
product_name varchar(15),
product_code varchar(10) not null default '',
create_time timestamp not null,
extra_comment varchar(45)
);
insert into t_extra_data
values (9,'产品7','code',current_timestamp,'金戈铁马');
insert into t_extra_data
values (11,'产品8','code',current_timestamp,'如今变却故人心');
-- 从其他表中获取数据并存入，相关类型需要对应
-- 这里t_extra_data 与 t_product 类型可转换
insert into t_product
select * from t_extra_data;
insert into t_extra_data
values (12, '产品9', 'code', current_timestamp, '却道故人心易变');
insert into t_extra_data
values (13, '产品10', 'code', current_timestamp, '铁马冰河入梦来');
-- 修改字段顺序
insert into t_product
(id, product_code, product_name, remark, create_time)
select id, 'code' as product_code,product_name,extra_comment, current_timestamp - interval '1' day
from t_extra_data where id >11
returning *;
-- update SQL标准
update t_product
set remark = '不知终日梦为鱼'
where id = 9
-- 返回更新后的数据，*表示返回全部字段
-- 不需要指定字段则不用编写 returning
-- 返回数据可为空
returning *;
-- select 可使用大部分SQL标准语句，如left join..on 等等
select count(remark), remark
from t_product
where length(remark) > 3
group by remark
having count(remark) > 0
order by count(remark) desc
limit 3 offset 1;
-- 模糊搜索'_'、'%'匹配一个或多个字符
select * from t_product
where remark like '1%%'
escape '1'; -- 将字符'1'作为转义字符
-- delete SQL标准
delete
from t_product
where id = 5
-- 返回删除的行数据，*表示返回全部字段
-- 返回数据可为空
returning *;
条件表达式
Postgreql 提供几个符合SQL定义的条件表达式
| 用法 | 描述 |
|---|---|
| CASE WHEN condition THEN result [WHEN...] [ELSE result] END | 类似if...else，java switch(arges){case...} |
| COALESCE(value,...) | 返回参数列表第一个不为空的参数，参数全为空则返回空 |
| NULLIF(value1, value2) | 将匹配value2的value1的值置空 |
| GREATEST(value,...) | 返回参数列表中的最大值，列表为空返回空 |
| LEAST(value,...) | 返回参数列表中的最小值，列表为空返回空 |
create table t_event
(
id integer primary key,
name varchar(45),
status smallint
);
insert into t_event
values (1, '学习Postgresql', 1);
insert into t_event
values (2, '学习RocketMQ', 0);
insert into t_event
values (3, '学习Websocket', 2);
insert into t_event
values (4, '学习财务知识', null);
insert into t_event
values (5, '学习Netty', 2);
select name,
case status
when 0 then '未开始'
when 1 then '进行中'
when 2 then '已结束'
else '未知状态' end
as status
from t_event;
select name,
case
when status = 0 then '未开始'
when status = 1 then '进行中'
when status = 2 then '已结束'
else '未知状态' end
as status
from t_event;
-- 空值赋 0
select name, coalesce(status, 0)
from t_event;
select coalesce('1', null, '2');
-- 将 status 为 1 的值赋空
select name, nullif(status, 1)
from t_event;
select name, greatest(status, 2)
from t_event;
select greatest('1', null, '2');
select greatest(null, null);
select name, least(status, 1)
from t_event;
select least('1', null, '2');
select least(null, null);
函数 Function
更多函数查看官方文档：PostgreSQL: Documentation
字符串函数
| 函数 | 描述 |
|---|---|
| length() | 获取字符长度 |
| lower() | 英文字母小写 |
| upper() | 英文字母大写 |
| octet_length() | 字节长度（8bit） |
| bit_length() | bit长度 |
| trim() | 开头、结尾或两端（默认为 BOTH）删除仅包含字符默认为空格中的字符的最长字符串，ltrim(),btrim(),rtrim() |
| concat() | 连接多个字符串成一个字符串 |
| left() | 从左边裁剪指定数量字符的子串 |
| right() | 从右边裁剪出指定数量字符的子串 |
| substring() | 获取子串 |
| position() | 获取子串出现的下标 |
| repeat() | 生成指定数量的重复字符串 |
| replace() | 替换字符串 |
| reverse() | 翻转字符串 |
| string_to_array() | 字符串转数组 |
select length('大漠孤烟直');
select lower('GRAVEL');
select upper('silver');
select octet_length('扶摇直上九万里');
select bit_length('扶摇直上九万里');
select trim(' 大鹏一日同风起 ');
select trim(both ' 一风' from ' 大鹏一日同风起 ');
select trim(leading ' 一风' from ' 大鹏一日同风起 ');
select trim(trailing ' 一' from ' 大鹏一日同风起 ');
select ltrim(' 大鹏一日同风起 ');
select rtrim(' 大鹏一日同风起 ');
select btrim(' 大鹏一日同风起 ');
-- NULL值忽略
select concat('这是一封离别的信', NULL, '', ' Let it Going');
select substring('行到水穷处，坐看云起时' for 5);
select substring('行到水穷处，坐看云起时' from 7 for 5);
select substring('行到水穷处，坐看云起时' from 6);
select left('行到水穷处，坐看云起时', 5);
select right('行到水穷处，坐看云起时', 5);
-- 第一个子串下标
select position('颜如玉' in '书中自有黄金屋，书中自有颜如玉');
select repeat('啊巴', 4);
select replace('纵有狂爆拔地起，我亦乘爆破万里', '爆', '风');
select reverse('霜为露白，苍苍葭蒹');
select string_to_array('海到无边天作岸，上登绝顶我为峰。南朝四百八十寺，多少楼台烟雨中。寄蜉蝣于天地，渺沧海之一粟', '。');
时间函数
| 函数 | 描述 |
|---|---|
| age() | 产生象征性的年月日间隔 |
| current_time | 当前时区时间 |
| current_date | 当前日期 |
| current_timestamp | 当前时区日前时间 current_timestamp::date = current_date |
| now() | 当前时区日前时间 |
| to_timestamp() | 时间戳转日期时间 |
时间类型可用于计算
select age(current_timestamp,'1999-05-20');
select age(current_date,'1999-05-20');
select time '08:00:00' - time '03:00:00';
select time '08:00:00' - interval '03:00:00';
select time '08:00:00' - interval '3' hour;
select current_timestamp::date + interval '1 hour';
select now() + interval '3' minute;
select to_timestamp(1697760000);
-- 检索所有可用时区
select *
from pg_timezone_names;
-- 设置北京时间
set time zone 'Asia/Shanghai';
-- 显示时区
show timezone;
数组函数
| 函数 | 描述 |
|---|---|
| array[] | 生成数组类型数据 |
| array_append() | 在原有的数组上添加子项 |
| array_prepend() | 在原有的数组上前面添加子项 |
| array_to_string() | 数组转字符串 |
| array_cat() | 连接两个数组 |
| array_position() | 返回一维数组第二个参数在数组第一次出现的下标，没有则为NULL |
| array_positions() | 返回一维数组中第二个参数所有出现的下标的数组 |
| array_remove() | 返回数组全匹配子项 |
| array_length() | 计算数组长度 |
| array_replace() | 将数组指定元素替换为新指定元素 |
select array ['Dawn','Silver','Gravel'];
select array_append(array ['Dawn','Silver','Gravel'], 'Array');
select array_prepend('Array', array ['Dawn','Silver','Gravel']);
-- 计算一维数组长度
select array_length(array ['Dawn','Silver','Gravel'], 1);
select array_position(array ['Dawn','Silver','Array','Gravel','Array'], 'Array');
select array_positions(array ['Dawn','Silver','Array','Gravel','Array'], 'Array');
select array_cat(array ['Dawn'], '{1,1}');
select array_cat(array ['Dawn'], array ['1','1']);
select array_remove(array ['Dawn','Silver','Array','Gravel','Array'], 'Array');
select array_replace(array ['Dawn','Silver','Array','Gravel','Array'], 'Array','Blessing');
-- null 用 * 替代，连接符为空字符
select array_to_string(array ['Dawn','Silver','Gravel',NULL],'','*');
其他函数
| 函数 | 描述 |
|---|---|
| generate_series() | 生成结果列表，可用于生成统计参数条件 |
| gen_random_uuid() | 生成随机UUID |
| current_schema() | 当前模式 |
| current_user | 当前用户 |
| inet_server_addr() | 服务端IP |
| inet_client_addr() | 客户端IP |
| version() | Postgresql 版本 |
| current_database() | 当前数据库 |
| current_catalog | 当前数据库 |
-- 设置返回值函数
select generate_series(1, 4);
-- 1-4，每次+2
select generate_series(1, 4, 2);
-- 0-7 每次加一天
select current_date + s.a
from generate_series(0, 7, 1) as s(a);
-- 含有时间
select generate_series(current_date, current_date - interval '7 day', - interval '1' day);
-- 只有日期
select generate_series(current_date, current_date - interval '7 day', - interval '1' day)::date;
select generate_series(current_timestamp, current_timestamp - interval '10 hour', - interval '1' hour);
-- 随机UUID
select gen_random_uuid();
-- 当前模式
select current_schema();
-- 当前用户
select current_user;
-- 当前服务端 ip
select inet_server_addr();
-- 当前客户端 ip
select inet_client_addr();
-- Postgresql 版本
select version();
-- 当前数据库
select current_database();
select current_catalog;
存储过程
SQL 中的存储过程是一组一起存储在数据库中的 SQL 语句。根据过程中的语句和传递的参数，对数据库执行一个或多个 DML 操作，并返回值(值可无,函数必须有返回值)，具有重用性。
PL/pgSQL 是 PostgreSQL 数据库系统的可加载过程语言。 设计目标PL/pgSQL 的目的是创建一种可加载的过程语言:
- 可用于创建函数、过程和触发器
- 向 SQL 语言添加控制结构
- 可以执行复杂的计算
- 继承所有用户定义的类型、函数、过程和运算符
- 可以被定义为受服务器信
- 易于使用。 使用 PL/pgSQL 创建的函数可以在可以使用内置函数的任何地方使用。
创建存储过程
create table t_animal
(
id integer primary key,
name varchar(45)
);
-- 存储过程,创建或者替换
-- 可有 out、in、inout 类型
create or replace procedure insert_data(in num int4, in count int4)
language plpgsql
as
$$
declare
card int8 := 1;
name varchar := 'cat';
begin
for i in 0..count
loop
insert into t_animal (id, name) values (num + i, concat(name, i));
end loop;
end;
$$;
-- 执行存储过程
-- 不能用于select
call insert_data(4, 10);
-- 删除存储过程
drop procedure insert_data;
创建函数
-- 创建或者替换函数，returns
create or replace function silver_sum(integer, arg2 integer)
returns integer as
$$
DECLARE
args1 alias for $1;
begin
return args1 + arg2;
end;
$$ language plpgsql;
-- out 添加返回参数，可有多个
create or replace function silver_sum(integer, arg2 integer, out total integer)
as
$$
DECLARE
args1 alias for $1;
begin
total := args1 + arg2;
end;
$$ language plpgsql;
-- 执行函数
select silver_sum(11, 12);
-- 删除函数
drop function if exists silver_sum;
部分语法
更多语法参考：PostgreSQL 15.5 Documentation文档 Chapter 43. PL/pgSQL — SQL Procedural Language
create table if not exists t_poem
(
id integer primary key,
poem_name varchar(20),
author varchar(20),
poem_type varchar(20),
content text
);
create or replace function silver_operation(in args1 integer, inout integer, out args3 integer)
as
$$
declare
param integer := 11; -- 声明一个 param 变量
args2 alias for $2; -- 给第二个参数起一个别名
author t_poem.author%TYPE; -- 复制表中字段的类型
poem t_poem%ROWTYPE; -- 表所有字段类型
begin
select * into poem from t_poem where id = param;
-- 一个判断语句
if not found then -- FOUND 是一个 local variable
raise notice '序号为% 诗歌不存在', param;
else
author := poem.author;
raise notice '作者：% 诗歌内容：%',author,poem.content;
end if;
declare -- 每一个 declare需要接上一个 begin...end
zero integer := 0;
begin
raise notice '% / % = %',args1,args2,args1 / args2;
raise notice '% / % = %',args1,zero,args1 / zero;
exception
when division_by_zero then
raise notice division_by_zero; -- 不编写任何代码表示忽略了这个异常
end;
args3 = args1 + args2;
end;
$$ language plpgsql;
select silver_operation(4, 2);
insert into t_poem
values (11, '静夜思', '李白', '五言律诗','窗前明月光，疑是地上霜。举头望明月，低头思故乡');
- in、inout、out，in 为输入参数，out输出参数，inout既是入参也是出参，默认为in。
- declare： 用于声明定义参数如：param integer := 11。
- alias： 用于给变量声明别名：newName alias for oldName。
- 2。
- tablename.field%type：复制指定字段的类型。
- tablename%rowtype：复制指定表的所有字段类型，相当一个实体类。
- begin...end为一个块，可有多个，declare之后必须接一个begin...end。
- select * into poem from t_poem 将select获取到的数据填充到poem变量中，单行结果集
- if...else...end if 是一个控制语句，类似的如下：
- IF ... THEN ... END IF
- IF ... THEN ... ELSE ... END IF
- IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
- CASE ... WHEN ... THEN ... ELSE ... END CASE
- CASE WHEN ... THEN ... ELSE ... END CASE
- raise：控制台打印信息，
%为占位符号，输出级别有：DEBUG、LOG、INFO、NOTICE、WARNING, 以及EXCEPTION,其中EXCEPTION为默认级别，它会阻止程序往后执行，可以使用raise exception division_by_zero using hint = '检查除数是否合法'给予相关提示。 - exception：捕获异常，可以罗列相关异常，并执行相关代码。
create or replace function silver_table(num integer)
returns setof t_poem -- return next 需要 setof
as
$$
declare
index integer := 1;
result record; -- 可以使用 t_poem%rowtype
begin
raise notice '----执行 while 循环----';
while index > 0
Loop
index = index + 1;
if index > 5 then
exit; -- 大于 5 退出循环
elsif index = 2 then
continue; -- 如果为 2 则继续
end if;
raise notice 'index：%',index;
end loop;
raise notice '----执行 for 循环----';
for i in reverse num..0 by 2 -- reverse 每次递减2，没有reverse则是递增
loop
raise notice 'i：%',i;
end loop;
for result in select * from t_poem order by id
loop
raise notice '%',result.content;
if result.id = 100 and num < 10 then
return next result; -- 返回当前行数据
end if;
end loop;
if num > 10 then
return query select * from t_poem order by id;
end if;
end;
$$ language plpgsql;
select silver_table(6);
insert into t_poem
values (100, '次北固山下', '王湾', '五言律诗', '客路青山外，行舟绿水前。潮平两岸阔，风正一帆悬。');
select silver_table(6);
select silver_table(11);
- 可以返回表类型如 t_poem。
- record：name RECORD 与 tablename%rowtype类型，record不是真正的数据类型，没有预定义的数据结构,在select或for命令期间分配实际的row结构。
- while: while 表达式 loop...end loop;
- exit、continue：exit退出loop，continue跳过本次循环后续步骤。
- for：for variable in 表达式 loop...end loop; 6..0 表示 6-0， reverse为递减，默认递增。by 表示每次递增/递减的步量，默认为1。
- return next result：返回当前 row数据。
- return query select * from t_poem order by id：返回查询的结果集数据。
create or replace procedure silver_transaction()
as
$$
begin
insert into t_poem
values (10, '次北固山下', '王湾', '五言律诗', '客路青山外，行舟绿水前。潮平两岸阔，风正一帆悬。');
rollback;
commit;
end;
$$ language plpgsql;
end;
call silver_transaction();
select *
from t_poem;
存储过程可以使用transaction事务，而function函数则不可以。
事务 Transaction
事务是单个原子命令的命令组合，要么全部成功或全部失败。在事务完成之前对其他会话不可见。事务有ACID4个特性：
- Atomicity: 原子性，所有操作要么作为单个单元完成，要么一个都不完成。如果事务执行期间出现系统故障，会没有部分结果，恢复后可见。
- Consistency: 一致性，数据库中的数据始终符合完整性约束的性质。事务可能允许在提交之前违反一些约束，但是如果在发生时仍然没有解决则会自动回滚。
- Isolation: 隔离性，事务在提交之前对并发事务不可见的属性。
- Durability: 持久性，一旦事务被提交完成，即使系统故障和崩溃之后，更改仍然存在。
如果没有事务隔离，可能会出现以下现象：
脏读：事务读取由于并发未提交事务写入的数据。
不可重复读：事务（自初始读取以来未提交的事务）重新读取了以前读过的数据，并发现数据已经被另一个事务修改，导致两次获取的行数据不一致。
幻读：在事务执行过程中，当两个完全相同的查询语句执行得到不同的结果集。
序列化异常：一次成功提交一组事务的结果，由于事务执行顺序的不同导致数据不一致。
SQL标准定义四个事务隔离级别处理以上现象：
| 隔离级别 | 脏读 | 不可重读 | 幻读 | 序列化异常 |
|---|---|---|---|---|
| Read uncommited | 可能 但不在Postgresql | 可能 | 可能 | 可能 |
| Read commited | 不可能 | 可能 | 可能 | 可能 |
| Repeatable read | 不可能 | 不可能 | 可能 但不在Postgresql | 可能 |
| Serializable | 不可能 | 不可能 | 不可能 | 不可能 |
Postgresql 实现了 Read commited、Repeatable read、Serializable 三种隔离级别，其中 Read commited为系统默认隔离级别。
create table if not exists t_poem
(
id integer primary key,
poem_name varchar(20),
author varchar(20),
poem_type varchar(20),
content text
);
-- 查询默认事务隔离级别
show default_transaction_isolation;
-- 设置会话级别的事务隔离等级
set transaction isolation level serializable;
start transaction; -- 开始事务 isolation level serializableinsert into t_poem
values (1, '春晓', '李白', '五言律诗', '春眠不觉晓，处处闻啼鸟。夜来风雨声，花落知多少');
savepoint dawn; -- 设置保存点
update t_poem
set author = 'SilverGravel'
where id = 1;
rollback to dawn; -- 回顾到上一个保存点，如果是 rollback，则第一个插入语句不存在
update t_poem
set author = '孟浩然'
where id = 1;
commit;
end transaction;
-- begin isolation level repeatable read;
begin transaction;
insert into t_poem
values (1, '春晓', '李白', '五言律诗', '春眠不觉晓，处处闻啼鸟。夜来风雨声，花落知多少');
savepoint dawn; -- 设置保存点
update t_poem
set author = 'SilverGravel'
where id = 1;
rollback to dawn; -- 回顾到上一个保存点，如果是 rollback，则第一个插入语句不存在
update t_poem
set author = '孟浩然'
where id = 1;
commit;
end transaction;
select *
from t_poem;
PostGIS
PostGIS扩展提供了对地理数据存储、索引、查询支持，扩展Postgresql数据库功能。
更多函数参考：PostGIS 3.4.0dev Manual
绘制地理数据可以使用：jts-1.19.0，下载JTSTestBuilder.jar，在文件所在路径执行
# 需要安装JDK
java -jar JTSTestBuilder.jar
| 函数 | 描述 |
|---|---|
| ST_GeomFromText() | 从特定的wkt文本转成指定ST_Geometry值 |
| ST_AsText() | 从geometry/geography转成wkt文本，无需SRID数据 |
| ST_IsValid() | 校验geometry值是否合法 |
| ST_MakeValid() | 尝试在不丢失顶点的情况下使无效geometry有效 |
| ST_Contains() | 判断 B 的每个点是否都位于 A 中，并且它们的内部是否有一个共同点 |
| ST_CoveredBy() | 判断 A 的每个点是否都位于 B 中 |
| ST_Covers() | 判断 B 的每个点是否都位于 A 中 |
| ST_Crosses() | 判断两个geomtry是否有一些内部共同点（不是全部） |
| ST_Disjoint() | 判断两个geometry没有共同的点 |
| ST_Touches() | 判断两个geometry至少有一个共同点，但是它们内部不相交 |
| ST_Equals() | 判断两个geometry是否包含相同共同点，相当于ST_Within(A,B) = true and ST_Within(B,A) = true |
| ST_Intersects() | 判断两个geometry是否相交，至少含有一个共同点 |
| ST_Within() | 判断 A 的每个点是否都位于 B 中，并且它们的内部是否有一个共同点 |
| ST_Distance() | 返回两个geometry或geography之间的距离 |
| ST_ShortestLine() | 返回两个2D geometry之间最短直线距离 |
| ST_Difference() | 计算一个geomtry图形表示几何A 不与几何 B 相交的部分的几何 |
| ST_Intersection() | 计算一个geomtry图形表示几何 A 与几何 B 相交的部分的几何 |
| ST_SymDifference() | 计算一个geomtry图形表示几何 A 与几何 B 不相交的部分的几何 |
-- 为数据库添加扩展，需要安装postgis插件才能执行
-- 默认为public，create extension if not exists postgis with schema 指定模式
-- postgis扩展不支持 alter extension postgis set schema public; 来修改模式
-- 需要 drop extension postgis; 重新修改模式
create extension if not exists postgis;
-- 查看 PostGIS版本
select PostGIS_Full_Version();
-- st_geomfromewkt
select st_geomfromewkt('POLYGON ((190 150, 20 10, 160 70, 190 150))');
-- st_astext
select st_astext('010300000001000000040000000000000000C067400' ||
'000000000C0624000000000000034400000000000002440' ||
'000000000000644000000000008051400000000000C0674' ||
'00000000000C06240');
-- st_isvalid false 表示不合法
select st_isvalid(st_geomfromewkt('POLYGON ((130 350, 230 350, 120 230, 240 230, 130 350))'));
-- 使用st_makevalid矫正
select st_astext(st_makevalid(st_geomfromewkt('POLYGON ((130 350, 230 350, 120 230, 240 230, 130 350))')));
-- st_contains
select st_contains(st_geomfromewkt('POLYGON ((164.5 321.5, 247 322.5, 204.5 275.5, 164.5 321.5))'),
st_geomfromewkt('POLYGON ((164.5 321.5, 247 322.5, 204.5 275.5, 164.5 321.5))'));
-- st_coveredby A 的每一个点都在 B 中
select st_coveredby(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((205.5 229, 250 230, 225 186.5, 205.5 229))'));
-- st_covers B 的每一个点都在 A 中
select st_covers(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((205.5 229, 250 230, 225 186.5, 205.5 229))'));
-- st_crosses
select st_crosses(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- st_disjoint
select st_disjoint(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- st_touches
select st_touches(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- st_equals
select st_equals(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- st_intersects
select st_intersects(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- st_within
select st_within(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))'));
-- ST_Distance
SELECT st_distance(
'POINT (160 40)',
'LINESTRING (10 30, 50 50, 30 110, 70 90, 180 140, 130 190)')
as distance;
select st_distance(
'POLYGON ((190 150, 20 10, 160 70, 190 150))',
st_buffer('POINT(80 160)', 30)
) as distance;
-- ST_ShortestLine
select st_astext(st_shortestline(
'POINT (160 40)',
'LINESTRING (10 30, 50 50, 30 110, 70 90, 180 140, 130 190)')
) as sline;
SELECT ST_AsText(ST_ShortestLine(
'POLYGON ((190 150, 20 10, 160 70, 190 150))',
ST_Buffer('POINT(80 160)', 30)
)) AS llinewkt;
-- st_difference
select st_astext(st_difference(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))')));
-- st_intersection
select st_astext(st_intersection(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))')));
-- st_symdifference
select st_astext(st_symdifference(st_geomfromewkt('POLYGON ((174 241.5, 275.5 243, 222 156.5, 174 241.5))'),
st_geomfromewkt('POLYGON ((166.75 202.5, 211.25 203.5, 186.25 160, 166.75 202.5))')));
Mybatis中的部分应用场景
数组类型的应用
XML中插入数据
<insert id="insertUser">
insert into t_user(id, card)
values (#{user.id},
ARRAY[
<foreach collection="user.card" open="" close="" separator="," item="item">
#{item}
</foreach>])
</insert>
Mybatis-Plus中实体类配置
@TableName注解属性 autoResultMap设置为true，实体类数组字段添加注解@TableField(typeHandler = ArrayTypeHandler.class,jdbcType = JdbcType.ARRAY)
模糊搜索
<select id="selectUsers" resultType="com.silvergravel.domain.User">
select id,card,name from t_user
where name like '%'|| #{name} || '%'
</select>
或者
<select id="selectUsers" resultType="com.silvergravel.domain.User">
select id,card,name from t_user
where name like concat('%',#{name},'%')
</select>
