Bulk delete via Direct Database Query

Valentin R

I am wondering if I can take advantage of Direct Database Query to remove multiple (thousands) of records.

My query:

DELETE FROM abc
WHERE id IN (?);

This works if I specify the id values by hand, but when using an argument there I run into an SQL error. Even when I transform an array of ids to a text like "1, 2, 3", the function fails with such an error
SQLSTATE[22P02]: Invalid text representation

1. Is this a better path for deleting thousands of records instead of looping?
2. What can be done to make it run?

Thanks.

Valentin R

To give more context: removing 1000 entries via a loop takes ~6 seconds. I've got 500k entries to remove.

Michael Udinski

@Valentin R — can you share the SQL query that caused the error? Makes me suspect the syntax might have had a mistake in it.

Valentin R

Hi @Michael Udinski

Here's what I was doing at first:
1. Querying records, getting an array of ids:
[{"id":8117},{"id":8118},{"id":8119}]
2. Applying "value" filter to that array which gives me:
[8117,8118,8119]
3. Applying "to_text" filter.
4. Applying "replace" filter to replace each of the brackets.
5. Using this var in the query:
DELETE FROM xxxx WHERE id IN ('?');

This results in an error:
{"message":"xano\extension\xs\Strings::xano\extension\xs\{closure}(): Return value must be of type string, array returned"}

I have also tried more complicated queries, but nothing worked out:
DELETE FROM table_name
WHERE column_name = ANY(array_variable);

DELETE FROM table_name
WHERE column_name = ANY(json_array);

DELETE FROM table_name
WHERE to_jsonb(id) = ANY('[{"id":8117},{"id":8118},{"id":8119}]'::jsonb);

DELETE FROM table_name
WHERE jsonb_build_object('id', id) IN (
'{"id": 8117}', '{"id": 8118}', '{"id": 8119}', '{"id": 8120}'
);

Valentin R

Any ideas here @Michael Udinski ?
And is there actually any benefit of doing it this way vs looping? I assume there is.

Valentin R

Solved with help from @Chris Coleman

Instead of doing it by id, I used this

DELETE FROM xxxx WHERE "status" = 'abc';