Insert nulls during bulk insert for Postgres-CodePudding

I am trying to convert this sqlfiddle http://www.sqlfiddle.com/#!3/fbaff/14 from MSSQL Server to Postgres but cannot get the sample data script to insert null values. My end goal is this convert this query to work on the same data and structure, but in Postgres:

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

I have tried various versions of "", '', NULL, and 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')

CodePudding user response：

try this:

insert into AuditTrail values
( 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');

I think you need the VALUES keyword.

Also the double-quotes around the table name probably causes a parse error.