Archive for the ‘PostgreSQL’ Category

PostgreSQL getting the lastest item from a history table (the entry with the latest timestamp)

Friday, March 16th, 2012
SELECT * FROM history_items
WHERE timeofhistory=(SELECT MAX(timeofhistory) FROM history_items)

The sub query will fetch the highest timestamp and then that is used as a condition for the query.

note: If you are using a version older than 8.1, then MIN and MAX is not able to use indexes, so instead use a ORDER BY and LIMIT clause

SELECT * FROM history_items
ORDER BY timeofhistory DESC LIMIT 1

This will of course work for versions 8.1 and higher as well, it should be identical to MAX and MIN in time.

PostgreSQL Getting a unix time from a timstamp with time zone

Friday, March 16th, 2012

If we have a timestamp with a timezone then getting the unix time from this is simple.
Note now() returns a timestamp with timezone so this query gets the current unix time

SELECT EXTRACT(epoch FROM now());

PostgreSQL 8.3 adding uuid-ossp on Debian

Sunday, October 3rd, 2010

UUID is a nice way to generate (almost) unique id’s.

However on PostgreSQL 8.3 the data type exists, but if you wish to be able to generate UUID’s you need to get a module (in this case uuid-ossp).

On Debian this module is in the contrib package, so if you have not installed this before do it now

user@server:~$ sudo apt-get install postgresql-contrib

Then you need to “load” the module into PostgreSQL; or more accurately you tell PostgreSQL about the new functions this module has, how to reach them etc.
Depending on you security settings (in pg_hba.conf) you may need to log in as the postgres user on your system.

postgres@sever:~$ psql -d MYDB -U postgres -f /usr/share/postgresql/8.3/contrib/uuid-ossp.sql