Generating a UUID in Postgres for Insert statement?

Question

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.

Answer 1

uuid-ossp is a contrib module, so it isn't loaded into the server by default. You must load it into your database to use it.

For modern PostgreSQL versions (9.1 and newer) that's easy:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

but for 9.0 and below you must instead run the SQL script to load the extension. See the documentation for contrib modules in 8.4.

For Pg 9.1 and newer instead read the current contrib docs and CREATE EXTENSION. These features do not exist in 9.0 or older versions, like your 8.4.

If you're using a packaged version of PostgreSQL you might need to install a separate package containing the contrib modules and extensions. Search your package manager database for 'postgres' and 'contrib'.

Answer 2

Without extensions (cheat)

If you need a valid v4 UUID

SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::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

---

Or you can simply get UUID-like value by doing this (if you don't care about the validity):

SELECT uuid_in(md5(random()::text || random()::text)::cstring);

output>> c2d29867-3d0b-d497-9191-18a9d8ee7830

(works at least in 8.4)

Answer 3

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.

db<>fiddle demo

Answer 4

The answer by Craig Ringer is correct. Here's a little more info for Postgres 9.1 and later…

Is Extension Available?

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;

Install Extension

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".

Verify Installation

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;

UUID as default value

For more info, see the Question: Default value for UUID column in Postgres

The Old Way

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.

Types of UUIDs

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.

Answer 5

pgcrypto Extension

As 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.

Answer 6

Update from 2021, There is no need for a fancy trick to auto generate uuid on insert statement.

Just do one thing:

1. Set default value of DEFAULT gen_random_uuid () to your uuid column. That is all.

Say, you have a table like this:

CREATE TABLE table_name (
    unique_id UUID DEFAULT gen_random_uuid (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (unique_id)
);

Now you need NOT to do anything to auto insert uuid values to unique_id column. Because you already defined a default value for it. You can simply focus on inserting onto other columns, and postgresql takes care of your unique_id. Here is a sample insert statement:

INSERT INTO table_name (first_name, last_name, email, phone) 
VALUES (
    'Beki',
    'Otaev',
    'beki@bekhruz.com',
    '123-456-123'
)

Notice there is no inserting into unique_id as it is already taken care of.

About other extensions like uuid-ossp, you can bring them on if you are not satisfied with postgres's standard gen_random_uuid () function. Most of the time, you should be fine without them on

Answer 7

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs)

uuid_generate_v1() This function generates a version 1 UUID.

1. Add Extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. Verify Extension

SELECT * FROM pg_extension;

3. Run Query

INSERT INTO table_name(id, column1, column2 , column3, ...) VALUES (uuid_generate_v1(), value1, value2, value3...);

Verify table data

Answer 8

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.

Answer 9

Postgres v13+

INSERT INTO your_table
VALUES (gen_random_uuid(), 'value column 2', 'value column 3')

Answer 10

first, you need to add extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Answer 11

On postgres version less than 13:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- gives:

SELECT gen_random_uuid();

On postgres version 13+:

SELECT gen_random_uuid();

In context when you don't know the postgres version but presume it's at least 9, and it matters to you not to enable the extension unnecessarily:

DO
$do$
BEGIN
  IF current_setting('server_version_num')::integer < 130000 THEN
    CREATE EXTENSION IF NOT EXISTS "pgcrypto";
  END IF;
END
$do$

SELECT gen_random_uuid();

Answer 12

SELECT uuid_generate_v5(uuid_ns_url (), 'test');