I have the following simplified table in Postgres:
I would like a query that can find the user on either its UUID id or its text uid.
SELECT * FROM user
WHERE id = 'jsdfhiureeirh' or uid = 'jsdfhiureeirh';
My query generates an invalid input syntax for uuid since I'm obviously not using a UUID in this instance.
How do I polish this query or check if the value is a valid UUID?
Found it! Casting the UUID column to ::text stops the error. Not sure about the performance hit but on about 5000 rows I get more than adequate performance.
SELECT * FROM user
WHERE id::text = 'jsdfhiureeirh' OR uid = 'jsdfhiureeirh';
SELECT * FROM user
WHERE id::text = '33bb9554-c616-42e6-a9c6-88d3bba4221c'
OR uid = '33bb9554-c616-42e6-a9c6-88d3bba4221c';
unhex or another functionsIF your_uuid_var ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' THEN <one_query> ELSE <other_query> END IF;. In my case, time for planning increased by a factor of 5 and time for execution reduced by a factor of 309.I had originally misunderstood the question. If you want to "safely" try to cast a string to a UUID, you can write a function to catch the invalid_text_representation exception and just return null (modified from an answer to a different question):
CREATE OR REPLACE FUNCTION uuid_or_null(str text)
RETURNS uuid AS $$
BEGIN
RETURN str::uuid;
EXCEPTION WHEN invalid_text_representation THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
SELECT uuid_or_null('INVALID') IS NULL will then result in true.
In other words (given that (true or null) = true),
SELECT * FROM user
WHERE id = uuid_or_null('FOOBARBAZ') OR uid = 'FOOBARBAZ';
Original answer:
Postgres will automatically convert the string to a UUID for you, but you need to use a valid UUID. For example:
SELECT * FROM user
WHERE id = '5af75c52-cb8e-44fb-93c8-1d46da518ee6' or uid = 'jsdfhiureeirh';
You can also let Postgres generate UUIDs for you using a DEFAULT clause with the uuid_generate_v4() function by using the uuid-ossp extension:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE user (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
uid TEXT,
name TEXT
);
value coming in (merging legacy system with modern one) hence the shape of my query (not sure where the ID could be coming from). I can obviously do this on the backend but was hoping for a SQL solution
SELECT * FROM user WHERE id = uuid_or_null('FOOBARBAZ') OR uid = 'FOOBARBAZ'?.. id::uuid
text. Perhaps someone might find this pattern useful for other types that you can't do the same, but yes, I'd recommend @Wainage answer for performance in this case.
You could check with a regular expression:
SELECT *
FROM user
WHERE ('jsdfhiureeirh' ~ E'^[[:xdigit:]]{8}-([[:xdigit:]]{4}-){3}[[:xdigit:]]{12}$'
AND id = 'jsdfhiureeirh')
OR uid = 'jsdfhiureeirh';
In stead of using ::text, use cast(uid as text), and build an index on that expression then Postgres recignozied it for very fast querying. We do that for sharding/partitioning building an index like this
Create index idx_partition256 on using btree ((right(cast(id as text), 2)));
You can use ::text, but then it is not possible to use I.e JPA since it will be confused with parameter replacement.
Query in JPA could be
“… where function(‘right’ cast(id as text), 2) in (…) …”
right is above, or what the 2 argument is.