theGamblerRises theGamblerRises - 2 months ago 12
Java Question

Postgres : syntax error at or near "as"

I am using JOOQ for java to postgresql db connection. It is trying to run this query.

insert into "public"."mission_batches" as "MB"
("micro_task_id", "is_active", "created_at", "updated_at")
values
('7e1cc9e8-fc11-409b-865e-3bf08e6ca924', false, timestamp '2016-10-05 21:47:13.061', timestamp '2016-10-05 21:47:13.061') returning "MB"."id", "MB"."micro_task_id", "MB"."is_active", "MB"."created_at", "MB"."updated_at"


But I am getting error from DB

org.jooq.exception.DataAccessException: SQL [insert into "public"."mission_batches" as "MB" ("micro_task_id", "is_active", "created_at", "updated_at") values (?, ?, cast(? as timestamp), cast(? as timestamp)) returning "MB"."id", "MB"."micro_task_id", "MB"."is_active", "MB"."created_at", "MB"."updated_at"];
ERROR: syntax error at or near "as"
Position: 40


It is working with my local DB[9.5]. On test server [9.4], it is throwing this error. Where should I look for fix? Java side or PG side?

Answer

Aliasing the table you're inserting into was only added in Postgres 9.5 (compare Postgres 9.5's documentation to 9.4's documentation. Since the columns in the returning clause refer to the inserted table anyway, you could just as easily do without it:

insert into "public"."mission_batches"
  ("micro_task_id", "is_active", "created_at", "updated_at") 
values 
  ('7e1cc9e8-fc11-409b-865e-3bf08e6ca924', false,
   timestamp '2016-10-05 21:47:13.061', timestamp '2016-10-05 21:47:13.061') 
returning 
  "id", "micro_task_id", "is_active", "created_at", "updated_at"