Postgres SELECT，其中WHERE是UUID或字符串 [英] Postgres SELECT where the WHERE is UUID or string

问题描述

我在Postgres中有以下简化表：

I have the following simplified table in Postgres:

• 用户模型
  
  
  
  
  • id（UUID）
  
  
  • uid（varchar）
  
  
  • name（varchar）
  
  
  • User Model
    • id (UUID)
    • uid (varchar)
    • name (varchar)

    我想要一个可以在其UUID 上找到用户的查询id 或其文本 uid 。

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

    我的查询为uuid生成无效的输入语法，因为我显然没有在这种情况下使用UUID。

    My query generates an invalid input syntax for uuid since I'm obviously not using a UUID in this instance.

    如何优化此查询或检查值是否为有效的UUID？

    How do I polish this query or check if the value is a valid UUID?

    推荐答案

    找到了！将UUID列强制转换为 :: text 可停止该错误。不确定性能会受到影响，但是在大约5000行上，我获得的性能还不够。

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

    这篇关于Postgres SELECT，其中WHERE是UUID或字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！