Postgresql INSERT query for timestamp returns ERROR: syntax error at or near ":" SQL state: 42601 Character: 191

Question

The SQL for table changeset is:

CREATE TABLE changesets
    (
      changeset_id bigint NOT NULL,
      user_id integer NOT NULL,
      created_at timestamp with time zone,
      closed_at timestamp with time zone,
      open boolean,
      min_lat double precision,
      min_lon double precision,
      max_lat double precision,
      max_lon double precision,
      CONSTRAINT pk_changeset PRIMARY KEY (changeset_id )
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE changesets
      OWNER TO postgres;

I tried to run the query:

INSERT INTO changesets (changeset_id,user_id,open,min_lat,min_lon,max_lat,max_lon,created_at,closed_at) 
SELECT 16465146,1315463,false,27.6785105,85.3408257,27.6849711,85.350291,2013-06-08T06:29:51Z,2013-06-08T06:32:52Z 
WHERE NOT EXISTS (SELECT * FROM changesets WHERE changeset_id=16465146);

But it throws the error:

ERROR:  syntax error at or near ":"
LINE 1: ...5105,85.3408257,27.6849711,85.350291,2013-06-08T06:29:51Z,20...
                                                             ^

********** Error **********

ERROR: syntax error at or near ":"
SQL state: 42601
Character: 191

If I remove the created_at,closed_at target-columns ind their timestamp-values 2013-06-08T06:29:51Z,2013-06-08T06:32:52Z from the SQL then the sql is executed succesfully.

What is wrong in this SQL? Is there problem with :,- of the timestamp?.

Thanks,

Answer 1

Pass timestamps as string literals
Refer to documentation

Try this insert:

INSERT INTO changesets 
(changeset_id,user_id,open,min_lat,min_lon,max_lat,max_lon,created_at,closed_at) 
SELECT 16465146,1315463,false,27.6785105,85.3408257,27.6849711,85.350291,
      '2013-06-08T06:29:51Z','2013-06-08T06:32:52Z' 
WHERE NOT EXISTS (SELECT * FROM changesets WHERE changeset_id=16465146);

or even better give them explicit types like here:

INSERT INTO changesets (changeset_id,user_id,open,min_lat,min_lon,max_lat,max_lon,created_at,closed_at) 
SELECT 16465146,1315463,false,27.6785105,85.3408257,27.6849711,85.350291,
TIMESTAMP WITH TIME ZONE '2013-06-08T06:29:51Z', 
TIMESTAMP WITH TIME ZONE '2013-06-08T06:32:52Z' 
WHERE NOT EXISTS (SELECT * FROM changesets WHERE changeset_id=16465146);