3

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.

smn_onrocks
  • 1,282
  • 1
  • 18
  • 33

4 Answers4

7

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • `drop type v4report.tbl_last_alert; ERROR: cache lookup failed for relation 19108 ` getting the error any idea – smn_onrocks Mar 12 '14 at 07:20
  • ` schemaname | relname | relkind ------------+---------+--------- (0 rows) ` i am getting this – smn_onrocks Mar 12 '14 at 07:24
  • 1
    @smn_onrocks: I am afraid that something is seriously broken in your db. You might be able to fix it by reindexing your system tables. [Details in this related answer.](http://stackoverflow.com/questions/22191982/attribute-number-10-exceeds-number-of-columns-0/22282860#22282860) There may be a hardware problem at the root of this. – Erwin Brandstetter Mar 12 '14 at 07:32
  • 2
    @smn_onrocks Before you do anything else, follow the advice in: http://wiki.postgresql.org/wiki/Corruption – Craig Ringer Mar 12 '14 at 07:45
  • @Erwin Brandstetter have tried all your advice but still getting the error. – smn_onrocks Mar 12 '14 at 10:31
3

If you can't drop type, delete it from pg_type:

DELETE FROM pg_type where typname~'tbl_last_alert';
YRUsoDiao
  • 951
  • 6
  • 10
0

The error calls it a "type"; you probably have a type with that name somewhere.

Use this in psql to find out what anything is:

\d tbl_last_alert
Eevee
  • 47,412
  • 11
  • 95
  • 127
0

You could check it in pg_type table:

select * from pg_type where typname = 'tbl_last_alert'
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • `select * from pg_type where typname = 'tbl_last_alert'; tbl_last_alert | 16716 | 10 | -1 | f | c | C | f | t | , | 19108 | 0 | 19109 | recor d_in | record_out | record_recv | record_send | - | - | - | d | x | f | \x1A | 0 | -1 | 0 | 0 | | ` After run the query i am getting this now i want to drop the table how to do it – smn_onrocks Mar 12 '14 at 06:56
  • @smn_onrocks `drop type tbl_last_alert`. – xdazz Mar 12 '14 at 07:00
  • `drop type v4report.tbl_last_alert; ERROR: cache lookup failed for relation 19108 ` getting the error any idea – smn_onrocks Mar 12 '14 at 07:21