关于出差学习到的pgSql的一些知识点总结

我在出差的学习中,对接口进行迁移,其中涉及到旧系统的oracle数据库迁移到现在的pgSql库.再次总结一些学习到的pgSql新东西,可以对oracle语句进行实现.

1.对插入语句涉及到了取序列的值进行自主生成主键ID,再次总结下序列相关的语句:

--查看序列
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
--创建序列(自定义一个对应表名的序列名,主键从1开始起始)
CREATE SEQUENCE (table_name)_id_seq START 1;
--先在表中设置好主键 再执行  给表设置该序列主键
ALTER TABLE (table_name) ALTER COLUMN id SET DEFAULT nextval('(table_name)_id_seq'::regclass);
自己实际运用到的是取到主键值用0进行24位的拼接才是真正的主键
(lpad(cast(nextval('seq_pm_srv_exchange_log_id') as varchar),24,'0'))

2.由上引出lpad函数

PostgreSQL中的lpad()函数有两个功能：
1，如果长度不够指定的长度，就在左边填充字符串,
2，如果长度超出了指定的长度，就把右边截掉。

3.pgSql实现Oracle的START WITH/CONNECT BY递归查询

oracle语句:

select distinct psrb.lan_id
		from pm_spc_region_backup0324 psrb
		where psrb.grade_id = 2000004
		connect by nocycle prior psrb.super_region_id = psrb.region_id
		and delete_state = '0'
		start with psrb.region_id = (select a.region_id
		from rm_address a
		where a.spec_id  = #{addrId,jdbcType=VARCHAR})

pgSql使用 WITH RECURSIVE 语法实现相同的功能

WITH RECURSIVE a AS (
		SELECT distinct psrb1.lan_id
		FROM pm_spc_region_backup0324 psrb1
		where psrb1.grade_id =2000004
		and psrb.region_id = (select a.region_id from rm_address a where a.spec_id  = #{addrId,jdbcType=VARCHAR})
		union all
		SELECT distinct psrb2.lan_id
		FROM pm_spc_region_backup0324 psrb2
		JOIN pm_spc_region_backup0324 a ON a.super_region_id  = psrb2.region_id)
		SELECT * FROM a;

其中oracle语句的prior关键字修饰的是super_region_id ,表明向上递归查询它的父级,如果反过来则代表向下递归查它的子级

对应的是join语句的关联条件a.super_region_id = psrb2.region_id

4.oracle的merge语句在pgsql的实现

(合并MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE)

oracle语句
merge into A a using B b
on (a.id = b.id and a.code = b.code)
when matched then
update col1 = b.col1,col2 = b.col2
    when not matched then
insert
into a
values (b.col1, b.col2);

pgSql语句:

with upsert as (
    update a
        set col1 = b.col1,col2 = b.col2
        from b t
        where a.id = t.id and a.code = t.code
        returning a.id,a.code
)
insert
into a
select *
from b t
where not exists(
        select 1
        from upsert b
        where t.id = b.id
          and t.code = b.code
    );