CODEWITHSUNDEEP
javasqlpostgresqlsyntax-errorjooq
theGamblerRises
theGamblerRises

Reputation: 696

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 the test server [9.4], it is throwing this error. Where should I look for fix? Java side or PG side?

Upvotes: 2

Views: 3529

Answers (1)

Mureinik
Mureinik

Reputation: 310993

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"

Upvotes: 3

Related Questions