CODEWITHSUNDEEP
postgresql
MegaRoks
MegaRoks

Reputation: 948

How to combine select and insert into sql

I have an add request:

INSERT INTO LIKES_PRODUCTS AS L (L.USER_ID, L.PRODUCT_ID) VALUES('7', '1')

There is a request for the number of rows in the table:

SELECT COUNT(L.USER_ID) AS LIKES FROM LIKES_PRODUCTS AS L

Is it possible to combine them into a single query, so that the addition occurs first, and then only the counting of rows in the table?

Upvotes: 1

Views: 1481

Answers (1)

user330315
user330315

Reputation:

You can do that with a data modifying CTE

with new_row as (
  insert into likes_products (user_id, product_id)
  values (7,1)
)
select count(user_id) as likes
from likes_products;

However, the final select does not see the effects of the previous CTE. If you always insert one row, you can simply count(user_id) + 1 in the select. Another option is to return the inserted rows and add them to the count:

with new_rows as (
  insert into likes_products (user_id, product_id)
  values (7,1),(8,2)
  returning *
)
select count(user_id) + (select count(*) from new_rows) as likes
from likes_products;

Upvotes: 5

Related Questions