relation already exists

This error occurs when you try to create a table, view, or index that already exists in the database.

The relation already exists error means the table, view, or index name is already used.

Understanding the Error

ERROR: relation "users" already exists

A table, view, sequence, or index with that name already exists.

Common Causes

1. Running CREATE TABLE Twice

SQL

CREATE TABLE users (id INT);  -- Works
CREATE TABLE users (id INT);  -- Error: already exists

2. Migration Run Multiple Times

Schema migration script executed repeatedly.

3. Same Name for Different Objects

SQL

CREATE TABLE products (id INT);
CREATE VIEW products AS SELECT * FROM orders;  -- Error: name taken

How to Fix It

Solution 1: Use IF NOT EXISTS

SQL

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255)
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Note: CREATE VIEW IF NOT EXISTS added in PostgreSQL 9.5+
CREATE VIEW IF NOT EXISTS user_summary AS SELECT ...;

Solution 2: Drop and Recreate

SQL

-- For development only - loses data!
DROP TABLE IF EXISTS users CASCADE;
CREATE TABLE users (id SERIAL PRIMARY KEY);

Solution 3: Check Before Creating

SQL

-- Check if table exists
SELECT EXISTS (
  SELECT FROM information_schema.tables
  WHERE table_schema = 'public'
  AND table_name = 'users'
);

Solution 4: Use CREATE OR REPLACE for Views

SQL

-- Only works for views, not tables
CREATE OR REPLACE VIEW user_summary AS
SELECT id, email FROM users;

Solution 5: Rename Existing Object

SQL

-- Rename old table
ALTER TABLE users RENAME TO users_old;

-- Create new table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255)
);

-- Migrate data if needed
INSERT INTO users (id, email) SELECT id, email FROM users_old;

-- Drop old table
DROP TABLE users_old;

Solution 6: Use Different Schema

SQL

-- Create in different schema
CREATE SCHEMA archive;
CREATE TABLE archive.users (id INT);

-- Now both can exist
SELECT * FROM public.users;
SELECT * FROM archive.users;

Checking What Exists

SQL

-- List all relations in public schema
SELECT relname, relkind
FROM pg_class
WHERE relnamespace = 'public'::regnamespace
AND relkind IN ('r', 'v', 'i', 's');  -- table, view, index, sequence

-- relkind: 'r' = table, 'v' = view, 'i' = index, 's' = sequence

Best Practices

Use IF NOT EXISTS in all CREATE statements
Use migrations that track what's been applied
Use schemas to organize related objects
Avoid DROP in production migrations
Test migrations on copy of production data