Re: Search system catalog for mystery type

Ok, it appears that relations and types automatically get a copy made of
some object with the same name, prefixed with a leading underscore. Now,
that was a dangerous choice! By convention, we all know that the safest
characters to use across DB platforms are lowercase chars and underscores.

In the past, I had been able to prefix tables, indexes, etc with
underscores. It is a tactic we use when I am "soft deleting" objects, or
installing new versions over old. The number of underscrores indicates how
many versions old it is. This convention (rather than the classic
"my_table_old") makes the tables float to the top of schema listings,
attracting attention during Db maintenance.

This was really common with us with PG for years, and now it errors out -
what happened, and when?

Carlo


"Alvaro Herrera" <alvherre@commandprompt.com> wrote in message
news:20091104205734.GJ3531@alvh.no-ip.org...
> Carlo Stonebanks wrote:
>> When I try the following command:
>> ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;
>>
>> I get the error message:
>>
>> ERROR: type "_audit_impt" already exists
>> SQL state: 42710
>
> Probably the easiest way around this is to use two underscores instead
> of one:
>
> ALTER TABLE mdx_core.audit_impt RENAME TO __audit_impt;
>
> Or any other char for that matter -- you picked the only one that would
> cause a problem.  Even a space should be fine:
>
> ALTER TABLE mdx_core.audit_impt RENAME TO " audit_impt";
>
> (Not sure I can recommend this though)
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>