Massive delete based on subquery on WHERE statement. Postgres

Question

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.

Answer 1

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.

Answer 2

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)