CODEWITHSUNDEEP
sqlpostgresql
Asnim P Ansari
Asnim P Ansari

Reputation: 2477

Error in using with statement with PostgreSQL insert query

I am trying to use a with clause with postgres insert statement

with inserted_record as (insert into person_age(person_name, years_old) values ('asnim', 21) returning *);

However, it errors out like

SQL Error [42601]: ERROR: syntax error at end of input
  Position: 108

If i run it with without the the with clause it works

insert into person_age(person_name, years_old) values ('asnim', 21) returning *;

What am I missing here?

Upvotes: 0

Views: 507

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

A CTE (WITH clause) is part of an SQL statement and cannot stand on its own. PostgreSQL complains about an error at the end of the statement, because it expects a following SELECT, INSERT, UPDATE or DELETE.

In a way, a CTE is like a view defined only for a single statement. You cannot define a CTE and then use it with several statements; for that, you could define a temporary view in the pg_temp schema.

Upvotes: 1

Related Questions