postgresql Postgres SELECT 其中 WHERE 是 UUID 或字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46433459/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres SELECT where the WHERE is UUID or string
提问by Wainage
I have the following simplified table in Postgres:
我在 Postgres 中有以下简化表：
- User Model
- id (UUID)
- uid (varchar)
- name (varchar)
- 用户模型
- id (UUID)
- uid (varchar)
- 名称 (varchar)
I would like a query that can find the user on either its UUID idor its text uid.
我想要一个可以在其 UUIDid或 text上找到用户的查询uid。
SELECT * FROM user
WHERE id = 'jsdfhiureeirh' or uid = 'jsdfhiureeirh';
My query generates an invalid input syntax for uuidsince I'm obviously not using a UUID in this instance.
我的查询生成了一个，invalid input syntax for uuid因为我在这个实例中显然没有使用 UUID。
How do I polish this query or check if the value is a valid UUID?
如何完善此查询或检查该值是否为有效的 UUID？
回答by Wainage
Found it! Casting the UUID column to ::textstops the error. Not sure about the performance hit but on about 5000 rows I get more than adequate performance.
找到了！强制转换 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';
回答by Josh Bowden
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_representationexception and just return null(modified from an answer to a different question):
我最初误解了这个问题。如果您想“安全地”尝试将字符串转换为 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 NULLwill then result in true.
SELECT uuid_or_null('INVALID') IS NULL然后将导致true.
In other words (given that (true or null) = true),
换句话说（鉴于(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:
Postgres 会自动为您将字符串转换为 UUID，但您需要使用有效的 UUID。例如：
SELECT * FROM user
WHERE id = '5af75c52-cb8e-44fb-93c8-1d46da518ee6' or uid = 'jsdfhiureeirh';
You can also let Postgres generate UUIDs for you using a DEFAULTclause with the uuid_generate_v4()function by using the uuid-osspextension:
您还可以使用扩展名让 Postgres 使用DEFAULT带有uuid_generate_v4()函数的子句为您生成 UUID uuid-ossp：
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE user (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
uid TEXT,
name TEXT
);
回答by Laurenz Albe
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';
