Postgres WITH query and multiple subsequent statements

Question

I need to delete from multiple tables, according to the contents of another table. To avoid having to re-declare a query in every statement, I'm using WITH:

WITH users_to_delete AS (
  SELECT id FROM auth.user WHERE email IN (
    '[email protected]',
    '[email protected]'
  )
)

DELETE FROM schema1.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema2.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema3.table WHERE "userId" IN (SELECT id FROM users_to_delete);

When trying to execute this, I get an error on the second statement of

relation "users_to_delete" does not exist

Is there a way to re-use the users_to_delete for multiple statements?

Gordon Linoff · Accepted Answer · 2019-07-25 10:44:29Z

11

Use multiple CTEs:

WITH users_to_delete AS (
      SELECT id
      FROM auth.user
      WHERE email IN ('[email protected]', '[email protected]')
     ),
     d1 AS (
      DELETE FROM schema1.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     ),
     d2 AS (
      DELETE FROM schema2.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     )
DELETE FROM schema3.table
WHERE "userId" IN (SELECT id FROM users_to_delete);

The returning clauses are not needed. I just find it strange to have CTEs that are entirely empty.

answered Jul 25, 2019 at 10:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

FatFreddy · Accepted Answer · 2019-07-25 11:35:58Z

3

use a helper or temp table:

    begin;

    create temp  table users_to_delete () INHERITS  (auth.user);
    insert into users_to_delete  (SELECT id FROM auth.user WHERE email IN (
        '[email protected]',
        '[email protected]')     );

    DELETE FROM schema1.table WHERE "userId" IN (SELECT id FROM users_to_delete);
    DELETE FROM schema2.table WHERE "userId" IN (SELECT id FROM users_to_delete);
    DELETE FROM schema3.table WHERE "userId" IN (SELECT id FROM users_to_delete);

    ALTER TABLE users_to_delete  NO INHERIT auth.user;
    drop table users_to_delete;
    commit;

edited Jul 25, 2019 at 11:35

answered Jul 25, 2019 at 11:27

FatFreddy

1,2101 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Postgres WITH query and multiple subsequent statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related