Postgres : syntax error at or near "as"

Question

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?

Mureinik · Accepted Answer · 2016-10-05 16:27:33Z

3

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"

answered Oct 5, 2016 at 16:27

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

3 Comments

theGamblerRises Over a year ago

So, only aliasing for insert is not supported in 9.4 ? Because this query is running perfectly

select "U"."id", "U"."username", "E"."email" from "public"."users" as "U" join "public"."emails" as "E" on "U"."id" = "E"."user_id" where "E"."email" = '[email protected]'

Roberto Over a year ago

@theGamblerRises Yeah, aliasing in select has been there for a long time...

theGamblerRises Over a year ago

Oh, ok. Then I'll change my code and make sure same version in all devices. Thanks

Collectives™ on Stack Overflow

Postgres : syntax error at or near "as"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related