Search code examples
sqlpostgresqldatabase-administration

Cannot create a table due to naming conflict

I am trying to create a table in my database and it gives me the following error.

ERROR:  type "tbl_last_alert" already exists
HINT:  A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existing type.

Then I thought that the table must exist so I ran the following query:

select * from pg_tables;

But could not find anything. Then I tried:

select * from tbl_last_alert;
ERROR:  relation "tbl_last_alert" does not exist

Any idea how to sort this?

i am tying to rename the type by 

ALTER TYPE v4report.tbl_last_alert RENAME TO tbl_last_alert_old;
ERROR:  v4report.tbl_last_alert is a table's row type
HINT:  Use ALTER TABLE instead.

and getting the error.

Solution

  • Postgres creates a composite (row) type of the same name for every table. That's why the error message mentions "type", not "table". Effectively, a table name cannot conflict with this list from the manual on pg_class:

    r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

    Bold emphasis mine. Accordingly, you can find any conflicting entry with this query:

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

    This covers all possible competitors, not just types. Note that the same name can exist multiple times in multiple schemas - but not in the same schema.

    Cure

    If you find a conflicting composite type, you can rename or drop it to make way - if you don't need it!

    DROP TYPE tbl_last_alert;

    Be sure that the schema of the type is the first match in your search path or schema-qualify the name. I added the schema to the query above. Like:

    DROP TYPE public.tbl_last_alert;