Re: [GENERAL] Drop only temporary table
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 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
Re: [GENERAL] Drop only temporary table
On Friday, March 18, 2016, Durumdara 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.
Re: [GENERAL] Drop only temporary table
On Fri, Mar 18, 2016 at 9:31 AM, Aleksander Alekseev < a.aleks...@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 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.
[GENERAL] Drop only temporary table
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
Re: [GENERAL] Drop only temporary table
Durumdara 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
