postgresql - Sequelize 错误 42804。需要重写或转换表达式

Question

我有一个项目，我正在使用 sequelize 来构建和同步数据库。我设置了一个名为 Space 的数据库表。当它尝试将数据库与我的其他表同步时。我收到错误代码 42804 。

这是表:

const seq = require('sequelize');
const { postgres } = require('../index');
const { Images } = require('./Images');
const { Reservation } = require('./Reservation');
const { SpaceAmenities } = require('./SpaceAmenities');


const Space = postgres.define(
    'space',
    {
        id: {type: seq.INTEGER, primaryKey: true, autoincrement: true},
        size: {type: seq.INTEGER, require: true, 
               validate: {isNumeric: true, allowNull: false}
        },
        amount: {type: seq.FLOAT, require: true, 
                 validate: {isFloat: true, allowNull: false}
        },
        floor: {type: seq.INTEGER, require: true, 
                validate: {isNumeric: true, allowNull: false}
        },
        available: {type: seq.BOOLEAN, require: true, defaultValue: 0,
                    validate: {isIn: [['0', '1']], isInt: true, allowNull: false}
        }
    },
    {
        createdAt: seq.DATE,
        updatedAt: seq.DATE
    }
)


Space.hasMany(Images, {as: 'imagesId'})
Space.hasMany(Reservation, {as: 'reservationId'})
Space.hasMany(SpaceAmenities, {as: 'spaceAmenitiesId'})


postgres.sync()
    .then(() => {
        console.log("Space table is connected and synced")
    })
    .catch((err) => {
        console.log("Error syncing the Space table: " + JSON.stringify(err))
    })


module.exports.Space = Space;

错误:

Error syncing the Space table: {"name":"SequelizeDatabaseError","parent":{"name":"error","length":184,"severity":"ERROR","code":"42804","hint":"You will need to rewrite or cast the expression.","file":"heap.c","line":"2946","routine":"cookDefault","sql":"CREATE TABLE IF NOT EXISTS \"spaces\" (\"id\" INTEGER , \"size\" INTEGER, \"amount\" FLOAT, \"floor\" INTEGER, \"available\" BOOLEAN DEFAULT 0, \"DATE\" TIMESTAMP WITH TIME ZONE NOT NULL, \"officeId\" INTEGER REFERENCES \"offices\" (\"id\") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (\"id\"));"},"original":{"name":"error","length":184,"severity":"ERROR","code":"42804","hint":"You will need to rewrite or cast the expression.","file":"heap.c","line":"2946","routine":"cookDefault","sql":"CREATE TABLE IF NOT EXISTS \"spaces\" (\"id\" INTEGER , \"size\" INTEGER, \"amount\" FLOAT, \"floor\" INTEGER, \"available\" BOOLEAN DEFAULT 0, \"DATE\" TIMESTAMP WITH TIME ZONE NOT NULL, \"officeId\" INTEGER REFERENCES \"offices\" (\"id\") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (\"id\"));"},"sql":"CREATE TABLE IF NOT EXISTS \"spaces\" (\"id\" INTEGER , \"size\" INTEGER, \"amount\" FLOAT, \"floor\" INTEGER, \"available\" BOOLEAN DEFAULT 0, \"DATE\" TIMESTAMP WITH TIME ZONE NOT NULL, \"officeId\" INTEGER REFERENCES \"offices\" (\"id\") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (\"id\"));"}

不知道如何通过同步解决这个错误。

还有与此表相关联的其他表，包括

图片:

const seq = require('sequelize');
const { postgres } = require('../index');


const Images = postgres.define(
    'images',
    {
        id: {type: seq.INTEGER, primaryKey: true, autoincrement: true},
        image: {type: seq.FLOAT(9, 2), require: true, 
                 validate: {isFloat: true, allowNull: false}
        },
    },
    {
        createdAt: seq.DATE,
        updatedAt: seq.DATE
    }
)


postgres.sync()
    .then(() => {
        console.log("Images table is connected and synced")
    })
    .catch((err) => {
        console.log("Error syncing the Images table: " + JSON.stringify(err))
    })


module.exports.Images = Images;

预订:

const seq = require('sequelize');
const { postgres } = require('../index');


const Reservation = postgres.define(
    'reservation',
    {
        id: {type: seq.INTEGER, primaryKey: true, autoincrement: true},
        start: {type: seq.DATE, required: true, 
                validate: {isDate: true, allowNull: false}
        },
        end: {type: seq.DATE, required: true, 
              validate: {isDate: true, allowNull: false}
        },
        amount: {type: seq.FLOAT(9, 2), require: true, 
                 validate: {isFloat: true, allowNull: false}
        },
    },
    {
        createdAt: seq.DATE,
        updatedAt: seq.DATE
    }
)


postgres.sync()
    .then(() => {
        console.log("Reservation table is connected and synced")
    })
    .catch((err) => {
        console.log("Error syncing the Reservation table: " + JSON.stringify(err))
    })


module.exports.Reservation = Reservation;

空间设施:

const seq = require('sequelize');
const { postgres } = require('../index');


const SpaceAmenities = postgres.define(
    'space_amenities',
    {
        id: {type: seq.INTEGER, primaryKey: true, autoincrement: true},
    },
    {
        createdAt: seq.DATE,
        updatedAt: seq.DATE
    }
)


postgres.sync()
    .then(() => {
        console.log("Space_Amenities table is connected and synced")
    })
    .catch((err) => {
        console.log("Error syncing the Space_Amenities table: " + JSON.stringify(err))
    })


module.exports.SpaceAmenities = SpaceAmenities;

Answer 1

你的问题是

"available" BOOLEAN DEFAULT 0

PostgreSQL 提示默认值，它不能隐式转换为 boolean:

\dCS boolean
                       List of casts
 Source type |    Target type    | Function |   Implicit?   
-------------+-------------------+----------+---------------
 boolean     | character         | text     | in assignment
 boolean     | character varying | text     | in assignment
 boolean     | integer           | int4     | no
 boolean     | text              | text     | in assignment
 integer     | boolean           | bool     | no
 jsonb       | boolean           | bool     | no
(6 rows)

你应该写

"available" BOOLEAN DEFAULT FALSE