My friend has the problem as shown below. He asked at StackOverFlow and there is no reply yet till now so I wish to help him to try the luck here. Thanks for everyone that helps. "I would like to ask if its possible to set up a database table that has a column that auto generates random alphanumerical data that is longer that what UUID can do. I've been using UUID for a while now but I would like to have a longer string of random data in my columns, something similar to that of a token authenticator (around 300+ characters). So when I insert values in columns, this particular column will auto generate data by itself. Thanks in advance." Quoted from Reuben Tan Source: https://stackoverflow.com/questions/43461771/mysql-column-that-auto-generates-data-longer-than-uuid

According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp: https://www.postgresql.org/docs/9.4/static/uuid-ossp.html On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database. Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine? One scenario is the summer time / daylight savings.

I'm using PostGres 10. I have this table > \d myapp_currencyprice; Table "public.myapp_currencyprice" Column | Type | Modifiers --------------------+--------------------------+----------- id | uuid | not null price | double precision | not null created | timestamp with time zone | not null currency_id | uuid | not null I have a CSV of data, which looks like id,price,created,currency_id fa9944a6-f622-499c-89b8-42534a541307,59451.41,2021-11-20 15:56:00-05,3965e495-d5a5-41ec-83fc-359545ca2716 13013751-d84a-441f-a19a-3c7b50c557d0,59474.17,2021-11-20 15:58:00-05,3965e495-d5a5-41ec-83fc-359545ca2716 but when I try and import the data on the command line, I get this error > PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c "\copy myapp_currencyprice FROM '/tmp/prices.csv' delimiter ',' csv" ERROR: invalid input syntax for uuid: "id" CONTEXT: COPY myapp_currencyprice, line 1, column id: "id" what's the right way to import data from a CSV into a PostGres table?

I have been trying now many times, and I tried to find a way to make sense of and find out my problem. But the problem is still performance issues. I tested it on a developer server ( 2 cores, 4gb ram ) but I still got issues and problems, here is the stored procedure I have typed.

DROP PROCEDURE IF EXISTS insertDistributorProduct;
    
    DELIMITER $$
    
    CREATE PROCEDURE insertDistributorProduct(
    	distributorUUID VARCHAR(36),
    	distributorTitle VARCHAR(256),
    	distributorDescription TEXT,
    	distributorSKU VARCHAR(128),
    	manufacturer VARCHAR(64),
    	manufacturerSKU VARCHAR(128),
    	productEan BIGINT(20),
    	productCostPrice INT(11),
    	productStock INT(11),
    	productStockExpected DATE
    )
    BEGIN
    	DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL;
    	DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL;
    	
    	SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m
                WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 );
    
    	-- If manufacturer not exists insert it
    	IF ( _manufacturerUUID IS NULL ) THEN
    		
    		SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma
                    WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 );
    		
    		-- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert
    		IF ( _manufacturerUUID IS NULL ) THEN
    		
    			SET _manufacturerUUID = UUID();
    			
    			INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added)
    			VALUES(_manufacturerUUID,manufacturer,NOW());
    			
    		END IF;
    			
    	END IF;
    		
    	SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp
                WHERE dp.distributorUUID = distributorUUID
                  AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) );
    	
    	-- Update distributorProduct if its all ready found
    	IF ( _distributorProductUUID IS NOT NULL ) THEN
    
    		UPDATE 
    			distributorProduct dp
    		
    		SET 
    			dp.distributorTitle = distributorTitle,
    			dp.distributorDescription = distributorDescription,
    			dp.manufacturerUUID = _manufacturerUUID,
    			dp.manufacturerSKU = manufacturerSKU,
    			dp.productEan = productEan,
    			dp.productCostPrice = productCostPrice,
    			dp.productStock = productStock,
    			dp.productStockExpected = productStockExpected,
    			dp.updated = NOW(),
    			dp.activated = 1
    			
    		WHERE
    			dp.distributorProductUUID = _distributorProductUUID
    		
    		LIMIT
    			1;
    	
    	-- Insert new product to distributorProduct if not exists.
    	ELSE
    	
    		INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added)
    		VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW());
    	
    	END IF;
    	
    END
    $$

My problem is that every time I have over 50-100k rows, this script takes longer and longer over time, and I have to try to look up my index but still nothing happens. How can I debug and improve the performance of my database? Every day I need to run this producer like 1-1,4milion times between 4-5 hours max, it updates all our products but right now it's useless. The first time 60k rows take 15min, and after that 60k more ( total 120k ) it takes approx 30-45min to run, and I still don't know why it happened. What did I do wrong? Everything I tested here is running in a virtual box software, I don't have to test this on real hardware.

I'm facing an issue while restoring a PostgreSQL 15 database dump. During the restore process, I encounter the following errors:

ERROR:  trailing junk after numeric literal at or near "..."
ERROR:  invalid command \N
ERROR:  invalid command \N
.
.
.
ERROR:  out of memory

The error occurs after processing the UUIDs in the dump file. It seems that the UUIDs are being interpreted incorrectly, possibly as numeric literals, which causes subsequent issues with invalid commands like \N, leading to an out of memory error. Here is the command I'm using to create the dump:

pg_dump -h  -p 5432 -U   > new.dump

And the restore command:

psql postgresql://:@/ < new.dump

Could anyone advise on the best way to create a dump file that correctly handles UUIDs during the restore process to avoid these issues? Specifically, I want to ensure UUIDs are treated as UUIDs and not interpreted as numeric literals, which leads to the invalid commands and out of memory error. Any help or best practices would be greatly appreciated! Thanks! **Additional info:** I am restoring to a PostgreSQL 15 instance. OS and version: Debian 12 pg_dump -V returns : pg_dump (Postgresql) 16.2

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))]; nested exception is org.postgresql.util.PSQLException: ERROR: spiexceptions.UndefinedFunction: function uuid_generate_v1() does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Where: Traceback (most recent call last):

Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key. Use Case --------- - Many systems will insert to the table, some may specify a UUID when inserting, but some will not. They will generate their own UUID, then insert it later (offline insert). - I already have systems that use MySQL, and I would prefer to stay with the same DBMS. - I prefer UUID v4 because of its physical machine independence. Alternatives ------------- I could simply create a trigger that calls the uuid function, as outlined in https://dba.stackexchange.com/questions/57293/mysql-alter-table-to-automatically-put-in-a-uuid , but the uuid function generates UUID v1 ids. I could always generate the UUID externally, but I would prefer a built-in default. This is easy to do in Postgres, and some other DBMSes, but is there a way to do it in MySQL?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses automatically. Whatever I use is going to be handling huge amounts of data. The SP-GiST operator family "text_ops" indexes using a trie. Because UUIDs are quite long and very dissimilar, these sound appealing even though I would only ever do full match searches. There's also a hash option. Hashing is O(1), and I won't need to do any comparisons besides equality of course, but because UUIDs are quite long, I'm afraid that generating hashes from them would waste a lot of time. Or is this something that depends too much on system and use specifics? I'd rather use *bigserial* in most cases, but I've been told to use *uuid* for this. We need *uuid* because we might have multiple servers using different databases, so there isn't a guarantee that we'll have unique bigints. We could use a different sequence (and seed) for each server, but it's still not as flexible as UUIDs. For example, we wouldn't be able to migrate database entries from one server to another without converting the IDs and their references everywhere.

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 picturing something simple like "generate a key and check if it exists, and generate another one if it does exist." But if you have a simple non-sequential key, you could end up in this collision loop for quite some time.

## 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, to the use of a non-clustered primary key in combination of a clustered index with a sort-able type, my research pointed me to UUIDv6 , and an implementation of it. It is able to generate UUIDs like the below (that is sequential):

UUIDv1                               UUIDv6 
------------------------------------ ------------------------------------ 
5714f720-1268-11e7-a24b-96d95aa38c32 1e712685-714f-6720-a23a-c90103f70be6 
68f820c0-1268-11e7-a24b-96d95aa38c32 1e712686-8f82-60c0-ac07-7d6641ed230d 
7ada38f0-1268-11e7-a24b-96d95aa38c32 1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 
8cc06fd0-1268-11e7-a24b-96d95aa38c32 1e712688-cc06-6fd0-a828-671acd892c6a 
9ea6a6b0-1268-11e7-a24b-96d95aa38c32 1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which I thought SQL Server would gladly sort them for me in the clustered primary key (uniqueidentifier) column. Little did I know how SQL Server would sort an uniqueidentifier column. Here's the ascending sort result:

UUIDv6 uniqueidentifier sorted 
------------------------------------ 
1e712688-cc06-6fd0-a828-671acd892c6a 
1e712686-8f82-60c0-ac07-7d6641ed230d 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 
1e712685-714f-6720-a23a-c90103f70be6 
1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which is causing fragmentation as if using random UUIDs. This post explains how they were actually sorted. ## The real question ## Luckily, the system is still in development. Which of these options should I go for next? 1. reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be

UUIDv6                               UUIDv6 reordered bytes 
------------------------------------ ------------------------------------ 
1e712685-714f-6720-a23a-c90103f70be6 c90103f7-0be6-a23a-6720-1e712685714f 
1e712686-8f82-60c0-ac07-7d6641ed230d 7d6641ed-230d-ac07-60c0-1e7126868f82 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c c1ebf8e6-fc8c-93f8-68f0-1e712687ada3 
1e712688-cc06-6fd0-a828-671acd892c6a 671acd89-2c6a-a828-6fd0-1e712688cc06 
1e712689-ea6a-66b0-910c-dbcdb07df7a4 dbcdb07d-f7a4-910c-66b0-1e712689ea6a

2. convert the UUIDv6 to binary(16) and use that instead

UUIDv6                               UUIDv6 binary(16) 
------------------------------------ -------------------------------- 
1e712685-714f-6720-a23a-c90103f70be6 1e712685714f6720a23ac90103f70be6 
1e712686-8f82-60c0-ac07-7d6641ed230d 1e7126868f8260c0ac077d6641ed230d 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 1e712687ada368f093f8c1ebf8e6fc8c 
1e712688-cc06-6fd0-a828-671acd892c6a 1e712688cc066fd0a828671acd892c6a 
1e712689-ea6a-66b0-910c-dbcdb07df7a4 1e712689ea6a66b0910cdbcdb07df7a4

### Problem with option 1 ### The UUID standard embeds a 4-bit version field inside the ID. UUIDv6 (still non-standard) also follows that rule. The way I will reorder them is going to break this. ### Problem with option 2 ### I'm not sure. Can hardly find anyone talking about it except this , which is going against the idea. Are there other pitfalls that I should be aware of in using the binary(16) type? Thanks!

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 idea of a distributed system: you get almost truly unique id's automatically generated and so on all the distributed systems you shouldn't expect to see an id twice. But why do you want that? what is the issue if two different databases have the same id? Is this only useful if you want to collect all the data from all databases and have them be unique?

I've recently inherited a SQL Server database that uses BINARY(16) instead of UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys. Should I be concerned?

In Postgres 9.x, for a column of type UUID , how do I specify a UUID to be generated automatically as a default value for any row insert?

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() function. I want it to become:

Slug varchar(400) NOT NULL DEFAULT uuid()

How can I do that? What query should I write for this? No matter what I do inside the phpMyAdmin or Adminer it does not become what I want. --- I have a column that has 'uuid()' as its default value. I want to change to so that it becomes uuid(). I don't want to create a new column. I don't want to drop the column and recreate it. I want to modify it.

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 any evidence in PG's documentation (or anywhere else) to substantiate this claim. The PG docs mention nothing about this having any bearing on replication. For me, if a replica is an exact copy of the master, the type of key used is irrelevant. So, is there something that escapes me? How would integer primary keys, compared to UUIDs, make harder to replicate your postgres db? A related question is whether using UUIDs as primary keys slow down join operations (some people claim they do). Are there any benchmarks on this? Can anybody share experiences?

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIDs or the like. Is there an alternative to, CREATE TABLE f ( id uuid DEFAULT gen_random_uuid() ); Especially one that can protect the column under GENERATED ALWAYS

I have a system which uses the proposed UUID7 format for its uuid values. UUID7 has timestamp encoded into into its value. Does PostgreSQL document how ORDER BY clauses work with the UUID data type? https://www.postgresql.org/docs/current/datatype-uuid.html There are no notes on sorting here.

lab7=# SELECT resource_id, uuid, created_timestamp from resource where "name" in ('ABC000004',
 'ABC000003') order by uuid, "name" desc;
 resource_id |                 uuid                 |     created_timestamp
      
-------------+--------------------------------------+----------------------
------
    17704701 | 018ec350-6976-79d5-b0f5-41af99b98fa4 | 2024-04-09 14:43:30.6
09909
    17704718 | 018ec353-ee50-7765-9e8b-3ebf22654662 | 2024-04-09 14:47:21.3
78086

Comparing the leftmost characters (assuming this was a string), I would expect 018ec353 to be in the result set before 018ec350. As this is not stored as a string, that assumption may be naive. Performance wise, sorting by resource_id here or create_timestamp is more performant, but I would have expected the sorting on the uniquely incrementing resource_id, the globally unique uuid, and the created_timestamp to all be alignment. (Newest created entry sorts first when order by desc) Are there any details on how this sorting is performed and why my assumptions are wrong?

I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values. The application code logs UUIDs, but...alas...doesn't always log the object type or table. While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to. Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?

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 as text. The projects table had a UUID field named id. My initial idea was a query looks like: SELECT * FROM projects JOIN users ON projects.id = ANY(users.project_ids) But that does not work since users.project_ids are not UUIDs so I tried: projects.id = ANY(users.project_ids::uuid[]) and even: projects.id = ANY(ARRAY[users.project_ids]::uuid[]) but neither one works: > ERROR: invalid input syntax for type uuid: "" UPDATE @a_horse_with_no_name is definitely right. The best option should be using an array of UUIDs. The question now is how can I alter an array of text into an array of uuid? The users table is currently empty (0 records). I have tried ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[]; which generates ERROR: result of USING clause for column "product_ids" cannot be cast automatically to type uuid HINT: You might need to add an explicit cast. > ALTER TABLE "users" ALTER COLUMN "product_ids" SET DATA TYPE UUID > USING "product_ids"::UUID; I have also tried ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[]; which generates > ERROR: default for column "project_ids" cannot be cast automatically > to type uuid[] The column is set to an empty array as default. I'm running PG version 10.4 and project_ids is currently text[] nullable.

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()); > ERROR: function max(uuid) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts. I know that I can cast to a character type or ORDER BY and LIMIT 1. I'm just curious as to why I have to use a workaround.