Getting all entries whose birthday is today in PostgreSQL

In case it matters, the age function will let you work around the issue of leap years:

where age(cd.birthdate) - (extract(year from age(cd.birthdate)) || ' years')::interval = '0'::interval

It case you want performance, you can actually wrap the above with an arbitrary starting point (e.g. 'epoch'::date) into a function, too, and use an index on it:

create or replace function day_of_birth(date)
  returns interval
as $$
  select age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval;
$$ language sql immutable strict;

create index on client_contacts(day_of_birth(birthdate));

...

where day_of_birth(cd.birthdate) = day_of_birth(current_date);

(Note that it's not technically immutable, since dates depend on the timezone. But the immutable part is needed to create the index, and it's safe if you're not changing the time zone all over the place.)

---

EDIT: I've just tested the above a bit, and the index suggestion actually doesn't work for feb-29th. Feb-29th yields a day_of_birth of 1 mon 28 days which, while correct, needs to be added to Jan-1st in order to yield a valid birthdate for the current year.

create or replace function birthdate(date)
  returns date
as $$
  select (date_trunc('year', now()::date)
         + age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval
         )::date;
$$ language sql stable strict;

with dates as (
  select d
  from unnest('{
    2004-02-28,2004-02-29,2004-03-01,
    2005-02-28,2005-03-01
  }'::date[]) d
)
select d,
       day_of_birth(d),
       birthdate(d)
from dates;

     d      | day_of_birth  | birthdate  
------------+---------------+------------
 2004-02-28 | 1 mon 27 days | 2011-02-28
 2004-02-29 | 1 mon 28 days | 2011-03-01
 2004-03-01 | 2 mons        | 2011-03-01
 2005-02-28 | 1 mon 27 days | 2011-02-28
 2005-03-01 | 2 mons        | 2011-03-01
(5 rows)

And thus:

where birthdate(cd.birthdate) = current_date

You could set your where clause to:

WHERE
    DATE_PART('day', birthdate) = date_part('day', CURRENT_DATE)
AND
    DATE_PART('month', birthdate) = date_part('month', CURRENT_DATE)