Is there a Postgres feature or built-in function that limits the display of uuids to only that needed to make them uniquely identifiable?

Question

It would have to return the portion necessary to uniquely identify the row even if a select statement didn't return all rows, of course, to be of any use. And I'm not sure how it would work if the uuid column were not part of a pk/index and was repeated.

Does this exist?

There's nothing built-in. You would need to maintain some sort of speicalised prefix tree index for them and of course between the time of you reading the shortened key and later trying to use it that short version could be invalidated. You obviously couldn't use it anywhere else in the database at all. I'm struggling to see how you might use this. — Richard Huxton
– Richard Huxton, Commented Apr 20, 2017 at 20:48
Obviously this would be for select-only statements. Would mostly be to keep a report a little less crowded but still include the key. Sort of how git only shows the first few hex of an md5. — John O
– John O, Commented Apr 20, 2017 at 20:56
Except by the time I read the report the key displayed might not be unique any more. If you don't care about uniqueness then you could just display the last 4 characters or whatever, I guess. — Richard Huxton
– Richard Huxton, Commented Apr 21, 2017 at 6:00

Keeper of the Grove · Accepted Answer · 2017-04-20 20:53:34Z

1

I think you would have to decide what constitutes uniquely identifiable by assuming that a number of places from the right make it uniquely identifiable. I think this is folly but the way you would do that is something like this:

SELECT RIGHT(uuid_column_name::text, 7) as your_truncated_uuid FROM table_with_uuid_column;

That takes the 7 places from the right of the text value of the uuid column.

answered Apr 20, 2017 at 20:53

Keeper of the Grove

1509 bronze badges

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

Comments

Evan Carroll · Accepted Answer · 2017-04-20 21:56:10Z

No, there is not. A UUID is a hex representation of a 120 bit random number, at least the v4 variant. It's not even guaranteed to be unique though it likely is.

You have a few options to implement this:

shave off characters and hope you don't introduce a collision. For instance, if you make d8366842-8c1d-4a31-a4c0-f1765b8ab108 d8366842, you have 16**8 possible combinations, or 4,294,967,296. how likely is your dataset to have a collision with 4.2 billion (2**32) possibilities? Perhaps you can add 8c1d back in to make it 16**12 or 28,147,497,6710,656 possibilities.
process and hash each row looking for collisions and recursively increase the frame of characters until no collisions are found, or hash every possible permutation.

That all said, another idea is to use ints and not uuids and then to use http://hashids.org/ which has a plugin for PostgreSQL. This is the method YouTube uses afaik.

Collectives™ on Stack Overflow

Is there a Postgres feature or built-in function that limits the display of uuids to only that needed to make them uniquely identifiable?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related