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,

Ihor Romanchenko · Accepted Answer · 2013-07-29 09:44:25Z

2

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);

edited Jul 29, 2013 at 9:44

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

answered Jul 28, 2013 at 10:42

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ram Shrestha Over a year ago

Thank You. Both methods worked. Now I am reading the documentation.

Ihor Romanchenko Over a year ago

Please do not link to the old versions of the documentation. Use docs/current/static instead of docs/8.4/static.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related