Tutorial 19-PostgreSQL Temp table

In this session we are going to discuss table management (Temp table) in postgresql


What is temp table?

- The table is temporary
- Temporary table stores the content specific to the session
- it will available duration of this session
- if the session established this table cant be used by other session
- it would be stored in temporary tablespace
ex:Temp_tbs

what is the use?

it will be used for report, migration activities

to check temp tablespaces

show temp_tablespaces;

to view the physical location

select spcname,pg_tablespace_location(oid) from pg_tablespace;

to view the temp tables


select schemaname,tablename,tableowner,tablespace from pg_tables where
tablename in ('ocdr','ocpr','ocdrp');


Types of temp tables


Note: by default autocommit is on we are disable it for this below
demo

1. on commit delete rows - structure will be there rows will be
deleted
create temp table ocdr (no int) on commit delete rows
tablespace temp_tbs;

2.on commit preserve rows - structure and rows will be there after
commit

create temp table ocpr (no int) on commit preserve rows
tablespace temp_tbs;

3.on commit drop - structrue and rows drop

create temp table drop (no int) on commit drop
tablespace temp_tbs;

subscribe to our channel:
-----------------------------------------
https://rebrand.ly/8z3l1y1

For doubts and queries kindly contact us on
-----------------------------------------------------------------------
[email protected]

Follow us:

www.facebook.com/MyNotesOracleDba

#temptable #tablemanagement #typesoftemptables