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

Question

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?

Laurenz Albe · Accepted Answer · 2019-02-05 16:10:20Z

6

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);

answered Feb 5, 2019 at 16:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A. L Over a year ago

I've decided to let psql do it for me :)

jhoepken · Accepted Answer · 2022-06-02 19:58:31Z

1

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).

edited Jun 2, 2022 at 19:58

jhoepken

1,8663 gold badges17 silver badges24 bronze badges

answered Feb 5, 2019 at 16:09

A. L

12.7k29 gold badges100 silver badges179 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related