PostgreSQL插入语句转换方法及新版本now()::abstime::INT4报错问题咨询

嘿，我来帮你搞定这两个PostgreSQL的问题，都是日常开发里很容易碰到的场景，给你详细拆解：

1. 如何转换PostgreSQL插入语句？ #

首先得明确你说的「转换」具体指哪种场景，我先覆盖几个最常见的情况，你可以对应自己的需求调整：

• 从其他数据库（比如MySQL）转PostgreSQL插入语句
  不同数据库的插入语法有细微差别，核心要注意这几点：

  • 自增字段：MySQL的AUTO_INCREMENT对应PostgreSQL的SERIAL/IDENTITY，插入时要么跳过自增列，要么用DEFAULT代替NULL
  • 日期时间：MySQL的NOW()直接换成PostgreSQL的now()或者CURRENT_TIMESTAMP；如果是字符串转日期，把STR_TO_DATE()换成to_timestamp()
  • 示例对比：
    MySQL原语句：

    INSERT INTO users (id, name, created_at) VALUES (NULL, 'Alice', NOW());
    

    转成PostgreSQL的写法：

    INSERT INTO users (name, created_at) VALUES ('Alice', now());
    -- 要是想显式指定自增列，就用DEFAULT
    INSERT INTO users (id, name, created_at) VALUES (DEFAULT, 'Alice', now());
    

• 单条插入转批量插入
  PostgreSQL支持批量插入，直接把多组值用逗号分隔就行，比循环单条插入高效太多：
  单条原语句：

  INSERT INTO glines (Host, AddedBy, AddedOn, ExpiresAt, Reason) VALUES ('host1', 'user1', 1690000000, 1700000000, 'test');
  

  批量转换后：

  INSERT INTO glines (Host, AddedBy, AddedOn, ExpiresAt, Reason) 
  VALUES 
  ('host1', 'user1', 1690000000, 1700000000, 'test'),
  ('host2', 'user2', 1691000000, 1701000000, 'another test');
  

• 转换为带冲突处理的插入
  你的glines表有Host唯一约束，插入时很可能碰到重复的情况，PostgreSQL的ON CONFLICT可以优雅处理，比如：

  INSERT INTO glines (Host, AddedBy, AddedOn, ExpiresAt, Reason) 
  VALUES ('host1', 'user1', 1690000000, 1700000000, 'test')
  ON CONFLICT (Host) DO UPDATE SET 
    LastUpdated = extract(epoch from now())::int4,
    Reason = EXCLUDED.Reason;
  

2. 新版本PostgreSQL中建表语句now()::abstime::int4报错的解决方法 #

这个问题我碰到过太多次了！原因是abstime这个类型在PostgreSQL 9.2之后就被标记为废弃，到新版本（比如12+）已经彻底不支持直接转换了。你原来的写法是想把当前时间转成Unix秒级时间戳，用标准写法就能解决：

正确的替代方案 #

把now()::abstime::int4换成extract(epoch from now())::int4，extract(epoch from ...)是PostgreSQL官方推荐的获取Unix时间戳的方式，直接返回从1970-01-01到当前时间的秒数，再转成int4（也就是integer）完全没问题。

修改后的完整建表语句：

CREATE TABLE glines ( 
  Id SERIAL, 
  Host VARCHAR(128) UNIQUE NOT NULL, 
  AddedBy VARCHAR(128) NOT NULL, 
  AddedOn INT4 NOT NULL, 
  ExpiresAt INT4 NOT NULL, 
  LastUpdated INT4 NOT NULL DEFAULT extract(epoch from now())::int4, 
  Reason VARCHAR(255) 
);

为什么原来的写法会报错？ #

abstime是PostgreSQL早期的老旧时间类型，它本质是用整数存储秒数，但官方因为它的溢出问题（int4最多只能到2038年）和类型兼容性问题，早就废弃了这个类型。新版本里已经不允许直接从timestamp（now()返回的类型）转换到abstime，所以才会报错。

如果以后你需要毫秒级时间戳，只要把extract(epoch from now()) * 1000::bigint，字段类型改成int8（也就是bigint）就行。

内容的提问来源于stack exchange，提问作者Alfie Ocaña