从 mysql 迁移后,postgres 数据库无法正常工作

我在 sequelize 中将 MySQL 数据库更改为 postgreSQL.但是迁移后,我在表或模型中遇到大小写首字母问题......在我的 MySQL 版本正常工作之前,但在迁移之后,我收到了错误消息:500 SequelizeDatabaseError: 关系用户"不存在

I change MySQL databese into postgreSQL in sequelize. But After migration I have issue with upper and lowercase first letter in Table or Model... Before my MySQL version was working properly but after migration I got error message: 500 SequelizeDatabaseError: relation "Users" does not exist

我的用户模型:

module.exports = function(sequelize, Sequelize) {
  var User = sequelize.define("User", {
    // profile
    userlevel: Sequelize.STRING,
    restaurant: Sequelize.STRING,
    access: Sequelize.STRING,
    optionsid: Sequelize.STRING,
    email: Sequelize.STRING,
    name: Sequelize.STRING,
    gender: Sequelize.STRING,
    location: Sequelize.STRING,
    website: Sequelize.STRING,
    picture: Sequelize.STRING,
    // Oauth
    password: {
      type: Sequelize.STRING,
      set: function(v) {
        var salt = bcrypt.genSaltSync(5);
        var password = bcrypt.hashSync(v, salt);
        return this.setDataValue('password', password);
      }
    },
    .....

迁移文件:

"use strict";
module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable("users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      userlevel: {
        type: DataTypes.STRING,
        defaultValue: '5'
      },
      restaurant: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      access: {
        type: DataTypes.STRING,
        defaultValue: '1'
      },
      optionsid: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false
      },
      name: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      gender: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      location: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      website: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      picture: {
        type: DataTypes.STRING,
        defaultValue: ''
      },
      password: {
        type: DataTypes.STRING
      },
      facebook: {
        type: DataTypes.STRING
      },
      twitter: {
        type: DataTypes.STRING
      },
      google: {
        type: DataTypes.STRING
      },
      tokens: {
        type: DataTypes.STRING
      },
      resetPasswordToken: {
        type: DataTypes.STRING
      },
      resetPasswordExpires: {
        type: DataTypes.DATE
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    }).done(done);
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable("users").done(done);
  }
};

如果我将 postgreSQL 中表的第一个字母更改为大写,一切正常...

If I change first letter of table in postgreSQL to uppercase everything is working properly...

推荐答案

PostgreSQL 将普通标识符的名称折叠为小写.所以 usersUsersUSERS 都解析为标识符 users.

PostgreSQL folds the names of ordinary identifiers to lower case. So users, Users, and USERS all resolve to the identifier users.

分隔标识符不同.(定界标识符用双引号括起来.)标识符 "users""Users""USERS" 是三个不同的标识符.

Delimited identifiers are different. (Delimited identifiers are surrounded by double quotes.) The identifiers "users", "Users", and "USERS" are three different identifiers.

您的迁移创建了表users".Sequelize 正在查找表 "Users".(分隔标识符——两个不同的表.)

Your migration created the table "users". Sequelize is looking for the table "Users". (Delimited identifiers--two different tables.)

您可能应该将迁移中的标识符更改为用户".还有其他方法,但这是阻力最小的路径.如果这已经在生产中,您最好编写另一个将 "users" 重命名为 "Users" 的迁移.

You should probably change the identifier in your migration to "Users". There are other ways, but this is the path of least resistance. If this is already in production, you might be better off writing another migration that renames "users" to "Users".

相关文章