Tengo una tabla de productos que tiene muchas variantes, la tabla de variantes tiene una columna de precios con el tipo de datos hstore.

Tengo dos consultas

Consulta 1

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

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

Mientras que la primera consulta falla con un mensaje de error ERROR: invalid input syntax for integer: "not a valid number", la segunda consulta funciona perfectamente bien.

0
Saad 6 mar. 2017 a las 20:01

2 respuestas

La mejor respuesta

Partiendo de mi comentario, descubramos cómo encontrar los datos problemáticos. Asumiré que tiene una cantidad abrumadora de filas en la tabla variants: suficientes filas que buscar manualmente valores no numéricos será difícil.

Primero, aislemos las filas que están no cubiertas por la segunda consulta.

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

Probablemente llevará un tiempo ejecutarlo, y solo será un gran volcado de datos. Estamos realmente interesados en solo price->'sg', y específicamente aquellos en los que price->'sg' no es una representación de cadena de un número entero.

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

Eso debería enumerar los elementos no unidos, y que incluyen no números en la cadena. Límpielos y su primera consulta debería funcionar.

2
jmelesky 6 mar. 2017 a las 17:35

Una o más filas de variants tienen contenido incorrecto para entero, es decir, "not a valid number". Ejecute la consulta para verificar cuáles:

select *
from variants
where price->'sg' like 'not%';
1
klin 6 mar. 2017 a las 17:33