Unused columns in WHERE clause



  • 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" 

    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" 

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



  • Both queries are ridiculously complicated. Use

    DELETE FROM "Foo"
    WHERE "CreatedAt" < current_timestamp;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2