PostgreSQL: relation already exists

What it means

In PostgreSQL, a relation is anything stored in pg_class: tables, indexes, sequences, views, materialised views, foreign tables. They share one namespace per schema, so a schema can’t have a table and an index both called invoices. Your CREATE asked for a name that something in that schema already has:

ERROR:  relation "invoices" already exists

The message doesn’t say what kind of object holds the name, or which schema. The schema is the one you named, or, for an unqualified name, the first schema on your search_path (usually public). The same table name in another schema is no problem.

Two relatives: column "total" of relation "invoices" already exists (code 42701, from ALTER TABLE … ADD COLUMN), and type "status" already exists, which also appears when you create a table named like an existing type, because every table gets a type of the same name.

Common causes

A migration ran twice, or ran by hand before the migration tool recorded it, so the tool tries again.
An index named like a table or another index, often by a migration that names indexes by hand, or a constraint whose automatic index name (invoices_pkey) is already used.
A restore into a database that isn’t empty: psql -f dump.sql or pg_restore without --clean into a database that already has the objects.
Unquoted names: CREATE TABLE Invoices creates invoices, so it clashes with an existing invoices. Only "Invoices" in double quotes is a different name.
A leftover from a failed run, in a tool that doesn’t wrap DDL in a transaction.

How to fix it

Find what holds the name

SELECT n.nspname AS schema, c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'invoices';

relkind is r for a table, i index, S sequence, v view, m materialised view, p partitioned table. In psql, \d invoices describes it.

Skip it if it’s already there

CREATE TABLE IF NOT EXISTS invoices (id integer PRIMARY KEY, total numeric);
CREATE INDEX IF NOT EXISTS invoices_total_idx ON invoices (total);
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS total numeric;

These give a NOTICE instead of an error. They only check the name: if the existing table has different columns, IF NOT EXISTS won’t tell you. Use it for idempotent setup scripts, not to paper over a migration history that’s out of step.

Fix the migration history

If the object is right and the tool doesn’t know it, mark that migration as applied using the tool’s own command, rather than editing its bookkeeping table by hand. If the object is a leftover from a failed run and you’re sure it holds nothing you need, drop it and run the migration again.

Rename the new object

Give indexes names that include the table and columns (invoices_total_idx), and keep table and index names apart.

Restore into an empty database, or clean first

Create a fresh database for the restore, or let pg_restore drop each object before recreating it:

pg_restore --clean --if-exists -d <database> backup.dump

--clean drops objects that are in the backup, so only use it on a database you mean to overwrite.

Reproduce it

On PostgreSQL 18.6, in schema seo_err_pg:

CREATE TABLE seo_err_pg.invoices (id integer PRIMARY KEY, total numeric);
CREATE TABLE seo_err_pg.invoices (id integer PRIMARY KEY, total numeric);
CREATE INDEX invoices ON seo_err_pg.invoices (total);
CREATE SEQUENCE seo_err_pg.invoices_pkey;
CREATE TABLE seo_err_pg.Invoices (id int);

ERROR:  relation "invoices" already exists
ERROR:  relation "invoices" already exists
ERROR:  relation "invoices_pkey" already exists
ERROR:  relation "invoices" already exists

The first CREATE TABLE worked; the index named invoices, a sequence named like the primary key’s index, and the unquoted Invoices all clashed. CREATE TABLE seo_err_pg."Invoices" worked: a different name. With IF NOT EXISTS:

NOTICE:  relation "invoices" already exists, skipping
CREATE TABLE

ADD COLUMN total numeric on the existing column gave column "total" of relation "invoices" already exists, and after CREATE TYPE seo_err_pg.status, CREATE TABLE seo_err_pg.status gave type "status" already exists with a hint that a relation has an associated type of the same name. With \set VERBOSITY verbose, psql shows the code: ERROR: 42P07: relation "invoices" already exists. PostgreSQL 14.24 gives the same messages.

In Inlet

⌘P opens a table by name, and the structure editor shows its indexes and constraints, so you can see what already holds a name. Inlet’s backups and restores use the bundled pg_dump/pg_restore. When a statement fails, Inlet shows the server’s error; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.

relation already exists