In PostgreSQL we have different types of tables, like temporary tables, unlogged tables, logged tables, and normal tables. But in Oracle Database, there is a feature named "global temporary tables," and in PostgreSQL, it doesn't have this one. But by using the pgtt extension, we can create global temporary tables inside PostgreSQL.
clone the repository.
git clone https://github.com/darold/pgtt.git
Move to the cloned directory.
Cd pgtt
Now you can make this extension in your system installed postgres like this:
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install
If you are using postgres version 17, just change the version number in the installation commands like this.
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config install
Log into the psql terminal and check that the pgtt extension is available to create or not.
sudo su postgres
psql
In psql there is a pg catalogue named pg_available_extensions to see what the available extensions in PostgreSQL we can create and use are.
Check the records from pg_available_extensions to see the available extensions.
select * from pg_available_extensions where name = 'pgtt';
Result:
name | default_version | installed_version | comment
------+-----------------+-------------------+----------------------------------------------------------------
pgtt | 4.5.0 | | Extension to add Global Temporary Tables feature to PostgreSQL
(1 row)
So now create the extension.
create extension pgtt ;
Explore the features provided by this extension by using the below command.
\dx+ pgtt
Result:
Objects in extension "pgtt"
Object description
------------------------------
table pg_global_temp_tables
type pg_global_temp_tables
type pg_global_temp_tables[]
(3 rows)
Now set the value for the postgres configuration parameter named session_preload_libraries to pgtt.
This is because the pgtt extension is actually accepting SQL from each separate connection, and each one is a separate backend connection.
set session_preload_libraries = 'pgtt';
Check the value of the session preload libraries again to ensure the value is changed or not.
show session_preload_libraries ;
Result:
session_preload_libraries
---------------------------
pgtt
(1 row)
We have a custom configuration parameter named pgtt.enabled to turn on and off the feature of global temporary tables creation.
show pgtt.enabled ;
Result:
-[ RECORD 1 ]+---
pgtt.enabled | on
We can check more details about this parameter from pg_settings like this.
select * from pg_settings where name = 'pgtt.enabled';
Result:
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
name | pgtt.enabled
setting | on
unit |
category | Customized Options
short_desc | Enable use of Global Temporary Table
extra_desc | By default the extension is automatically enabled after load, it can be temporary disable by setting the GUC value to false then enable again later wnen necessary.
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Now we are going to open this in two separate psql sessions to check if the global temporary table created in one session is seen in another psql session.
So now we are checking the current psql session process id.
SELECT pg_backend_pid();
Result:
pg_backend_pid
----------------
151870
(1 row)
Now use the below syntax to create a global temporary table by using the /*GLOBAL*/ keyword.
CREATE /*GLOBAL*/ TEMPORARY TABLE emp_temp
(
emp_id integer,
emp_name text,
salary numeric
)
ON COMMIT PRESERVE ROWS;
Insert some sample records.
insert into emp_temp (emp_id,emp_name,salary) values ('1','shiju','50000');Based on this extension, we have a separate schema to store the global temporary tables and their related metadata in the schema named pgtt.
select * from pgtt_schema.
Result:
pgtt_schema.emp_temp pgtt_schema.pg_global_temp_tables
Now check the records from this table.
select * from pgtt_schema.emp_temp ;
Result:
emp_id | emp_name | salary
--------+----------+--------
1 | shiju | 50000
(1 row)
We can get the global temporary table definition from the separate catalogue named pg_global_temp_tables.
select * from pgtt_schema.pg_global_temp_tables ;
Result:
relid | nspname | relname | preserved | code
-------+-------------+----------+-----------+---------------------
42932 | pgtt_schema | emp_temp | t | +
| | | | emp_id integer,+
| | | | emp_name text, +
| | | | salary numeric +
| | | |
(1 row)
Now log into another psql terminal and check its process id.
SELECT pg_backend_pid();
Result :
pg_backend_pid
----------------
153971
(1 row)
Check that the global temporary table created in the one session can be seen in another session.
select * from pgtt_schema.emp_temp ;
Result:
emp_id | emp_name | salary
--------+----------+--------
1 | shiju | 50000
(1 row)
Now check the table definition of global temporary tables from this session to verify that it can be seen from this session.
select * from pgtt_schema.pg_global_temp_tables ;
Result:
relid | nspname | relname | preserved | code
-------+-------------+----------+-----------+---------------------
42932 | pgtt_schema | emp_temp | t | +
| | | | emp_id integer,+
| | | | emp_name text, +
| | | | salary numeric +
| | | |
(1 row)
Now we verified that the global temporary tables created in one session can be seen in the other session.
Now check the global temporary table statistics from the pg catalogue named pg_stat_user_tables.
select relid,schemaname,relname,seq_scan from pg_stat_user_tables where relname = 'emp_temp';
Now we have opened two psql sessions, and now the emp_table is created in two separate schemas like this. This is why we can see two pg_temp_* schemas and both are actually store the global temporary table and its data.
Result:
relid | schemaname | relname | seq_scan
-------+-------------+----------+----------
42932 | pgtt_schema | emp_temp | 0
42937 | pg_temp_3 | emp_temp | 2
42944 | pg_temp_4 | emp_temp | 6
(3 rows)
The creation of an index on the global temporary table and also dropping the global temporary table will fail when the value of the parameter named pgtt.enabled is on.
CREATE INDEX ON emp_temp (emp_id);
Result:
ERROR: a temporary table has been created and is active, can not add an index on the GTT table in this session.
Now try to drop the table.
drop table emp_temp ;
Result:
ERROR: can not drop a GTT that is in use.
Now set the value for the parameter named pgtt.enabled to off and try again these commands.
CREATE INDEX ON emp_temp (emp_id);
Result:
CREATE INDEX
Drop the global temporary table.
drop table emp_temp ;
Result:
DROP TABLE
Limitations of the pgtt extension.
- Unsupported the creation of foreign key references in the global temporary tables.
- Unsupported the partition concept in the global temporary tables.
By using the pgtt extension, we can create the global temporary tables and use these tables in the other session also, and it has some limitations like the unsupported partition and unsupported foreign key creation. And also, we can only drop the table and create the index on the global temporary table based on the value of the parameter named pgtt.enabled.
Knowing these pgtt extension functionalities is very useful for PostgreSQL developers and database administrators, and they can use this feature for their specific needs.