Search code examples
postgresqlheidisql

Postgres syntax error on UNIQUE INDEX - HeidiSQL

HeidiSQL generated the following creation code:

CREATE TABLE "books" (
    "id" BIGINT NOT NULL,
    "creation_date" TIMESTAMP NOT NULL,
    "symbol" VARCHAR NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE INDEX "symbol" ("symbol")
)
;
COMMENT ON COLUMN "books"."id" IS E'';
COMMENT ON COLUMN "books"."creation_date" IS E'';
COMMENT ON COLUMN "books"."symbol" IS E'';

And when I try to submit, I get the following error:

IMG

Is it an HeidiSQL bug with PostgreSQL?

Solution

  • There is a recommendation note for how you should create an UNIQUE INDEX in PostgreSQL:

    The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

    There are a couple of ways of creating a UNIQUE INDEX on PostgreSQL.

    1. The first way is, as mentioned above, using ALTER TABLE on a previously created table:

      ALTER TABLE books ADD UNIQUE ("symbol");

      OR

      ALTER TABLE books ADD CONSTRAINT UQ_SYMBOL UNIQUE ("symbol")

      Note: this approach uses the auto-generation of indexes of PostgreSQL (that is, PostgreSQL will identify that that column is unique and add an index to it).

    2. The second way is using CREATE INDEX:

      CREATE UNIQUE INDEX "symbol" ON books("symbol");

    3. Last but not least, you can simply omit, in the table creation, the INDEX keyword and let PostgreSQL do the magic (create the index) for you:

      CREATE TABLE "books" (
  "id" BIGINT NOT NULL,
  "creation_date" TIMESTAMP NOT NULL,
  "symbol" VARCHAR NOT NULL,
  PRIMARY KEY ("id"),
  UNIQUE ("symbol")
);

      Note: The syntax UNIQUE "symbol"("symbol") could be a confusion made with method 2, as in that method one it is required to specify both the table and the column name (books("symbol")).

    Therefore, that is not a PostgreSQL bug, but rather a HeidiSQL one.

    Edit: I was able to reproduce the bug and opened an issue on github.