PostgreSQL: ERROR: SequelizeDatabaseError operator does not exist character varying = bigint1
Getting this Error for find query using sequelize library in nodejs:
Error: SequelizeDatabaseError: operator does not exist: character varying = bigint1
My Sequelize Database Model looks as follows
const sequelizeHelper = require("../helpers/sequelizeHelper");
const db = sequelizeHelper.db;
const sqlOperator = sequelizeHelper.sqlOperator;
const UserRolesModel = require("./UserRolesModel");
const UserModel = db.define(
"users",
{
userId: {
field: "userId",
type: sqlOperator.UUID,
primaryKey: true,
defaultValue: sqlOperator.UUIDV4
},
firstName: {
field: "firstName",
type: sqlOperator.STRING(100),
require: true
},
lastName: {
field: "lastName",
type: sqlOperator.STRING(100),
},
email: {
field: "email",
type: sqlOperator.STRING(1025),
validate: {
isEmail: true
}
},
userName: {
field: "userName",
type: sqlOperator.STRING(1025),
},
phone: {
field: "phone",
type: sqlOperator.STRING(13),
},
photo: {
field: "photo",
type: sqlOperator.STRING(1024),
defaultValue: null,
validate: {
isUrl: true
}
},
studentDetails: {
field: "studentDetails",
type: sqlOperator.JSON,
defaultValue: null,
},
profession: {
field: "profession",
type: sqlOperator.STRING(13),
defaultValue: ""
},
},
{
tableName: "users",
timestamps: true,
indexes: [
{
unique: true,
fields: ["email"],
name: "user_email_unique_index"
},
{
unique: true,
fields: ["userName"],
name: "user_user_name_unique_index"
},
]
}
);
UserModel.associate = () => {
UserModel.hasMany(UserRolesModel, {
as: "roles"
});
UserModel.sync({ alter: true });
}
module.exports = UserModel;
Solution:
This kind of issue usually occur when we try to compare mismatched data types. You cannot compare an integer with a varchar. PostgreSQL is strict and does not do any magic typecasting for you. I’m guessing SQLServer does typecasting automagically (which is a bad thing).
const phone = 9998886660;
UserModel.count({
where: {
phone: phone
}
});The above code throws operator does not exist: integer = character varying exception, because we are trying to compare integer with string. To fix this issue change as follows
const phone = "9998886660"; //this will fix the issue
UserModel.count({
where: {
phone: phone
}
});