PostgreSQL: ERROR: SequelizeDatabaseError operator does not exist character varying = bigint1

educcess node.js, Programming, sequelize errors postgres, sequelize 0 Comments

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: &#91;
      {
        unique: true,
        fields: &#91;"email"],
        name: "user_email_unique_index"
      },
      {
        unique: true,
        fields: &#91;"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>

For other sequelize errors: Read More Here