CODEWITHSUNDEEP
sqlpostgresqlhstore
Saad
Saad

Reputation: 1904

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.

Upvotes: 1

Views: 2721

Answers (2)

jmelesky
jmelesky

Reputation: 3970

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.

Upvotes: 3

klin
klin

Reputation: 121594

One or more rows of variants have improper content for integer, namely "not a valid number". Run the query to check which ones:

select *
from variants
where price->'sg' like 'not%';

Upvotes: 2

Related Questions