CODEWITHSUNDEEP
databasepostgresqlindexingnamespaces
A. L
A. L

Reputation: 12639

PostgreSQL Error: Relation already exists - FOREIGN KEY in CREATE TABLE

I am making a table as follows:

CREATE TABLE creator.lists
(
    _id bigserial PRIMARY KEY NOT NULL,
    account_id bigint NOT NULL,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    display_name text DEFAULT '',
    name text DEFAULT '',
    extra jsonb,

    FOREIGN KEY (account_id)
        REFERENCES creator.accounts (_id)
            ON DELETE CASCADE
);

but I get this error:

ERROR:  relation "account_id_index" already exists

When I run:

CREATE INDEX
    account_id_index
ON
    creator.lists
(
    account_id
);

How do I create an index on the foreign key? I am running v11.1

Just a note, that I've also ran a similar command before for another table:

CREATE INDEX
    account_id_index
ON
    creator.contacts
    (
        account_id
    );

I don't suppose that index names need to be unique between tables?

Upvotes: 2

Views: 8973

Answers (2)

A. L
A. L

Reputation: 12639

Okay, it seems like index names need to be unique as removing the naming fixed it:

CREATE INDEX
ON
    creator.contacts
    (
        account_id
    );

From the documentation:

name The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table. If the name is omitted, PostgreSQL chooses a suitable name based on the parent table's name and the indexed column name(s).

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246033

Indexes live in the same namespace as tables, views and sequences, so you cannot use the same name twice for any of these objects in one schema.

Either choose a different name, or have PostgreSQL choose one for you:

CREATE INDEX ON creator.lists (account_id);

Upvotes: 6

Related Questions