PostgreSQL批量插入/批量更新时间一致问题

• PostgreSQL在执行批量插入、更新使用函数now()或是current_time()获取当前系统时间一致，导致在根据时间order by时无法获取理想的结果

user表结构

create table "user"
(
    user_id       serial            not null
        constraint user_pk
            primary key,
    user_name     varchar(32)       not null,
    user_sex      integer 			not null,
    user_password varchar(256)      not null,
    create_time   timestamp         not null,
    create_by     integer           not null,
    update_time   timestamp         not null,
    update_by     integer           not null,
    deleted       integer 			not null
);

批量新增user

• insert into "user" (user_name, user_sex, user_password, create_time, create_by, update_time, update_by,
                      deleted)
  values ('张三', 1, '123456', now(), 1, now(), 1, 0),
         ('宋爱梅', 0, '123456', now(), 1, now(), 1, 0),
         ('王志芳', 1, '123456', now(), 1, now(), 1, 0),
         ('于光', 0, '123456', now(), 1, now(), 1, 0),
         ('贾隽仙', 1, '123456', now(), 1, now(), 1, 0),
         ('贾燕青', 0, '123456', now(), 1, now(), 1, 0),
         ('刘振杰', 1, '123456', now(), 1, now(), 1, 0),
         ('郭卫东', 0, '123456', now(), 1, now(), 1, 0),
         ('崔红宇', 1, '123456', now(), 1, now(), 1, 0),
         ('马福平', 0, '123456', now(), 1, now(), 1, 0),
         ('冯红', 1, '123456', now(), 1, now(), 1, 0),
         ('崔敬伟', 0, '123456', now(), 1, now(), 1, 0),
         ('穆增志', 1, '123456', now(), 1, now(), 1, 0),
         ('谢志威', 0, '123456', now(), 1, now(), 1, 0),
         ('吕金起', 1, '123456', now(), 1, now(), 1, 0),
         ('韩云庆', 0, '123456', now(), 1, now(), 1, 0),
         ('鲁全福', 1, '123456', now(), 1, now(), 1, 0),
         ('郭建立', 0, '123456', now(), 1, now(), 1, 0),
         ('郝连水', 1, '123456', now(), 1, now(), 1, 0),
         ('闫智胜', 0, '123456', now(), 1, now(), 1, 0);
  

• 查询create_time、update_time

  • select create_time,update_time
    from "user";
    

• 结果**create_time,update_time均为同一时间**

  • 

问题解决

• now()

  • 按照当前事务的开始时刻返回结果，所以获取的值在事务运行的整个期间内都不改变

• clock_timestamp()

  • 当前日期和时间（在语句执行期间变化）
  • 返回真正的当前时间，因此获取到的值在同一条 SQL 命令中都会变化

• 批量新增用户

  • insert into "user" (user_name, user_sex, user_password, create_time, create_by, update_time, update_by,
                        deleted)
    values ('张三', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('宋爱梅', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('王志芳', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('于光', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('贾隽仙', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('贾燕青', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('刘振杰', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('郭卫东', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('崔红宇', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('马福平', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('冯红', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('崔敬伟', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('穆增志', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('谢志威', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('吕金起', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('韩云庆', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('鲁全福', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('郭建立', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('郝连水', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0),
           ('闫智胜', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0);
    

• 查询create_time、update_time

  • select create_time,update_time
    from "user";
    

• 结果

  •