postgresql knex迁移为枚举创建类型thows类型已存在

aor9mmx1  于 2023-06-29  发布在  PostgreSQL
关注(0)|答案(2)|浏览(154)

我试图改变一个表中的列来修改knex枚举为本地类型,以受益于Postgres的类型系统,当我执行迁移时,我得到了这个错误类型"request_type" already exists,你知道这里发生了什么吗?

export async function up(knex: Knex): Promise<any> {
  return knex.schema.alterTable('appointments', table => {
    table.enu('type', ['video', 'physical'], { useNative: true, enumName: 'request_type' }).alter();
  });
}

export async function down(knex: Knex): Promise<any> {
  return knex.schema
    .alterTable('appointments', table => {
      table.dropColumn('type');
    })
    .then(() => knex.raw('CREATE TYPE request_type AS ENUM("video", "physical")'))
    .then(() => knex.raw('drop type request_type'));
}
f0brbegy

f0brbegy1#

看起来knex中有一个bug,导致在这样修改列时创建类型查询被添加两次。
https://runkit.com/embed/xqtl8p2knhi8

const Knex = require('knex');

const knex = Knex({
  client: 'pg',
});

knex.schema.alterTable('appointments', table => {
    table.enu('type', ['video', 'physical'], { useNative: true, enumName: 'request_type' }).alter();
}).toSQL()

/*
  Creates SQL:

0: Object {bindings: [], sql: "create type \"request_type\" as enum ('video', 'physical')"}
1: Object {bindings: [], sql: "create type \"request_type\" as enum ('video', 'physical')"}
2: Object {bindings: [], sql: "alter table \"appointments\" alter column \"type\" drop default"}
3: Object {bindings: [], sql: "alter table \"appointments\" alter column \"type\" drop not null"}
4: Object {bindings: [], …}
*/
rkue9o1l

rkue9o1l2#

有一种方法可以做到这一点。可以动态创建类型。
假设我想创建一个product列,并使用“product”枚举作为其允许的值。我们可以使用这个查询:

table.enu('product', ['level1', 'level2'], { useNative: true, enumName: 'product' })

这将动态创建类型,并将创建具有此枚举类型的列

相关问题