Friday, October 20

Funny temporary table behavior in Postgres

Was playing around with temporary tables in PostgreSQL and found this quite weird behavior.
When you have a table called say animal and create a new temporary table with the same name postgres is totally ok with the "duplicate" name. Since the temporary table ends up in a different schema I presume. Anyway, it was quite confusing before I figured out what was going on.
It's the same both in 8.1 and 8.2beta. Not sure if it to be expected, a oversight from my side or simply a "bug".

Example:
[postgres@dolphin ~]$ psql
Welcome to psql 8.2beta1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# create user test password 'test';
CREATE ROLE
postgres=# create database testdb owner test template template0;
CREATE DATABASE
postgres=# \c testdb test
You are now connected to database "testdb".
testdb=> create table animal (dog int);
CREATE TABLE
testdb=> \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
public | animal | table | test
(1 row)

testdb=> \d animal
Table "public.animal"
Column | Type | Modifiers
--------+---------+-----------
dog | integer |

testdb=> create temporary table animal (cat int);
CREATE TABLE
testdb=> \d
List of relations
Schema | Name | Type | Owner
-----------+--------+-------+-------
pg_temp_1 | animal | table | test
(1 row)

testdb=> \d animal
Table "pg_temp_1.animal"
Column | Type | Modifiers
--------+---------+-----------
cat | integer |

testdb=> drop table animal;
DROP TABLE
testdb=> \d animal
Table "public.animal"
Column | Type | Modifiers
--------+---------+-----------
dog | integer |

testdb=>

No comments: