Re: Drop only temporary table

On Fri, Mar 18, 2016 at 9:31 AM, Aleksander Alekseev <
a(dot)alekseev(at)postgrespro(dot)ru> wrote:

> You can use schema name as a prefix:
>
> postgres=# \d+
> List of relations
> Schema | Name | Type | Owner | Size | Description
> -----------+------+-------+----------+------------+-------------
> pg_temp_1 | t | table | postgres | 8192 bytes |
> (1 row)
>
> postgres=# drop table pg_temp_1.t;
> DROP TABLE
>
> But generally speaking I suggest you generate random names for
> temporary tables.
>
> On Fri, 18 Mar 2016 13:47:06 +0100
> Durumdara <durumdara(at)gmail(dot)com> wrote:
>
> > Dear PG Masters!
> >
> > As I experienced I can create normal and temp table with same name.
> >
> > create table x (kod integer);
> >
> > CREATE TEMPORARY TABLE x (kod integer);
> >
> > select tablename from pg_tables where schemaname='public'
> > union all
> > select c.relname from pg_class c
> > join pg_namespace n on n.oid=c.relnamespace
> > where
> > n.nspname like 'pg_temp%'
> > and c.relkind = 'r'
> > and pg_table_is_visible(c.oid);
> >
> > ---
> >
> > I can see two x tables.
> >
> > As I see that drop table stmt don't have "temporary" suboption to
> > determine which to need to eliminate - the real or the temporary.
> >
> > Same thing would happen with other DDL/DML stmts - what is the
> > destination table - the real or the temporary?
> >
> > "insert into x(kod) values(1)"
> >
> > So what do you think about this problem?
> >
> > I want to avoid to remove any real table on resource closing (=
> > dropping of temporary table).
> > How to I force "drop only temporary"? Prefix, option, etc.
> >
> > Thanks for your help!
> >
> > dd
>
>
>
> --
> Best regards,
> Aleksander Alekseev
> http://eax.me/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It is not wise to create temp tables with the same name as actual tables.
It is always a good idea to prefix temp tables with something like "tmp_'
or "t_';

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.