Array of enums column doesn't work at all

[iFonny]

Issue type:

[ ] question
[ x ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ x ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ x ] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Hello!

I have a problem with creating a Column for my ReviewEntity. I want to insert some enums inside an Array. It's working with an enum with only 1 char (like in this example, I have no idea why) but not if I add a value in enum after creation or if I use words.

It's working on the first launch (creation), but If I run again (with sync enabled) I will get an error.

review.entity

  @Column({
    name: 'type',
    type: 'enum',
    enum: ReviewTypeEnum,
    array: true
  })
  type: ReviewTypeEnum[];

review.enums

export enum ReviewTypeEnum {
  Text = 'text',
  Image = 'image',
  Audio = 'audio',
  Video = 'video',
}

Full error:

query failed: ALTER TYPE "public"."_review_types_enum" RENAME TO "review_types_enum_old"
error: { error: cannot alter array type review_types_enum[]
    at Connection.parseE (/Users/ifonny/Travail/Lucette/Lucette-API-nestjs/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/Users/ifonny/Travail/Lucette/Lucette-API-nestjs/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/Users/ifonny/Travail/Lucette/Lucette-API-nestjs/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at Socket.EventEmitter.emit (domain.js:441:20)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 181,
  severity: 'ERROR',
  code: '42809',
  detail: undefined,
  hint:
   'You can alter type review_types_enum, which will alter the array type as well.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'typecmds.c',
  line: '3279',
  routine: 'RenameType' }

[TheNoim]

Please, a Hot Fix would be appreciated. This bug is very annoying.

[TheNoim]

I created a WIP fix. The only thing which prevents me to create a pr, is the requirement to create a test...

Index: src/driver/postgres/PostgresQueryRunner.ts
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- src/driver/postgres/PostgresQueryRunner.ts	(date 1563718253000)
+++ src/driver/postgres/PostgresQueryRunner.ts	(date 1563909453000)
@@ -2006,9 +2006,28 @@
         }
         const result = await this.query(`SELECT "udt_schema", "udt_name" ` +
             `FROM "information_schema"."columns" WHERE "table_schema" = '${schema}' AND "table_name" = '${name}' AND "column_name"='${column.name}'`);
+
+        let udtName = result[0]["udt_name"];
+
+        // You can not modify the array type. You need to edit the enum itself.
+        if (column.isArray) {
+            // Find the information about the array type
+            const typeResult = await this.query(`select "typelem" from pg_type where "typname" = '${udtName}';`);
+
+            const typelem = typeResult[0]["typelem"];
+
+            // Get with the oid of the enum array type the enum type
+            const baseTypeNameResult = await this.query(`select "typname" from pg_type where oid = ${typelem}`);
+
+            const baseTypeName = baseTypeNameResult[0]["typname"];
+
+            // Replace the udtName with the enum type
+            udtName = baseTypeName;
+        }
+
         return {
             enumTypeSchema: result[0]["udt_schema"],
-            enumTypeName: result[0]["udt_name"]
+            enumTypeName: udtName
         };
     }
 

[rained23]

encountered this bug too, need to drop the database because of this.

[elie222]

Dropping the database works but not a good solution for production

[JosephYeoh]

Not a solution! But Instead of dropping the whole database, you could drop the column used by the enum with a migration that looks something like this:

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "user" DROP COLUMN "foo"`, undefined);
    await queryRunner.query(`DROP TYPE "fooEnum"`, undefined);
    await queryRunner.query(`CREATE TYPE "fooEnum" AS ENUM('EnumA', 'EnumB', 'EnumC')`, undefined);
    await queryRunner.query(`ALTER TABLE "user" ADD "foo" "fooEnum" array NOT NULL DEFAULT '{}'`, undefined);
}

public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "user" DROP COLUMN "foo"`, undefined);
    await queryRunner.query(`DROP TYPE "fooEnum"`, undefined);
    await queryRunner.query(`CREATE TYPE "public"."fooEnum" AS ENUM('EnumA', 'EnumB')`, undefined);
    await queryRunner.query(`ALTER TABLE "user" ADD "foo" "fooEnum" array NOT NULL DEFAULT '{}'`, undefined);
}

You will still need to manually populate the dropped column.

[devdammit]

@TheNoim just clean ur build or dist folder.

[TheNoim]

@TheNoim just clean ur build or dist folder.

Why? About what are you talking? My pull request is a much better solution for this issue.

[carlocorradini]

Does we have a solution for this problem???
I am still getting the error... :(

[machendos]

@TheNoim are u in progress of fix now? I also have this problem

[TheNoim]

Yes, I did. But someone needs to merge it: #6126

[patrykkarny]

+1 same issue

[jeromemeichelbeck]

Ok, this bugs me because I'm pretty sure I used an array of enums in a past project a few months ago and I didn't get this error.
But I used

@Column('enum', {
    array: true,
    enum: Object.values(RolesEnum),
    default: [RolesEnum.reader],
})

Instead of

@Column('enum', {
    array: true,
    enum: RolesEnum,
    default: [RolesEnum.reader],
})

Now I can't make it work either way!
Very frustrating...

[carlocorradini]

Finally🤩🤯🥳