在Postgresql的Knex js中修改表修改枚举会出错 [英] Alter table modify enum in Knex js for Postgresql gives error

问题描述

我正在使用knex js和postgresql数据库。我使用了一个迁移文件来创建表 knex migrate：make create_car_table 。在此我添加了一列fuel_type。 table.enu（'fuel_type'，['PETROL'，'DIESEL'，'CNG']）。

I am using knex js and postgresql database. I have used a migration file to create a table knex migrate:make create_car_table. In this I have added a column fuel_type. table.enu('fuel_type', ['PETROL', 'DIESEL', 'CNG']).

现在我需要更改表，并且需要这些枚举值 ['HYBRID'，'ELECTRIC'，'PETROL'，'DIESEL'] 。

Now I need to alter the table and I need these enum values ['HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'].

我已经使用 knex migration：make alter_car_table 创建了另一个迁移文件，并添加了以下代码

I have created another migration file using knex migrate:make alter_car_table and added the below code

exports.up = function(knex, Promise) {
    return knex.schema.alterTable('car', function (table) {
        table.enu('fuel_type', ['HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL']).alter();
    });
};

exports.down = function(knex, Promise) {
    return knex.schema.alterTable('car', function (table) {
        table.enu('fuel_type', ['PETROL', 'DIESEL', 'CNG']).alter();
    });
};

当我运行 knex migrate：latest 时出现以下错误。

when I run knex migrate:latest I get the below error.

Knex:warning - migrations failed with error: alter table "car" alter column "fuel_type" type text check ("fuel_type" in ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL')) using ("fuel_type"::text check ("fuel_type" in ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'))) - syntax error at or near "check"

或附近的语法错误href = http://knexjs.org/#Schema-alter rel = noreferrer> Knex Js 。

I have refered Knex Js for this.

推荐答案

更改列不适用于 knex 0.13.0 中的枚举类型。

Alter column does not work for enum types in knex 0.13.0.

还将枚举作为检查约束实现，因此要更改它，您需要重新创建它。

Also enums are implemented as check constraints, so to change it you need to recreate the.

类似

exports.up = function(knex, Promise) {
  return knex.schema.raw(`
    ALTER TABLE "car"
    DROP CONSTRAINT "car_fuel_type_check",
    ADD CONSTRAINT "car_fuel_type_check" 
    CHECK (fuel_type IN ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'))
  `);
};

exports.down = function(knex, Promise) { ... };

您可能需要检查最初由knex从数据库生成的约束名称。

You might need to check your constraint name that was originally generated by knex from the DB.

当前 knex.schema.raw 是修改枚举的唯一方法。

Currently knex.schema.raw is the only way to modify enums.

这篇关于在Postgresql的Knex js中修改表修改枚举会出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！