PostgreSQL批量插入NULL值问题及MSSQL查询迁移求助

我正尝试把一个MSSQL Server的示例迁移到PostgreSQL，但没法让插入脚本正确写入NULL值。最终目标是把下面的MSSQL查询转换成PostgreSQL可用的版本，同时保留原数据和表结构：

select a1.OrderNumber, a1.New as Step, 
  sum(datediff(second, a1.TimeEntered, isnull(a2.timeEntered,getdate()))) as [Total Time in Step (seconds)]
from AuditTrail a1
left join AuditTrail a2
  on a1.New = a2.Old 
  and a1.OrderNumber = a2.OrderNumber
group by a1.OrderNumber, a1.New
order by a1.OrderNumber

我试过用""、''、NULL、IS NULL等写法，但都不管用。以下是我写的表结构和插入脚本：

create table AuditTrail(
    Old varchar(50),
    New varchar(50),
    TimeEntered Timestamp,
    OrderNumber varchar(50)
);

insert into "AuditTrail" 
( **THIS SHOULD BE NULL**, 'Step 1'   ,   '4/30/12 10:43  ','1C2014A'),
('Step 1',   'Step 2' ,   '  5/2/12 10:17 ','1C2014A'),
('Step 2',   'Step 3' ,   '  5/2/12 10:28 ','1C2014A'),
('Step 3',   'Step 4' ,   '  5/2/12 11:14 ','1C2014A'),
('Step 4',   'Step 5' ,   '  5/2/12 11:19 ','1C2014A'),
('Step 5',   'Step 9' ,   '  5/3/12 11:23 ','1C2014A'),
(NULL    , 'Step 1'   ,   '5/18/12 15:49  ','1C2014B'),
('Step 1',   'Step 2' ,   '  5/21/12 9:21 ','1C2014B'),
('Step 2',   'Step 3' ,   '  5/21/12 9:34 ','1C2014B'),
('Step 3',   'Step 4' ,   '  5/21/12 10:08','1C2014B'),
('Step 4',   'Step 5' ,   '  5/21/12 10:09','1C2014B'),
('Step 5',   'Step 6' ,   '  5/21/12 16:27','1C2014B'),
('Step 6',   'Step 9' ,   '  5/21/12 18:07','1C2014B'),
(NULL    , 'Step 1'   ,   '6/12/12 10:28  ','1C2014C'),
('Step 1',   'Step 2' ,   '  6/13/12 8:36 ','1C2014C'),
('Step 2',  'Step 3'  ,   ' 6/13/12 9:05  ','1C2014C'),
('Step 3',  'Step 4'  ,   ' 6/13/12 10:28 ','1C2014C'),
('Step 4',   'Step 6' ,   '  6/13/12 10:50','1C2014C'),
('Step 6',   'Step 8' ,   '  6/13/12 12:14','1C2014C'),
('Step 8',   'Step 4' ,   '  6/13/12 15:13','1C2014C'),
('Step 4',   'Step 5' ,   '  6/13/12 15:23','1C2014C'),
('Step 5',   'Step 8' ,   '  6/13/12 15:30','1C2014C'),
('Step 8',   'Step 9' ,   '  6/18/12 14:04','1C2014C')

解决方法 #

1. 修复插入脚本的NULL问题

PostgreSQL中插入NULL直接写NULL即可，你脚本里第一行的**THIS SHOULD BE NULL**是无效内容，需要替换成NULL。另外，PostgreSQL对非标准时间字符串的解析可能出错，建议用to_timestamp函数指定格式来确保时间正确插入。

修正后的插入脚本：

insert into AuditTrail 
(NULL, 'Step 1', to_timestamp('4/30/12 10:43', 'MM/DD/YY HH24:MI'), '1C2014A'),
('Step 1', 'Step 2', to_timestamp('5/2/12 10:17', 'MM/DD/YY HH24:MI'), '1C2014A'),
('Step 2', 'Step 3', to_timestamp('5/2/12 10:28', 'MM/DD/YY HH24:MI'), '1C2014A'),
('Step 3', 'Step 4', to_timestamp('5/2/12 11:14', 'MM/DD/YY HH24:MI'), '1C2014A'),
('Step 4', 'Step 5', to_timestamp('5/2/12 11:19', 'MM/DD/YY HH24:MI'), '1C2014A'),
('Step 5', 'Step 9', to_timestamp('5/3/12 11:23', 'MM/DD/YY HH24:MI'), '1C2014A'),
(NULL, 'Step 1', to_timestamp('5/18/12 15:49', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 1', 'Step 2', to_timestamp('5/21/12 9:21', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 2', 'Step 3', to_timestamp('5/21/12 9:34', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 3', 'Step 4', to_timestamp('5/21/12 10:08', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 4', 'Step 5', to_timestamp('5/21/12 10:09', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 5', 'Step 6', to_timestamp('5/21/12 16:27', 'MM/DD/YY HH24:MI'), '1C2014B'),
('Step 6', 'Step 9', to_timestamp('5/21/12 18:07', 'MM/DD/YY HH24:MI'), '1C2014B'),
(NULL, 'Step 1', to_timestamp('6/12/12 10:28', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 1', 'Step 2', to_timestamp('6/13/12 8:36', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 2', 'Step 3', to_timestamp('6/13/12 9:05', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 3', 'Step 4', to_timestamp('6/13/12 10:28', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 4', 'Step 6', to_timestamp('6/13/12 10:50', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 6', 'Step 8', to_timestamp('6/13/12 12:14', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 8', 'Step 4', to_timestamp('6/13/12 15:13', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 4', 'Step 5', to_timestamp('6/13/12 15:23', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 5', 'Step 8', to_timestamp('6/13/12 15:30', 'MM/DD/YY HH24:MI'), '1C2014C'),
('Step 8', 'Step 9', to_timestamp('6/18/12 14:04', 'MM/DD/YY HH24:MI'), '1C2014C');

2. 转换MSSQL查询到PostgreSQL

PostgreSQL没有datediff和isnull函数，需要用PostgreSQL的等价函数替代：

• datediff(second, a, b) → EXTRACT(EPOCH FROM (b - a))：计算两个时间的秒级差值
• isnull(a, b) → COALESCE(a, b)：返回第一个非NULL值
• getdate() → CURRENT_TIMESTAMP：获取当前时间

转换后的查询：

select a1.OrderNumber, a1.New as Step, 
  sum(EXTRACT(EPOCH FROM (COALESCE(a2.timeEntered, CURRENT_TIMESTAMP) - a1.TimeEntered))) as "Total Time in Step (seconds)"
from AuditTrail a1
left join AuditTrail a2
  on a1.New = a2.Old 
  and a1.OrderNumber = a2.OrderNumber
group by a1.OrderNumber, a1.New
order by a1.OrderNumber;

内容的提问来源于stack exchange，提问作者sreeli