I have a postgres server running with one column (say marks) of type VARCHAR(255), but is supposed to have numbers, like if i do a select *.. query , i will get ['100','50','21','14'...] etc.
i would like to run a range query on it, like user passes [10,30] and gets ['21','14'] as result. I think this would require casting at the time of running the BETWEEN query, but i cannot get it to work properly.
I am using sequalize.js which is generating the following query:
SELECT "id"
FROM "token_attributes" AS "token_attributes"
WHERE "token_attributes"."attributesDirectoryId" = 3
AND CAST('token_attributes.attributeValue' AS INTEGER) BETWEEN 10 AND 30;
on server also this query seems to fail. the sequalize query that is being created is :
{
where: {
attributesDirectoryId: 3,
attributeValue: Where { attribute: [Cast], comparator: '=', logic: [Object] }
},
attributes: [ 'id' ]
}
i have used the following code to create the where condition (cast and where were imported from sequelize):
let whereFilter ={}
let value = where(cast(`${tableName}.attributeValue`, 'integer'), {[Op.between]: rangeAsInt})
whereFilter['attributeValue'] = value
so this is basically calling table.findAll({where:whereFilter}) I am not sure how to either make sequelize create a correct sql api or what the actual correct SQL api would be. can anyone help?
found the issue, i missed the sequilize.col function :
let whereFilter ={}
let value = where(cast(col(`${tableName}.attributeValue`), 'integer'), {[Op.between]: rangeAsInt})
whereFilter['attributeValue'] = value
and the query would be :
SELECT "id"
FROM "token_attributes" AS "token_attributes"
WHERE "token_attributes"."attributesDirectoryId" = 3
AND CAST("token_attributes"."attributeValue" AS INTEGER) BETWEEN 10 AND 30;