Questions tagged [uuid]

A Universally Unique Identifier (UUID), GUID in Microsoft parlance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
33 views

Performance Issues with Concurrent Queries During Mass Update in PostgreSQL

I have a PostgreSQL table with a large amount of data (2 terabytes) where I recently performed a mass update to add UUID values to a column (uuid_column). While the update was successful, I am ...
sujeet's user avatar
  • 235
0 votes
1 answer
667 views

Partitioning strategy and optimal number of partitions for varying data formats (string, numeric, and UUID) in a large table

I'm working on designing a partitioning strategy for a large table in my database, and I'm considering using hash-based partitioning. I came across a recommendation to have the number of partitions ...
sujeet's user avatar
  • 235
0 votes
3 answers
2k views

Change the default value of a string column to real uuid()

When I use show create table Posts I see this column: `Slug` varchar(400) NOT NULL DEFAULT 'uuid()' This means that the default value is a string. I want the default string to be a real uuid() ...
Mohammad Miras's user avatar
1 vote
1 answer
595 views

What cons should I consider if I put index on every UUID column in my database

I'm creating a system that is not entirely microservices, but it's getting close to microservices and each module has its own database. And based on what I have seen in many online services (Azure, ...
Saeed Neamati's user avatar
0 votes
2 answers
376 views

What is the real advantage of using a UUID instead of an auto-incremented element?

I've been looking at resources to understand UUIDs. I now understand what they are, but I still don't really see where they are truly useful. Most of the information I've found revolves around the ...
masonCherry's user avatar
4 votes
1 answer
223 views

How do I add a constraint on the UUID type of Postgres to only allow UUID v4?

I have a lot of tables in a Postgres 14 database that use the native UUID type. Is there a database-wide setting/constraint to limit this type to only allow UUID v4? A potential solution I've come ...
Abhijit's user avatar
  • 181
0 votes
0 answers
400 views

Error about UUID UUID_GENERATE_V

After migrating to a new db server, I find these errors : [ERR] 9> create or replace function "public"."uuid_generate_v1mc"() returns "pg_catalog"."uuid" AS '...
padjee's user avatar
  • 177
2 votes
1 answer
2k views

Will an index on a uuid column be smaller in Postgres than MySQL / InnoDB?

I've got a MySQL database which uses UUID (v4) as identifier for all entities. The UUID's are stored as a CHAR(36) and ascii charset. For one table with about 50.000.000 rows, the index on the UUID ...
sbrattla's user avatar
  • 173
0 votes
2 answers
709 views

MariaDB UUID datatype not orders correctly

I'm using MariaDB 10.8.3. I have a table with a PK of type UUID. I'm inserting ordered UUIDs generated in application side. When I retrive the data of the table ordering by UUID column, it is not ...
blackparrot's user avatar
19 votes
2 answers
9k views

Should I call my UUID primary key column ID or not?

I'm working on a database design that extensively uses UUIDs for PRIMARY KEYs. However, this confronts me with a very consequential choice. How do I name these columns? I would call them uuid, except ...
BigSmoke's user avatar
  • 674
4 votes
2 answers
6k views

How to handle short UUIDs with Postgres?

I see that many web services (Stripe comes to mind) use a special encoding for their UUIDs. Instead of the usual encoding a44521d0-0fb8-4ade-8002-3385545c3318 they are going to be encoded using a ...
laurent's user avatar
  • 187
1 vote
1 answer
2k views

How do I import CSV UUID data into Postgres on the command line?

I'm using PostGres 10. I have this table > \d myapp_currencyprice; Table "public.myapp_currencyprice" Column | Type | Modifiers ----------------...
Dave's user avatar
  • 663
0 votes
1 answer
1k views

What concerns are there respect to use a short-UUID-like type as primary key?

I'm thinking to use (on PostgreSQL v13) an UUID type (with uuid_generate_v4()) for my tables with high traffic, and the others with a Text type (as primary key) by using an auto-generated similar to ...
Ουιλιαμ Αρκευα's user avatar
1 vote
3 answers
451 views

Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?

Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is ...
RackAttack's user avatar
1 vote
1 answer
92 views

Mysql8 fail to upsert generated uuid

I have a mysql8 table with 2 columns: ID(Int) AND UUID(Varchar36): CREATE TABLE `upsert_test` ( `ID` int unsigned DEFAULT NULL COMMENT 'id;', `UUID` varchar(36) DEFAULT NULL ) ENGINE=InnoDB; I ...
Woods Chen's user avatar
0 votes
1 answer
1k views

MySQL 8 - Primary Keys - Sequential UUID or INT with AI?

My DB has ~100 tables and 3 for which I need UUIDs. For these 3 tables, the main goal is simply to hide sensitive IDs to visitors. MySQL 8 has introduced UUID support and I was planning for these 3 ...
Bruno Leveque's user avatar
3 votes
1 answer
2k views

PostgreSQL: what are purpose of functions uuid_hash(uuid) and uuid_hash_extended(uuid, bigint)

Functions uuid_hash and uuid_hash_extended Exploring postgresql I discovered 2 functions that looks interesting: SELECT p.proname , obj_description(p.oid) AS func_desc, ...
Alex Yu's user avatar
  • 213
0 votes
1 answer
2k views

Add jsonb key/value pair with uuid

I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it. What I am trying to add is a key id with a uuid value This is how my data looks right now: { ...
melokki's user avatar
  • 83
0 votes
1 answer
700 views

How to assign GUID to batches of data based on row counts and tunable batch size

I need to assign batches of rows a common GUID id (for consumption by an external process). Below is a simplified setup describing what I'm looking for: BASE_TABLE represents one of pre-existing ...
Gus's user avatar
  • 133
2 votes
1 answer
663 views

About UUID and generators in PostgreSQL

I'm thinking to use UUID as PK for all tables into my new PostgreSQL database, for such a goal I will be using uuid_generate_v5 and uuid_ns_dns or uuid_ns_url functions, but I have some doubts: is ...
Ουιλιαμ Αρκευα's user avatar
1 vote
1 answer
2k views

MariaDB Uuid as Primary Key Performance Problem / Tweaks

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id ...
Tanktiger's user avatar
0 votes
1 answer
462 views

is it relatively safe to union two tables with UUID PK? [closed]

I want to union two tables whose primary keys are UUID(V4) and I want to preserve them in the result. I work with PostgreSQL Is it safe to do this? What are the pitfalls may be encountered?
Dmitry Shvetsov's user avatar
2 votes
1 answer
8k views

How to set uuid-ossp as a default extension for whole server?

I installed uuid-ossp for the default PostgreSQL database (postgres) using the following sql commands but whenever I create a new database I am forced to run the same commands once again for every new ...
mbnoimi's user avatar
  • 243
7 votes
3 answers
9k views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
xehpuk's user avatar
  • 297
5 votes
3 answers
682 views

Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key?

Background As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, ...
oopoopoop's user avatar
2 votes
3 answers
4k views

MySQL UUID() behavior

4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk". I answered, after trying it, UPDATE table SET uuidcol = UUID(); It worked for me, but about half (it ...
Déjà vu's user avatar
  • 473
0 votes
1 answer
394 views

Best practice for exposing record IDs to API users

I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user. Currently, these records are created with an auto generated integer key,...
Half_Duplex's user avatar
1 vote
2 answers
5k views

generate array of unique uuid in postgreSQL

I would like to insert array of unique uuid values in table with uuid column as primary key. Table structure is CREATE TABLE Organisation (uuid id PRIMARY KEY DEFAULT uuid_generate_v4()); e.g. I have ...
Jane's user avatar
  • 111
3 votes
2 answers
5k views

GUID vs Identity on a table performance [duplicate]

My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a ...
user1633146's user avatar
8 votes
1 answer
7k views

UUID or BIGSERIAL Foreign Keys Postgres

I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?
civ15's user avatar
  • 183
1 vote
3 answers
4k views

ID, UUID and "deleted_at" columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
DarkGhostHunter's user avatar
1 vote
0 answers
530 views

SQL Server Partition. How would splitting range with Guid work?

I have a very large table in my database, and it has a GUID column called Group_Id. This Group_Id has a pretty even split of data, and it would be beneficial to partition the table by this column. ...
frobot's user avatar
  • 9
0 votes
1 answer
2k views

Universally converting UUID to IDs

what are the best practices in Postgres for globally converting UUIDs into IDs. I'm currently using both UUIDs and IDs in my schema. The intent is for UUIDs to be exposed to the public (for URLs et.) ...
Jason's user avatar
  • 35
2 votes
1 answer
235 views

UUID as primary key and download ID

I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, ...
TwanK's user avatar
  • 21
0 votes
1 answer
3k views

UUID array declared in stored procedure, on access throws explicit type casts needed

I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception. create or replace function test.test_function(max integer, interval interval) ...
Pavanraotk's user avatar
0 votes
1 answer
362 views

Where clause in select does not work with Inequality of guid

I want to query our CRM Db. I have a backup of CRM Db and the existing one. There is a custom entity,in both of them I have created. Some of my custom entity records has deleted from the existing db. ...
alireza azadi's user avatar
2 votes
1 answer
7k views

Cannot run 'uuid-ossp' functions even when it's activated

I want to generate a v4 uuid in PostgreSQL using the extension uuid-ossp, so in psql I ran: CREATE EXTENSION "uuid-ossp"; Then I ran SELECT uuid_generate_v1(); or SELECT uuid_generate_v4(); but I ...
Fawwaz Yusran's user avatar
0 votes
1 answer
115 views

NON-Random UUID keys in custom distributed DB

Is there any reason other than security for using random (or even semi-random) UUIDs as primary keys to database records? Is there any reason why I should not WANT to be able to use the UUID to ...
squarewav's user avatar
  • 101
4 votes
1 answer
3k views

best index type for uuid in postgres 11

I realize a similar question has been asked multiple times but all the ones I can find are from 2014/2015 and many versions of postgres ago. I'm on postgres 11. I'm introducing a new primary key for ...
drobert's user avatar
  • 143
0 votes
0 answers
4k views

How to pass array of UUIDs *OR* empty array and return implicit (inner join)?

How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty? This question (Casting an array of texts to an array of ...
user3.1415927's user avatar
2 votes
1 answer
8k views

Postgres support for UUID [closed]

My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not ...
wheresmycookie's user avatar
4 votes
2 answers
3k views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
Callum Breen's user avatar
16 votes
4 answers
51k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
Sig's user avatar
  • 383
3 votes
1 answer
7k views

How to fix issue uuid_generate_v1() does not exist in postgresql , actually i have the function under public schema

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from da.test_get(cast(? as integer),cast(? as text),cast(? as text),cast(? as text),cast(? as text)...
Jeremy 's user avatar
2 votes
1 answer
5k views

Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns ...
J.D.'s user avatar
  • 37.1k
0 votes
1 answer
818 views

Primary key and index column for Merge replication- Id vs GUID

I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the ...
abbhey's user avatar
  • 105
13 votes
1 answer
31k views

In Postgres, how to select by a column of type UUID [closed]

I have a DB table that someone else created, its PK is a single column of type 'uuid'. I'm struggling to perform a simple select by that column. Examples I have tried with no success: select from ...
Clint Eastwood's user avatar
1 vote
1 answer
2k views

In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all?

A popular answer on SO states that using UUIDs as primary keys has the following benefit: Makes replication trivial (as opposed to int's, which makes it REALLY hard) I have been unable to find ...
ARX's user avatar
  • 1,419
1 vote
2 answers
3k views

Convert SID string to varbinary(85)

My ssrs server was moved to a different domain because of which the Users table need to be updated with new SID current sid: S-1-5-21-39949945-591092532-1226208319-1007 I want to convert this sid ...
Mohammad Yusuf's user avatar
30 votes
1 answer
25k views

PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?

What is the difference between the following two functions gen_random_uuid() provided by pgcrypto extension uuid_generate_v4() provided by uuid-ossp extension Are they both the same behind the ...
user avatar