use values and select from in the same insert

user_11049921 0
Viewed 40

I'm trying to use 'select from' and 'values' in the same insert but I'm getting an error:

with insert_user as(
 insert into users (user_name) values ('omni')
 returning *
),
insert_articles as( 
 insert into articles (article_text, user_id)
 values ('article text')
 select insert_users.id
 from insert_users
);
postgresql sql
3 Answers

You don't need a second CTE for the insert. Just put it in an outer query and it should work:

with insert_user as(
 insert into users (user_name) values ('omni')
 returning *
)
insert into articles (article_text, user_id)
select 'article text',insert_user.id 
from insert_user;

Demo: db<>fiddle

user_22753880

I think what you want is:

postgres=# WITH insert_user as (
  INSERT INTO users (user_name) VALUES ('omni') RETURNING *
)
INSERT INTO articles (article_text, user_id) SELECT 'article_text', id FROM insert_user;
INSERT 0 1
postgres=# select * from articles;
 article_text | user_id 
--------------+---------
 article_text |       1
(1 row)

postgres=# select * from users;   
 user_name | id 
-----------+----
 omni      |  1
(1 row)
user_121157780

For combine VALUES and SELECT use JOIN or UNION

user_166861300
Related