My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right?
The problem is the following line returns an error:
honeydb=# insert into items values(
uuid_generate_v4(), 54.321, 31, 'desc 1', 31.94);
ERROR: function uuid_generate_v4() does not exist
LINE 2: uuid_generate_v4(), 54.321, 31, 'desc 1', 31.94);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
I've read the page at: http://www.postgresql.org/docs/current/static/uuid-ossp.html
I'm running Postgres 8.4 on Ubuntu 10.04 x64.
This question is related to
postgresql
uuid
postgresql-8.4
PostgreSQL 13 supports natively gen_random_uuid ():
PostgreSQL includes one function to generate a UUID:
gen_random_uuid () ? uuid
This function returns a version 4 (random) UUID. This is the most commonly used type of UUID and is appropriate for most applications.
SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring);
output>> c2d29867-3d0b-d497-9191-18a9d8ee7830
(works at least in 8.4)
clock_timestamp()
explanation.If you need a valid v4 UUID
SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || clock_timestamp()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8+1) + 8)::int)::text from 17)::cstring);
* Thanks to @Denis Stafichuk @Karsten and @autronix
Also, in modern Postgres, you can simply cast:
SELECT md5(random()::text || clock_timestamp()::text)::uuid
pgcrypto
ExtensionAs of Postgres 9.4, the pgcrypto
module includes the gen_random_uuid()
function. This function generates one of the random-number based Version 4 type of UUID.
Get contrib modules, if not already available.
sudo apt-get install postgresql-contrib-9.4
Use pgcrypto
module.
CREATE EXTENSION "pgcrypto";
The gen_random_uuid()
function should now available;
Example usage.
INSERT INTO items VALUES( gen_random_uuid(), 54.321, 31, 'desc 1', 31.94 ) ;
Quote from Postgres doc on uuid-ossp
module.
Note: If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead.
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT uuid_in((md5((random())::text))::cstring);
After reading @ZuzEL's answer, i used the above code as the default value of the column id and it's working fine.
The answer by Craig Ringer is correct. Here's a little more info for Postgres 9.1 and later…
You can only install an extension if it has already been built for your Postgres installation (your cluster in Postgres lingo). For example, I found the uuid-ossp extension included as part of the installer for Mac OS X kindly provided by EnterpriseDB.com. Any of a few dozen extensions may be available.
To see if the uuid-ossp extension is available in your Postgres cluster, run this SQL to query the pg_available_extensions
system catalog:
SELECT * FROM pg_available_extensions;
To install that UUID-related extension, use the CREATE EXTENSION command as seen in this this SQL:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Beware: I found the QUOTATION MARK characters around extension name to be required, despite documentation to the contrary.
The SQL standards committee or Postgres team chose an odd name for that command. To my mind, they should have chosen something like "INSTALL EXTENSION" or "USE EXTENSION".
You can verify the extension was successfully installed in the desired database by running this SQL to query the pg_extension
system catalog:
SELECT * FROM pg_extension;
For more info, see the Question: Default value for UUID column in Postgres
The information above uses the new Extensions feature added to Postgres 9.1. In previous versions, we had to find and run a script in a .sql file. The Extensions feature was added to make installation easier, trading a bit more work for the creator of an extension for less work on the part of the user/consumer of the extension. See my blog post for more discussion.
By the way, the code in the Question calls the function uuid_generate_v4()
. This generates a type known as Version 4 where nearly all of the 128 bits are randomly generated. While this is fine for limited use on smaller set of rows, if you want to virtually eliminate any possibility of collision, use another "version" of UUID.
For example, the original Version 1 combines the MAC address of the host computer with the current date-time and an arbitrary number, the chance of collisions is practically nil.
For more discussion, see my Answer on related Question.
Source: Stackoverflow.com