relation "profiles" already exists

Why it happens

Postgres refuses to create a relation whose name is already in use in the target schema. The full message names the object:

ERROR:  relation "profiles" already exists
SQLSTATE: 42P07

Three causes account for nearly all occurrences during a migration:

1. A non-idempotent statement ran twice. A migration step failed partway, was retried, and the CREATE TABLE public.profiles (...) at the top of the batch runs again against the table the first attempt already committed. Bare CREATE TABLE / CREATE INDEX / CREATE SEQUENCE are not safe to replay.

2. The object was created out of band. Someone created profiles in the Supabase SQL editor or table editor, or a Lovable-generated schema already provisioned it, and then the migration file that also defines it runs on the same database.

3. The name collides across a partial run. The destination was not empty. An earlier interrupted migration left half its tables behind; the next run replays the whole schema and trips on the first object that survived.

Note that 42P07 is specifically a name collision, not a data collision. A duplicate row on a primary key gives you 23505 instead — see duplicate-key-buckets-pkey.

How to fix it

First, confirm the object already exists and check what it looks like before you overwrite anything. Do not assume the existing table matches the one you are about to create.

-- Does it exist, and in which schema?
SELECT n.nspname AS schema, c.relname AS name, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'profiles';
-- relkind: r=table, v=view, m=matview, i=index, S=sequence

-- Inspect the existing columns
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'profiles'
ORDER BY ordinal_position;

If the existing table is correct and you just want the migration to skip it, make the statement idempotent:

CREATE TABLE IF NOT EXISTS public.profiles (
  id          uuid PRIMARY KEY REFERENCES auth.users (id) ON DELETE CASCADE,
  username    text UNIQUE,
  full_name   text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

IF NOT EXISTS returns a notice instead of an error and creates nothing when the name is taken. The same guard exists for the other relation kinds:

CREATE INDEX    IF NOT EXISTS idx_profiles_username ON public.profiles (username);
CREATE SEQUENCE IF NOT EXISTS public.profiles_seq;
CREATE OR REPLACE VIEW public.active_profiles AS SELECT * FROM public.profiles;

If the existing object is wrong or left over from a failed run and you want to recreate it cleanly, drop it first. Only do this on a destination you are willing to rebuild — CASCADE also drops dependent views, foreign keys, and policies:

DROP TABLE IF EXISTS public.profiles CASCADE;

CREATE TABLE public.profiles (
  id          uuid PRIMARY KEY REFERENCES auth.users (id) ON DELETE CASCADE,
  username    text UNIQUE,
  full_name   text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

To make an entire replay safe without dropping data, wrap the create-and-fill in a guard that only runs when the table is absent:

DO $
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND c.relname = 'profiles'
  ) THEN
    CREATE TABLE public.profiles (
      id uuid PRIMARY KEY,
      username text UNIQUE
    );
  END IF;
END $;

How to prevent it

• Write idempotent DDL. Every CREATE in a migration file should either use IF NOT EXISTS or be paired with a matching DROP ... IF EXISTS when you intend recreation. A migration file that cannot be re-run is a migration file that breaks on the first retry.
• Never edit an already-applied migration. Supabase records applied files in supabase_migrations.schema_migrations. Changing an applied file and re-pushing re-runs it. Add a new migration instead.
• Migrate into an empty schema. Point the destination at a fresh Supabase project, or reset public before a full schema replay, so leftover objects from an aborted run cannot collide. Confirm it is empty: SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';
• Keep dashboard changes out of the loop. Objects created by hand in the SQL editor are not in your migration history, so the next replay does not know they exist. Define schema in migration files only.
• Order retries around commits. If a step can be retried, make sure the whole step is idempotent — SupaMigrate replays schema in dependency order and each step is written to be safe to re-run, so a mid-migration failure resumes without tripping 42P07.