sql - 在Postgresql Hstore内部查询

我有一个products表，它有很多变体，variants表有一个带有hstore数据类型的price列。
我有两个问题
问题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"时，第二个查询工作正常。

最佳答案

根据我的评论，让我们找出如何找到有问题的数据。我假设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]';

它应该列出未加入的项，以及在字符串中包含非数字的项。把它们清理干净，你的第一个查询就可以了。