[GH-ISSUE #308] DB Schema Creation Error using PostgreSQL #247

New issue

Open

opened 2026-02-26 09:37:04 +03:00 by kerem · 1 comment

kerem commented

2026-02-26 09:37:04 +03:00

Owner

Originally created by @Proxymiity on GitHub (Aug 7, 2023).
Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/308

When initializing the database with Postgres, the relation IX_Username_1 is already used on admin (username):

CREATE TABLE admin (id BIGINT NOT NULL, username VARCHAR(255) DEFAULT NULL, password VARCHAR(255) NOT NULL, super BOOLEAN DEFAULT 'false' NOT NULL, active BOOLEAN DEFAULT 'true' NOT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX IX_Username_1 ON admin (username);
[...]
CREATE UNIQUE INDEX IX_Username_1 ON mailbox (username);

This causes the following error: [42P07] ERROR: relation "ix_username_1" already exists.

Using orm:schema-tool:create --dump-sql and changing the relation to ix_username_2 solves the problem.

Originally created by @Proxymiity on GitHub (Aug 7, 2023). Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/308 When initializing the database with Postgres, the relation `IX_Username_1` is already used on `admin (username)`: ```sql CREATE TABLE admin (id BIGINT NOT NULL, username VARCHAR(255) DEFAULT NULL, password VARCHAR(255) NOT NULL, super BOOLEAN DEFAULT 'false' NOT NULL, active BOOLEAN DEFAULT 'true' NOT NULL, created TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, modified TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX IX_Username_1 ON admin (username); [...] CREATE UNIQUE INDEX IX_Username_1 ON mailbox (username); ``` This causes the following error: `[42P07] ERROR: relation "ix_username_1" already exists`. Using `orm:schema-tool:create --dump-sql` and changing the relation to `ix_username_2` solves the problem.

kerem commented

2026-02-26 09:37:05 +03:00

Author

Owner

@frlan commented on GitHub (Mar 23, 2024):

Unsure whether this is the best solution

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    indexname = 'ix_username_1'
ORDER BY
    tablename,
    indexname;

Results in

 tablename |   indexname   |                                  indexdef                                  
-----------+---------------+----------------------------------------------------------------------------
 mailbox   | ix_username_1 | CREATE UNIQUE INDEX ix_username_1 ON public.mailbox USING btree (username)
(1 Zeile)

@frlan commented on GitHub (Mar 23, 2024): Unsure whether this is the best solution ```sql SELECT tablename, indexname, indexdef FROM pg_indexes WHERE indexname = 'ix_username_1' ORDER BY tablename, indexname; ``` Results in ``` tablename | indexname | indexdef -----------+---------------+---------------------------------------------------------------------------- mailbox | ix_username_1 | CREATE UNIQUE INDEX ix_username_1 ON public.mailbox USING btree (username) (1 Zeile) ```