INSERT into table with WITH clause not working in postgres

Question

Sorry for the followup question (from INSERT into table if doesn't exists and return id in both cases)

But I couldn't find any solution for my questions.

I have a feedback table whose columns are foreign key of other tables. For ex. scopeid is foregin key of id column in scope table, similarly userid is foreign key of id column from user table and so on.

So, I am trying to insert following data in the table: scope: home_page, username: abc status: fixed app: demoapp

So, to insert above data, I am trying to write subquery to get the id of each value and use that. Also if that value doesn't exists insert and use the new ID to insert that in feedback table.

So basically I am trying to insert into multiple table (if something doesnt exists) and use those ID to insert into final table which is feedback table.

Hope things are much clearer now.

Here is my feedback table:

id  scopeid   comment   rating   userid   statusid   appid
3     1        test      5        2          1           2

All the id columns are foreign key of other tables and so in my below query I am trying to get the id by name and if not exists add those.

Here is my final query:

INSERT INTO feedbacks (scopeid, comment, rating, userid, statusid, appid)
VALUES
(
-- GET SCOPE ID
 (
  WITH rows_exists AS (
  SELECT id FROM scope 
  WHERE appid=2 AND NAME = 'application'),
  row_new AS (INSERT INTO scope (appid, NAME) SELECT 2, 'application' WHERE NOT EXISTS (SELECT id FROM scope WHERE appid=2 AND name='application') returning id)
  SELECT id FROM rows_exists UNION ALL SELECT id FROM row_new
 ),
-- Comment
 'GOD IS HERE TO COMMENT',
-- rating
 5,
-- userid
 (
  WITH rows_exists AS (
  SELECT id FROM users
  WHERE username='abc'),
  row_new AS (INSERT INTO users (username) SELECT 'abc' WHERE NOT EXISTS (SELECT id FROM users WHERE username='abc') returning id)
  SELECT id FROM rows_exists UNION ALL SELECT id FROM row_new
 ),
-- statusid
 (SELECT id FROM status WHERE NAME='received'),
-- appid
 (
  WITH rows_exists AS (
  SELECT id FROM apps
  WHERE name='google'),
  row_new AS (INSERT INTO apps (name) SELECT 'google' WHERE NOT EXISTS (SELECT id FROM apps WHERE NAME='google') returning id)
  SELECT id FROM rows_exists UNION ALL SELECT id FROM row_new
 )
)

But I get following Error:

with clause containing a data-modifying statement must be at the top level

Is that even possible what I am trying to achieve by this way or other method.

The error seems pretty clear. You can only use insert in a CTE if it is the first CTE in the query. — Gordon Linoff
– Gordon Linoff, Commented May 14, 2019 at 13:21
Are you looking for something like this INSERT INTO example_table (id, name) SELECT 1, 'John' WHERE NOT EXISTS ( SELECT id FROM example_table WHERE id = 1 ); — Manu mathew
– Manu mathew, Commented May 14, 2019 at 13:26
@GordonLinoff so, does that mean its not possible to write above query? — undefined
– undefined, Commented May 14, 2019 at 14:50
This is a confusing query. What are you trying to do? INSERT into feedbacks or multiple inserts into other tables? How does scope, user, status, and apps relate? Please back up and give a fuller description of your XY problem. — Parfait
– Parfait, Commented May 14, 2019 at 15:01

Gordon Linoff · Accepted Answer · 2019-05-14 15:18:09Z

The following inserts ids that don't exist and then inserts the resulting id:

with s as (
      select id
      from scope
      where appid = 2 AND NAME = 'application'
     ),
     si as (
      insert into scope (appid, name)
          select v.appid, v.name
          from (values (2, 'application')) v(appid, name)
          where not exists (select 1 from scope s where s.appid = v.appid and s.name = v.name)
          returning id
     ),
     . . . similar logic for other tables
insert into feedback (scopeid, comment, . . . )
    select (select id from s union all select id from is) as scopeid,
           'test' as comment,
           . . .;

You should be sure you have unique constraints in each of the table for the values you are looking for. Otherwise, you could have a race condition and end up inserting the same row multiple times in a multithreaded environment.

Collectives™ on Stack Overflow

INSERT into table with WITH clause not working in postgres

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