用户可以根据实际需求，使用不同的方法

1. 函数

test03=# create or replace function f_upsert(int,text,timestamp) returns void as $$  
declare  
  res int;  
begin  
  update test set info=$2,crt_time=$3 where id=$1;  
  if not found then  
    insert into test (id,info,crt_time) values ($1,$2,$3);  
  end if;  
  exception when others then  
    return;  
$$ language plpgsql strict;  
CREATE FUNCTION  
test03=# select f_upsert(1,'digoal',now()::timestamp);  
 f_upsert   
----------  
(1 row)  
test03=# select * from test;  
 id |  info  |          crt_time            
----+--------+----------------------------  
  2 | pu     | 2017-04-24 15:28:20.37392  
  1 | digoal | 2017-04-24 15:31:29.254325  
(2 rows)  
test03=# select f_upsert(1,'digoal001',now()::timestamp);  
 f_upsert   
----------  
(1 row)  
test03=# select * from test;  
 id |   info    |         crt_time            
----+-----------+---------------------------  
  2 | pu        | 2017-04-24 15:28:20.37392  
  1 | digoal001 | 2017-04-24 15:31:38.0529  
(2 rows)  
test03=# select f_upsert(3,'hello',now()::timestamp);  
 f_upsert   
----------  
(1 row)  
test03=# select * from test;  
 id |   info    |         crt_time            
----+-----------+---------------------------  
  2 | pu        | 2017-04-24 15:28:20.37392  
  1 | digoal001 | 2017-04-24 15:31:38.0529  
  3 | hello     | 2017-04-24 15:31:49.14291  
(3 rows)  
2. WITH语法，用法1
create table test(id int primary key, info text, crt_time timestamp);  
存在则更新，不存在则插入。
with upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *) insert into test select $id,$info,$crt_time where not exists (select 1 from upsert where id=$id);    
替换变量，进行测试
with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 from upsert where id=1);    
同时插入一条不存在的值，只有一个会话成功，另一个会话会报PK约束错误。
3. WITH语法，用法2
即使表没有PK或者唯一约束，也能保证并发。
create table test(id int, info text, crt_time timestamp);  
3.1 对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会lock着记录，后来的session会wait。
  w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *)    
  insert into test select $id, $info, $crt_time from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);    
替换变量，进行测试
  w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='digoal0123',crt_time=now() where id=1 returning *)    
  insert into test select 1, 'digoal0123', now() from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
INSERT 0 0  
test03=# select * from test;  
 id |    info    |         crt_time            
----+------------+---------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  1 | digoal0123 | 2017-04-24 15:31:38.0529  
(3 rows)  
  w1 as(select ('x'||substr(md5('4'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='digoal0123',crt_time=now() where id=4 returning *)    
  insert into test select 4, 'digoal0123', now() from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=4);    
INSERT 0 1  
test03=# select * from test;  
 id |    info    |          crt_time            
----+------------+----------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  1 | digoal0123 | 2017-04-24 15:31:38.0529  
  4 | digoal0123 | 2017-04-24 15:38:39.801908  
(4 rows)  
3.2 对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会更新数据，后来的session不等待，直接失败。
with w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info=$info,crt_time=$crt_time from w1 where pg_try_advisory_xact_lock(tra_id) and id=$id returning *)    
  insert into test select $id,$info,$crt_time from w1   
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);     
替换变量，进行测试
with w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='test',crt_time=now() from w1 where pg_try_advisory_xact_lock(tra_id) and id=1 returning *)    
  insert into test select 1,'test',now() from w1   
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
INSERT 0 0  
test03=# select * from test;  
 id |    info    |          crt_time            
----+------------+----------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  4 | digoal0123 | 2017-04-24 15:42:50.912887  
  1 | test       | 2017-04-24 15:44:44.245167  
(4 rows)  
            PolarDB 开源版 使用PostGIS 以及泰森多边形 解决 "零售、配送、综合体、教培、连锁店等经营"|"通信行业基站建设功率和指向" 的地理最优解问题 
                 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（四）|学习笔记
            
快速学习3 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（四）
                 3 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（三）|学习笔记
            
快速学习3 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（三）
使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。
DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )  
                                            12343
                Postgresql pg_dump&pg_restore用法
            
PostgreSQL提供的一个工具pg_dump,逻辑导出数据，生成sql文件或其他格式文件，pg_dump是一个客户端工具，可以远程或本地导出逻辑数据，恢复数据至导出时间点。pg_dump 一次只转储一个数据库, 并且不会转储有关角色或表空间的信息 (因为那些是群集范围而不是每个数据库)。
                                            10890
insert慢是经常被问到的问题，笔者尝试在本文中对这个问题做一个分类梳理，列举的线上例子会做简化，希望对读者有所启发。
注意：因为阿里云MySQL线上实例还是以RDS 5.6为主体，本文的分析也是以5.6 innodb 引擎为主，其他版本的rds的实例可能略有差别。
转载请注明出处:https://www.cnblogs.com/funnyzpc/p/9311281.html
　　PostgreSQL的高级特性本准备三篇的(递归、窗口函数、JSON)，结果中间一直一直加班 和遗忘 拖到现在才写到中篇，欸，加班真不是一件好事情。