Post reply

PostgreSQL Temporary Table

  • February 10, 2023 at 12:00 am

    #4145202

    Comments posted to this topic are about the item PostgreSQL Temporary Table

  • February 11, 2023 at 3:08 pm

    This was removed by the editor as SPAM

  • February 11, 2023 at 3:41 pm

    This was removed by the editor as SPAM

  • February 18, 2023 at 9:01 am

    #4151360

    In Redshift, which is a cousin of PostgreSQL, you can have both a "temporary" table and a "#" table as in Sql Server. Both are temporary tables. Is this the case in pure Postgres and if so what would be the difference ?

    ----------------------------------------------------

  • March 1, 2023 at 6:58 pm

    #4155586

    Great article, but I think there are some tweaks can be made to make it more accurate  :

    1. A temporary table gets automatically dropped on closing the session.     

    That behaviour is controlled by ON COMMIT  and can be changed to drop table at the end of transaction rather than at the end of session.

    2. A temporary table does not belong to any schema.

    Postgres automatically creates pg_temp_nnn schemas which can be aliased by the session that creates temp table as  pg_temp. The actual schema name can be found with

    select pg_namespace.nspname from pg_catalog.pg_namespace where pg_namespace.oid=pg_my_temp_schema()

    3. When a temporary table shares the same name as that of a permanent table, the permanent table cannot be accessed until the temporary table gets dropped from the database or session. 

    Permanent table (tables) can always be accessed by full name [schema_name].[table_name] regardless of whether temp table exist.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply