Postgres SELECT, где WHERE - UUID или строка

У меня есть следующая упрощенная таблица в Postgres:

Я хотел бы запрос, который может найти пользователя либо по его id UUID или его текстовый uid.

Мой запрос генерирует invalid input syntax for uuid поскольку в этом случае я, очевидно, не использую UUID.

Как мне отшлифовать этот запрос или проверить, является ли значение допустимым UUID?

Ответы

Ответ 1

Нашел! Передача столбца UUID в ::text останавливает ошибку. Не уверен в производительности, но примерно на 5000 строк я получаю более чем адекватную производительность.

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

Ответ 2

Я изначально неправильно понял вопрос. Если вы хотите "безопасно" попытаться наложить строку на UUID, вы можете написать функцию, чтобы поймать исключение invalid_text_representation и просто вернуть null (измененный из ответа на другой вопрос):

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 затем приведет к true.

Другими словами (учитывая, что (true or null) = true),

SELECT * FROM user
WHERE id = uuid_or_null('FOOBARBAZ') OR uid = 'FOOBARBAZ';

Оригинальный ответ:

Postgres автоматически преобразует строку в UUID для вас, но вам нужно использовать действительный UUID. Например:

SELECT * FROM user
WHERE id = '5af75c52-cb8e-44fb-93c8-1d46da518ee6' or uid = 'jsdfhiureeirh';

Вы также можете позволить Postgres генерировать UUID для вас с помощью предложения DEFAULT с помощью функции uuid_generate_v4() с помощью расширения uuid-ossp:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE user (  
   id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   uid TEXT,
   name TEXT
);

Ответ 3

Вы можете проверить с помощью регулярного выражения:

SELECT *
FROM user
WHERE ('jsdfhiureeirh' ~ E'^[[:xdigit:]]{8}-([[:xdigit:]]{4}-){3}[[:xdigit:]]{12}$'
       AND id = 'jsdfhiureeirh')
      OR uid = 'jsdfhiureeirh';