PostgreSQL INSERT INTO ON CONFLICT语法。UPSERT(insert on conflict do) 记录存在就更新，不存在就更新。
在某些业务场景中，需要把插入语句和更新组合到一起，一条SQL同时完成插入和更新逻辑。
这个时候就需要用到 INSERT ON CONFLICT语法特性了。可以实现根据主键或者唯一约束，来插入或指定条件更新。

官方语法说明

Command:     INSERT  
Description: create new rows in a table  
Syntax:  
[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]

假如我们有这样一张日志表，要同时实现写入和更新。

CREATE TABLE user_log_info (
    log_id text NOT NULL,
    log_name text,
    log_path text,
    log_size bigint,
    log_time timestamp,
    create_time timestamp,
    PRIMARY KEY (log_id,log_time)
)

根据主键新增或更新

符合写入日志时间比当前记录新，才执行写入更新

INSERT INTO user_log_info
VALUES
	( 'demo001', 'open1', '/logs/a.log', 30, TO_TIMESTAMP( 1713509590 ), LOCALTIMESTAMP ) ON CONFLICT ( log_id, log_time ) DO
UPDATE 
	SET log_name = EXCLUDED.log_name,
	log_path = EXCLUDED.log_path,
	log_size = EXCLUDED.log_size,
	log_time = EXCLUDED.log_time,
	create_time = EXCLUDED.create_time 
WHERE
	EXCLUDED.log_time > user_log_info.log_time

根据符合条件才更新

举例：符合日志size大小，比之前大才会更新，SQL如下

INSERT INTO user_log_info
VALUES
	( 'demo001', 'open1', '/logs/a.log', 30, TO_TIMESTAMP( 1713509590 ), LOCALTIMESTAMP ) ON CONFLICT ( log_id, log_time ) DO
UPDATE 
	SET log_name = EXCLUDED.log_name,
	log_path = EXCLUDED.log_path,
	log_size = EXCLUDED.log_size,
	log_time = EXCLUDED.log_time,
	create_time = EXCLUDED.create_time 
WHERE
	EXCLUDED.log_size > user_log_info.log_size

实际应用场景

相当于我们在写入数据时，可以像写查询一样带上WHERE条件，实现符合条件才更新

PGSQL定时任务实现

cron + pgpass
利用shell脚本 + Linux定时任务，去定时执行清理日志表

首先配置命令行免密登录

根据Postgres的官方文档配置.pgpass。

文件内容格式如下：127.0.0.1:5432:库名:用户:密码

登录服务器，在用户主目录中创建`.pgpass`文件配置
vi .pgpass
127.0.0.1:5432:demo_test:postgres:123456

// 授权当前用户可见
chmod 600 .pgpass

// 执行验证测试，查询连接用户数SQL
/usr/pgsql-13/bin/psql -h 127.0.0.1 -p 5432 -d demo_test  -U postgres  -c "select COUNT(*) from pg_stat_activity"

编写shell脚本封装SQL

vim pgcron.sh。文件内容如下

#!/bin/bash

# 执行方法处理
execpgsql(){
  echo $1
  starttime=`date +"%Y-%m-%d %H:%M.%S"` 
  result=$(/usr/pgsql-13/bin/psql -h 127.0.0.1 -p 5432 -d demo_test  -U postgres  -c "$1")
  endtime=`date +"%Y-%m-%d %H:%M.%S"`
  echo "[开始执行:$starttime" "执行结束:$endtime]" "SQL:$1" "结果:$result"
}

# 定时删除表
sql="DROP TABLE user_log_info"
execpgsql "$sql"

再将这个脚本加入 crontab，并设定执行周期

PostgreSQL UPSERT(insert on conflict do)和pgpass使用说明