date_trunc on timestamp column returns nothing

Question

I have a strange problem when retrieving records from db after comparing a truncated field with date_trunc().

This query doesn't return any data:

select id from my_db_log 
 where date_trunc('day',creation_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

But if I add the column creation_date with id then it returns data(i.e. select id, creation_date...).

I have another column last_update_date having same type and when I use that one, still does the same behavior.

select id from my_db_log
 where date_trunc('day',last_update_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

Similar to previous one. it also returns record if I do id, last_update_date in my select.

Now to dig further, I have added both creation_date and last_updated_date in my where clause and this time it demands to have both of them in my select clause to have records(i.e. select id, creation_date, last_update_date).

Does anyone encountered the same problem ever? This similar thing works with my other tables which are having this type of columns!

If it helps, here is my table schema:

id serial NOT NULL,
creation_date timestamp without time zone NOT NULL DEFAULT now(),
last_update_date timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT db_log_pkey PRIMARY KEY (id),

I have asked a different question earlier that didn't get any answer. This problem may be related to that one. If you are interested on that one, here is the link.

EDITS:: EXPLAIN (FORMAT XML) with select * returns:

<explain xmlns="http://www.postgresql.org/2009/explain">
  <Query>
    <Plan>
      <Node-Type>Result</Node-Type>
      <Startup-Cost>0.00</Startup-Cost>
      <Total-Cost>0.00</Total-Cost>
      <Plan-Rows>1000</Plan-Rows>
      <Plan-Width>658</Plan-Width>
      <Plans>
        <Plan>
          <Node-Type>Result</Node-Type>
          <Parent-Relationship>Outer</Parent-Relationship>
          <Alias>my_db_log</Alias>
          <Startup-Cost>0.00</Startup-Cost>
          <Total-Cost>0.00</Total-Cost>
          <Plan-Rows>1000</Plan-Rows>
          <Plan-Width>658</Plan-Width>
          <Node/s>datanode1</Node/s>
          <Coordinator-quals>(date_trunc('day'::text, creation_date) &gt;= to_date('2014-03-05'::text, 'yyyy-mm-dd'::text))</Coordinator-quals>
        </Plan>
      </Plans>
    </Plan>
  </Query>
</explain>

Answer 1

"Impossible" phenomenon

The number of rows returned is completely independent of items in the SELECT clause. (But see @Craig's comment about SRFs.) Something must be broken in your db.
Maybe a broken covering index? When you throw in the additional column, you force Postgres to visit the table itself. Try to re-index:

REINDEX TABLE my_db_log;

The manual on REINDEX. Or:

VACUUM FULL ANALYZE my_db_log;

Better query

Either way, use instead:

select id from my_db_log 
where creation_date >= '2014-03-05'::date

Or:

select id from my_db_log 
where creation_date >= '2014-03-05 00:00'::timestamp

'2014-03-05' is in ISO 8601 format. You can just cast this string literal to date. No need for to_date(), works with any locale. The date is coerced to timestamp [without time zone] automatically when compared to creation_date (being timestamp [without time zone]). More details about timestamps in Postgres here:
Ignoring timezones altogether in Rails and PostgreSQL

Also, you gain nothing by throwing in date_trunc() here. On the contrary, your query will be slower and any plain index on the column cannot be used (potentially making this much slower)