Postgres SELECT where the WHERE is UUID or string

Question

I have the following simplified table in Postgres:

• User Model
  • id (UUID)
  • uid (varchar)
  • name (varchar)

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?

Answer 1

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