we (GNUmed) run a medical database on PostgreSQL. We are
very pleased with it (PostgreSQL, that is 😉 in all aspects.
The date-of-birth field in our table holding patients is of
type “timestamp with time zone”. One of our patient search
queries uses the date-of-birth field to find matches. Since
users enter day, month, and year but not hour, minute, and
second of the DOB we run the query with
date_trunc(‘day’, dob) = date_trunc(‘day’, what_the_user_entered_as_dob) and
(appropriately escaped, of course)
The actual DOB is indeed stored with hour, minute and second
so the time information is not redundant but we don’t need
it for searching.
So I figured it would make sense to add a functional index
on date_trunc(‘day’, dob) to the patients table. Which
worked (appeared to, at least) with PG 7.4.
One of our users is on PG 8.2 and gets the warning that
date_trunc() is not immutable and can thus not be used in a
functional index. Makes sense all by itself. What I don’t
understand, however, is exactly *why* date_trunc is not
immutable ? All it does is extracting part of the
information that’s there anyways. One would assume it to be
the timestamp equivalent of substring(), no ? (not a good
example, perhaps, as that might depend on encoding
settings…) It *shouldn’t* really depend on, say, date/time
related locale settings, should it ?
I’d be happy to provide more details if that is needed for
which I’d have to contact the user in question.
Thanks for any insight offered,
The code to create the index:
create index idx_identity_dob_ymd on dem.identity(date_trunc(‘day’, dob))
The exact error it emits:
functions in index expression must be marked IMMUTABLE
Those were lifted from the error log without further editing.
I know that I could fake immutability by wrapping
date_trunc() in a stored procedure marked IMMUTABLE but
I wonder what pitfalls that might hold.
For the record, this was changed just before 8.0 release: