PostgreSQL: ERROR: SequelizeDatabaseError operator does not exist character varying = bigint1
http://knowledge-cess.com/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
<code>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; </code>
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).
<code>const phone = 9998886660; UserModel.count({ where: { phone: phone } });</code>
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
<code>const phone = "9998886660"; //this will fix the issue UserModel.count({ where: { phone: phone } });</code>