其 WHERE 为 UUID 或字符串的 Postgres SELECT

我有以下 Postgres 的简化表格:

  • 用户模型
    • Id (UUID)
    • Uid (varchar)
    • Name (varchar)

我希望查询，可以找到其 UUID id或其文本 uid上的用户。

SELECT * FROM user
WHERE id = 'jsdfhiureeirh' or uid = 'jsdfhiureeirh';

我的查询生成一个 invalid input syntax for uuid，因为我显然没有在这个实例中使用 UUID。

我如何抛光这个查询或检查值是否是一个有效的 UUID？

168019 次浏览
小开

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

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';
小开
最佳答案

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

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


提交答案