Error in using with statement with PostgreSQL insert query

Question

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?

You need some statement after the WITH. As written your WITH statement makes no sense — user330315
– user330315, Commented Jul 21, 2021 at 7:32
You mean, the variable declared should be used below the first statement. — Asnim P Ansari
– Asnim P Ansari, Commented Jul 21, 2021 at 7:35
What are you trying to achieve? What's different that you want to happen with the with clause that doesn't happen in normal insert? Cause yeah, normal insert works, there doesn't appear to be a need for anything more complex. — Deltharis
– Deltharis, Commented Jul 21, 2021 at 7:39
@Deltharis, I was trying to use row in inserted_record and use its id as foreign key in another table. — Asnim P Ansari
– Asnim P Ansari, Commented Jul 21, 2021 at 7:45

Laurenz Albe · Accepted Answer · 2021-07-21 08:07:49Z

1

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.

edited Jul 21, 2021 at 8:07

answered Jul 21, 2021 at 8:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Error in using with statement with PostgreSQL insert query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related