sql - 在 Postgresql hstore 中查询

Question

我有一个产品表，它有很多变体，变体表有一个价格列，数据类型为 hstore。

我有两个问题

查询 1

SELECT variants.* FROM variants WHERE (CAST(variants.price -> 'sg' AS INT) > 1000)

查询 2

SELECT products.* FROM products INNER JOIN variants ON variants.checkoutable_id = products.id AND variants.checkoutable_type = 'Product' WHERE (CAST(variants.price -> 'sg' AS INT) > 1000)

虽然第一个查询失败并显示错误消息 ERROR: invalid input syntax for integer: "not a valid number" 第二个查询工作得很好。

Answer 1

根据我的评论，让我们弄清楚如何找到有问题的数据。我将假设您在 variants 表中有大量的行 - 手动查找非数字值的行数将很困难。

首先，让我们隔离第二个查询未覆盖的行。

SELECT *
FROM variants
WHERE
  checkoutable_type != 'Product' OR
  checkoutable_id NOT IN (SELECT id FROM products);

这可能需要一段时间才能运行，而且只是一个大数据转储。我们真正感兴趣的只是 price->'sg'，特别是 price->'sg' 不是整数的字符串表示形式的那些。

SELECT price->'sg'
FROM variants
WHERE
  (checkoutable_type != 'Product' OR
   checkoutable_id NOT IN (SELECT id FROM products)) AND
  price->'sg' !~ '[0-9]';

那应该列出未加入的项目，其中包括字符串中的非数字。清理这些，您的第一个查询应该可以工作。