Question 185059 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Massive delete based on subquery on WHERE statement. Postgres

I need made a subquery with IN statement. Like this (simplify):

DELETE
FROM table_1 
WHERE id_table_1 IN (SELECT string_agg(fk_id_table_1::TEXT,',') FROM table_2

I need made a massive delete, based on second subquery. The problem is if i use IN statement i must use string_agg to put the id separeted by commas, but id is integer and string_agg need change to text, if i put id_table_1::text IN (…subquery…) it return empty. I try using ANY too and put the id inside a integer array. But it show "ERROR: operator does not exist: integer = integer[]". I try use EXISTS too.

Somebody give me some light? Thanks a lot.

Tags:

2

Answers


  1. 0

    While you can code a literal values for a IN using a comma delimited list, eg WHERE id_table_1 IN (1,2,3), you can’t use string_agg in the way you’re using it. Instead, use a query that returns a single column to create the list of values, like this:

    DELETE
FROM table_1 
WHERE id_table_1 IN (SELECT fk_id_table_1 FROM table_2)

    Better, use postgres’s join delete syntax:

    DELETE FROM table_1
USING table_2
WHERE table_1.id_table_1 = table_2.fk_id_table_1

    You could use string_agg() if you were programatically building a query as a string, but that’s not the situation here.

    Login or Signup to reply.
  2. 0

    You don’t need to use the string_agg function in the sub-query (actually, this will return a single value of all aggregated ids! hence, no match with any of table 1 ids).

    The syntax of the IN operator is: value IN (value1, value2,...),
    where the list of values can be a list of literal values such as numbers, strings… or a *result of a select statement (sub-query) as WHERE value IN (SELECT col FROM table_name).

    So you can fix your query as the following:

    DELETE
FROM table_1 
WHERE id_table_1 IN (SELECT fk_id_table_1 FROM table_2)

    And using the exists operator:

    DELETE
FROM table_1 T1
WHERE EXISTS (SELECT 1 FROM table_2 T2 WHERE T2.fk_id_table_1 = T1.id_table_1)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search