Drop only temporary table

Durumdara <durumdara@gmail.com> writes:

As I experienced I can create normal and temp table with same name.

Sure.

As I see that drop table stmt don't have "temporary" suboption to determine
which to need to eliminate - the real or the temporary.

Once you've created a temp table, it masks any normal table of the same name
(unless you use a schema-qualified reference to the normal table).

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Mar 18, 2016 at 9:31 AM, Aleksander Alekseev <
a.alekseev@postgrespro.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@gmail.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@postgresql.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.

On Friday, March 18, 2016, Durumdara <durumdara@gmail.com> wrote:

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.

If you have to explicitly drop a temporary table you are likely doing
something wrong. When you create the table you tell it when to go away and
at that time it will - no need for a drop statement.

David J.