8

I'm using PostgreSQL version 14.4. I installed the uuid-ossp extension.

I created a table like this:

CREATE TABLE reserved_words 
  ADD id uuid NOT NULL DEFAULT uuid_generate_v1()
  ADD word NOT NULL varchar(20);

Unfortunately, when I try adding a new record, rather than a new UUID being generated, instead the "uuid_generate_v1()" string is added in as the id!

I've scoured the Internet but can't find out how to alter things so that the function itself is executed. Any ideas?

Improve this question
3
  • 5
    Since Postgres 13 you can use the built-in function gen_random_uuid no need to install an extension Commented Jul 5, 2022 at 17:27
  • For versions other than v4 (random), the uuid-ossp extension must be installed. Commented Apr 29, 2023 at 17:52
  • 1
    uuid v1 is essentially mac address + a timestamp. uuid v4 is a cryptographically secure hash that is guaranteed unique for a few centuries, with the odds of a collision being somewhat less than the odds of randomly picking an individual electron out of tevery electron in hte entire universe. Commented Jan 16, 2024 at 18:49

4 Answers 4

Reset to default
4

The foremost point is that data type should be of uuid

The 'uuid-ossp' extension offers functions to generate UUID values.

To add the extension to the database run the following command

CREATE EXTENSION "uuid-ossp";

you can use the core function gen_random_uuid() to generate version-4 UUIDs.

To make use of this function in dbeaver ,follow the steps:

1.Go to table in which you want to generate UUID's

2.In the table properties tab find the column to which you need to apply the uuid function

3.Double click on the column name and it will show expanded view of it's properties

4.Under default value of that particular column properties, write the function name as shown in the image gen_random_uuid()

Dbeaver illustration

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

gen_random_uuid() works in pgAdmin as well, no need for extensions
@PiotrWojsa The CREATE EXTENSION ... is for the server, not the client. It is required once to make the mentioned function available in the server.
@SebDieBln You don't need this extension at all for the function gen_random_uuid() since it's a standard function since PostgreSQL version 13. Using an extension to get v7 (that includes a timestamp) would make sense to me, but is not available in this extension. So why would you install this extension?
3

My apologies, it actually does work. What's happening is that in DBeaver, the DB client I user, it does at first show the UUID generation function but then when you save the new record, it creates the UUID correctly.

Note: I don't really understand the difference between uuid_generate_v1 and uuid_generate_v4 but am going to opt to use the latter one.

Improve this answer

1 Comment

I think the point was that a v1 hash is predictable since its generated from MAC address and a timestamp so is a security concern but v4 is generated differently and harder to guess / generate.
2

uuid_generate_v1 () → uuid

Generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.

uuid_generate_v4 () → uuid

Generates a version 4 UUID, which is derived entirely from random numbers.

source

Improve this answer

Comments

0

Ctrl + Alt + Shift + U - is the shortcut to insert UUID
works in SQL & Table editor
version: 25.1.5 (Windows)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.