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

User · Answer

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

User · Answer

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 lt  gt fiddle demo

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Without extensions  cheat   SELECT uuid in md5 random    text    clock timestamp    text   cstring    output gt  gt  c2d29867-3d0b-d497-9191-18a9d8ee7830    works at least in 8 4    Thanks to  Erwin Brandstetter for 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

[postgresql] Generating a UUID in Postgres for Insert statement?

Is Extension Available?

Install Extension

Verify Installation

UUID as default value

The Old Way

Types of UUIDs

Examples related to postgresql

Examples related to uuid

Examples related to postgresql-8.4