I found this query in our codebase:

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:

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.)

  • 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, 2022 at 17:41
  • @Akina Thanks that is a good tip. In this case though they are both declared as non-null.
    – lonix
    Apr 6, 2022 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, 2022 at 14:58

1 Answer 1


Both queries are ridiculously complicated. Use

WHERE "CreatedAt" < current_timestamp;
  • 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, 2022 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, 2022 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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