Generate a UUID in PostgreSQL
While inserting a value into a PostgreSQL table, you may encounter an error if the
uuid-ossp extension is missing.
uuid-ossp is a contrib module and is not loaded into the server by default. To use it, you need to load it into your database.
dbdocs=> insert into dbdocsSales values (uuid_generate_v4(),'101','Mark','Smith','[email protected]','0000000000');
ERROR: function uuid_generate_v4() does not exist
LINE 1: insert into dbdocsSales values (uuid_generate_v4(),'101','Ma...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
dbdocs=>
Is the Extension Available?
When you try to install an extension in PostgreSQL, it's important to ensure that the extension has been specifically built for your Postgres installation or cluster.
To determine whether the
uuid-ossp extension is available in your Postgres cluster, you can execute a SQL query to check the
pg_available_extensions system catalog. This catalog provides information about the extensions that are available for installation in your specific PostgreSQL environment
dbdocs=# SELECT * FROM pg_available_extensions;
Install the Extension
If the extension is not already installed, you can create it using the following command for modern versions of PostgreSQL (9.1 and newer)
dbdocs=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
dbdocs=#
Run the insert statement again -
dbdocs=> insert into dbdocsSales values (uuid_generate_v4(),'101','Mark','Smith','[email protected]','0000000000');
INSERT 0 1
dbdocs=> select * from dbdocsSales;
sales_id | sales_number | first_name | last_name | email | phone
--------------------------------------+--------------+------------+-----------+-----------------------+------------
655cfeba-e717-4003-b711-e0ba63c6ec45 | 101 | Mark | Smith | [email protected] | 0000000000
(1 row)
dbdocs=>
Related content