CODEWITHSUNDEEP
postgresql
Wainage
Wainage

Reputation: 5392

Postgres SELECT where the WHERE is UUID or string

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?

Upvotes: 128

Views: 279481

Answers (4)

neko
neko

Reputation: 61

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 (…) …”

Upvotes: 5

Wainage
Wainage

Reputation: 5392

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';

Upvotes: 226

Josh Bowden
Josh Bowden

Reputation: 5980

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
);

Upvotes: 31

Laurenz Albe
Laurenz Albe

Reputation: 246238

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';

Upvotes: 19

Related Questions