0

More precisely: is there an elegant/simple way to ensure that any entities added to a given table are given a random unique identifier? In other words: is it possible to configure PostgreSQL (or rather, a table therein) where a random unique identifier is generated by the database upon the insertion of an entity into the table? I feel like I remember some time ago hearing or seeing something about a solution where you simply need to "input" only the data that you're concerned with, and the DB will automatically generate an ID for every such entry.

So, suppose I have a table where none of the attributes are suitable to be used as an ID, and I want an attribute by which I will easily be able to identify each of them; eg. I want to have a table with email, username, password (I know this isn't a good example as both the username and email attributes are suitable to be used as unique ID-s, but suppose they're not), and I want to be able to insert an entity into that table by providing only the email, username, and password, but for security reasons (or whatever other reasons) I don't want the unique ID to be determined sequentially (ie. starting from 1 and then incrementing for every subsequent entry inserted). So optimally, I would like to be able to input email: billybob@mail.com; username: billybob; password: password123, and then the database adding an entity that, along with all of those, has a user_ID of 275234318, for example. (I don't care whether it's a purely numeric ID or if it's comprised of letters and/or special characters as well, I just care that it works, and that it happens automatically)

What would be the most elegant way to achieve this? And to be clear, I'm asking this because I'm not entirely satisfied with the suggestion to generate a random number and check whether an entry with that ID already exists in the table every time I want to insert a new entity, because that's clunky and requires a stand-alone procedure to be implemented.

Ganondorf10987
  • 13
  • 3
  • There is extension uuid-ossp for creating UUID values as primary keys. You can define it as MyID UUID NOT NULL DEFAULT uuid_generate_v4(). – Cetin Basoz Nov 14 '21 at 19:13
  • 2
    Does this answer your question? [Generating a UUID in Postgres for Insert statement?](https://stackoverflow.com/questions/12505158/generating-a-uuid-in-postgres-for-insert-statement) – Haleemur Ali Nov 14 '21 at 19:17
  • 2
    for recent versions of postgresql (13+) you don't need to install the `uuid-ossp` module, example table DDL: `create table test (id uuid primary key default gen_random_uuid(), val text not null);`. for earlier versions, you need the extension module for functions to generate the uuid. see this answer for more details https://stackoverflow.com/questions/12505158/generating-a-uuid-in-postgres-for-insert-statement – Haleemur Ali Nov 14 '21 at 19:19
  • Version 4 UUIDs as commented by Haleemur Ali are the solution. – Laurenz Albe Nov 15 '21 at 03:49

0 Answers0