PostgreSQL使用总结

之前在工作中使用了一年半PostgreSQL9.5，总结一下。

• 递归查询：开发中碰到了递归查询的场景，网上搜了一下，觉得挺有意思，以下这个示例非原创，版权归原作者所有。

  开发有需求，说需要对一张地区表进行递归查询，Postgres中有个with recursive的查询方式，可以满足递归查询(一般>=2层)。 测试如下：

  create table tb(id varchar(3) , pid varchar(3) , name varchar(10));
  insert into tb values('002', 0, '浙江省');
  insert into tb values('001', 0, '广东省');
  insert into tb values('003', '002', '衢州市');
  insert into tb values('004', '002', '杭州市') ;
  ...​
  

  测试语句,查询浙江省及以下县市：

  with RECURSIVE cte as
  (
   select a.id,a.name,a.pid from tb a where id='002'
   union all
   select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid
  )
  select id,name from cte;
   id | name
  ----+--------
  002 | 浙江省
  003 | 衢州市
  004 | 杭州市
  ...
  

  如果查询有报错如死循环跳出，则需要检查下父字段与子字段的数据是否有相同。如果想按层次分别显示出来，也可以这么写：

  with RECURSIVE cte as
  (
   select a.id,cast(a.name as varchar(100)) from tb a where id='002'
   union all
   select k.id,cast(c.name||'>'||k.name as varchar(100)) as name from tb k inner join cte c on c.id = k.pid
  )
  select id,name from cte;
   id | name
  ----+----------------------
  002 | 浙江省
  003 | 浙江省>衢州市
  004 | 浙江省>杭州市
  ...