postgres

1、approval_status按照指定顺序排序，create_time 按倒序：

WHERE (status = 0 ) ORDER BY position (approval_status::text in '2,1,4,5,3'),create_time DESC limit 20 offset 0

 2、Mybatis-plus 数据库自增主键为int 类型对应Java类类型为Integer 报错问题

添加  type = IdType.AUTO

    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

 3、postgres在表更新的时候自动更新update_time字段时间

1、创建表 tb_case

2、--创建触发器
CREATE OR REPLACE FUNCTION "upd_timestamp"()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

3、--在表中添加对应的触发器
CREATE TRIGGER "update_time" BEFORE UPDATE ON "tb_case"
FOR EACH ROW
EXECUTE PROCEDURE "upd_timestamp"();

 4、to_char函数使用

https://my.oschina.net/u/877170/blog/279055

5、sql 循环更新

DO $$
DECLARE 
i INTEGER  := 0;  --  定义变量
j INTEGER  := 10000;

BEGIN
  while i < j LOOP
    -- 循环体内需要执行的 SQL 语法
    update tb_community_bs_info set community_police_name = registrar where community_code = (select community_code from tb_community_bs_info bs where community_police_name is null and registrar is not null limit 1);
    i =i +1;
  END LOOP;
END $$;

6、将一个表的字段更新另一个表的字段

update vehicle_info v set pic_url = t.pic_url from test t where v.vehicle_id = t.vehicle_id