Using UUID Library in Postgres 9.0 and Earlier

UPDATE: This info applies only to Postgres versions earlier than 9.1, such as 8.4 and 9.0. For 9.1, see my later post on "Extensions in Postgres".

If you are using a version of Postgres installed by installers provided by EnterpriseDB, then your installation includes a library for generating UUID values. The library is known as uuid-osspc and is described in this doc. Commands such as "uuid_generate_v1()" return the hex string representation of a UUID’s 128-bit value.

While this library may be included in your Postgres, it is not enabled by default. To enable, you need to execute some SQL commands to create some function definitions within your Postgres database. Fortunately, these SQL commands have already been written for you. You just need to execute a provided SQL script. Find that script in folder such as this one on my Mac:

/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sql

Either execute that script directly, or copy-paste its contents to pgAdmin’s "SQL" (tab) window. If run successfully, pgAdmin displays a message saying "Query returned successfully". And you should see a bunch of functions listed under your database’s definition, such as YourDatabase > Schemas > public > Functions. You may need to refresh pgAdmin's display by context-clicking on your database in the "Server Groups" listing, to choose the menu item "Refresh".

Actually, in my install of Postgres 9.0.4 I encountered an apparent glitch where the list of functions would not refresh until I added a table. This may be a bug in pgAdmin – I’m not sure.

To try the library, run this SQL:

SELECT uuid_generate_v1();