Mammoth
  • |
  • Contact
  • |
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting
  • |
  • |
  • |
  • |
  • |
PostgreSQL Gotcha: Default timestamps are not exact!
Posted Tuesday Oct 9th, 2007 03:54pm
by Joshua Drake
| Permalink

Follow cmdpromptinc on Twitter


If you are compiling PostgreSQL from source you have a configure option called:
--enable-integer-datetimes
Now Debian/Ubuntu wisely turn this option on by default, unfortunately the RPM provided by PostgreSQL.Org and the RPM provided by RedHat/Fedora do not. Why is this a problem? I think we can all agree that given that a particular value is within a column that value should be able to be retrieved from that column. Consider the following:
SELECT created FROM foo WHERE id = 2630863;
         created        
-------------------------------
 2007-08-29 12:35:27.897597-04
O.k. simple enough. Now consider this:
SELECT created FROM foo WHERE created = '2007-08-29 12:35:27.897597-04';

 created
--------------
(0 rows)
What? That doesn't seem to make sense does it? If we continue our testing:
SELECT created FROM foo WHERE created = '2007-08-29 12:35:27.897597-04'  
   AND id = 2630863;

 created
--------------
(0 rows)
And:
SELECT created FROM foo WHERE created ilike '2007-08-29 12:35:27.897597-04' 
   AND id = 2630863;
         created
-------------------------------
 2007-08-29 12:35:27.897597-04
Say what? Heh... In short... if you can, always use --integer-datetimes. So why is this a really big deal? It is a really big deal because you must initdb to fix the problem. That means a dump and reload. That means a significant outage.

Categories: PostgreSQL, OpenSource

blog comments powered by Disqus

Copyright © 2000-2014 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.