6

I found this query in our codebase:

DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f"."CreatedAt"
    FROM "Foo" AS "f"
    WHERE "f"."CreatedAt" <= CURRENT_TIMESTAMP);

It deletes records created before the current time.

This gives the same result:

DELETE FROM "Foo"
WHERE "Foo"."CreatedAt"
IN (SELECT "f"."CreatedAt"
    FROM "Foo" AS "f"
    WHERE "f"."CreatedAt" <= CURRENT_TIMESTAMP);

I don't know why the Foo.Id is included in the WHERE clause - maybe leftover junk from various refactorings (e.g. it could have been used for batch delete with ORDER BY "f"."Id" LIMIT 1000). But because it's a PK, I'm reluctant to remove it, as maybe it's there for a reason.

Is there a theoretical / perf reason for having it in there, or are the two queries equivalent?

(This targets both postgres and sqlite.)

3
  • 1
    What will happen to the record that has Id = NULL? What will happen to the record that has CreatedAt = NULL? Think. Then test both queries...
    – Akina
    Apr 6 at 17:41
  • @Akina Thanks that is a good tip. In this case though they are both declared as non-null.
    – lonix
    Apr 6 at 22:43
  • 1
    You may be right about ORDER BY ... LIMIT ... in a historical version. Sqlite allows you to use those clauses directly in DELETE without a subquery, but maybe PostgreSQL doesn't.
    – Barmar
    Apr 7 at 14:58

1 Answer 1

13

Both queries are ridiculously complicated. Use

DELETE FROM "Foo"
WHERE "CreatedAt" < current_timestamp;
2
  • Thanks Laurenz, your query has made my work much simpler! But for my education, are the ones I posted theoretically the same, or is there some difference between them? The PK in there is what confused me.
    – lonix
    Apr 6 at 16:09
  • 1
    They do the same thing, albeit in a more complicated fashion with a self-join. The two queries in your question are different, but have the same effect. As you suspect, there is no good reason to use the primary key. Apr 6 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.