pg数据库中的upsert和RECURSIVE

【upcert 】

9.5之后

insert into $tableName (id,name,age,update) values('id1','name1',18,now()) ON conflict(id,name) DO UPDATE SET age = EXCLUDED.age,update=EXCLUDED.update；

insert into $tableName (id,name,age,update) values('id1','name1',18,now()) ON conflict(id,name) DO NOTHING  ;

9.5之前

存储过程不介绍了，太麻烦

with upsert as (update $tablename set age=$age,update=now() where id=$id name=$name  returning *) insert into $tablename select $id,$name,$age,now() where not exists (select 1 from upsert where id=$id and name=$name);

 

【RECURSIVE】

数据表结构

id    name    pid

1     中国      0

2     辽宁      1

3     大连       2

4     沈阳        2

5     和平区     4

WITH RECURSIVE r AS (

SELECT * FROM test_area WHERE id = 5

UNION ALL

SELECT test_area.* FROM test_area, r WHERE test_area.id = r.pid )

SELECT * FROM r ORDER BY id;

 

查询结果

id     name    pid

1     中国      0

2     辽宁      1

4     沈阳        2

5     和平区     4

 

https://www.cnblogs.com/ricklz/p/12590618.html#with-recursive-%E4%BD%BF%E7%94%A8%E9%99%90%E5%88%B6