Search code examples
sqlpostgresqlhstore

Querying inside Postgresql hstore

I have a products table which has many variants, variants table has a price column with hstore datatype.

I have two queries

Query 1

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

Query 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)

While the first query fails with an error message ERROR: invalid input syntax for integer: "not a valid number" the second query works perfectly fine.

Solution

  • Building off of my comment, let's figure out how to find the problematic data. I'm going to assume you have an overwhelming number of rows in the variants table -- enough rows that manually looking for non-numeric values is going to be difficult.

    First, let's isolate the rows which are not covered by the second query.

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

    That will probably take a while to run, and just be a big data dump. We're really interested in just price->'sg', and specifically the ones where price->'sg' isn't a string representation of an integer.

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

    That should list out the items not joined in, and which include non-numbers in the string. Clean those up, and your first query should work.