Hi
Well I do not pretend to give an answer, it is rather a question -I am a novice in postgresql two!-
Why not to split the table in two, the second ones inherits the first. Your datetime column is present only in the daughter. Parent table are undeleted, daughter one are deleted. A trigger on the parent table fils the daughter one for DELETE queries, and you can make your queries on the parent one with SELECT ONLY or SELECT depending on your need to have or not to have the deleted rows
Hopefully, expert will provide us with the advantages and drawbacks of both solutions!
Romain
Date: Sun, 26 Aug 2012 19:08:36 +1200
From: jemmyw@gmail.com
To: pgsql-novice@postgresql.org
Subject: [NOVICE] best index for timestamp field null and not null queries
Hi, I've got a database table with a datetime column "deleted_at". I'll be running lots of queries against the table but they'll all be of the nature "deleted_at IS NULL" and "deleted_at IS NOT NULL".
What's the best index to place on this column? I assume if I add just a straight index then it'll index all of the values that go into it.