My tables make use of UUID values. I am inserting rows using SQL SQLWorkbench/J but I don't see how I can generate UUIDs when performing a SQL INSERT INTO (-want-to-generate-uuid-here, '5', 'some value'). Is there a way to generate UUIDs with SQLWorkBench/J?
Thanks
Use
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
then you can use uuid_generate_v4().
Since Postgres 13, UUIDs can be geneerated with gen_random_uuid() without needing the uuid-ossp extension. This returns a Version 4 UUID value.
Since Postgres 18, UUIDs can be generated with uuidv4() (an alias for gen_random_uuid()) and uuidv7() for UUID version 4 and 7 respectively.
The simplest solution is gen_random_uuid():
insert into tableName (id)
values (gen_random_uuid())
You asked:
how I can generate UUIDs when performing a SQL INSERT
Call a function that generates a UUID value on-the-fly.
To generate a UUID value, you no longer need the uuid-ossp extension discussed in older Answers on Stack Overflow.
In Postgres 18+, use Version 7 UUID values generated by the built-in function, uuidv7. Version 7 is designed for efficient indexing in a database.
CREATE TABLE example_ (
id_ UUID PRIMARY KEY DEFAULT uuidv7() ,
…
);
For more details, see my Answer on similar Question.
Define a table whose primary key is a UUID with Version 7 values generated by default.
CREATE TABLE example_ (
id_ UUID PRIMARY KEY DEFAULT uuidv7(),
description_ VARCHAR NOT NULL
);
Insert a row. Notice how the UUID is unspecified in the following SQL. Postgres will automatically call the uuidv7 function we defined as a default for our id_ column`.
INSERT INTO example_ (description_) VALUES ( 'alpha' ) ;
Dump to console.
SELECT * FROM example_ ;
id_ | description_
--------------------------------------+--------------
0199da69-65bb-70ff-b866-063dcd72ea01 | alpha
(1 row)
Delete that row.
DELETE FROM example_ WHERE id_ = '0199da69-65bb-70ff-b866-063dcd72ea01' :: UUID ;
The :: cast may not be required in all situations, but some folks recommend it for clarity.
insert how should we generate the UUID ? do we need to call any special function ?DEFAULT as seen here, the function is called automatically for each INSERT. Or the calling app can supply an UUID value. Otherwise, you need to call the function explicitly where you need a value. For example: SELECT uuidv7() ;insert into example values (DEFAULT) correct or insert into example values(uuidv7()), let me rephrase my question, how do we write insert statement for the table you defined in the answer ? actually it would help others if you added into the answer for future reference, not many people read comments. and thank you
default keyword in your values list: INSERT INTO example_ VALUES (DEFAULT, 'alpha' );