Implementing Global Temporary Tables in PostgreSQL with pgtt

Do Postgres have Global Temporary Table in the same way that Oracle and SQL Server do ?

No, PostgreSQL does not natively support global temporary tables in the same way Oracle and SQL Server do. While PostgreSQL accepts the GLOBAL keyword for compatibility, it ignores it—the keyword has no actual effect.

How PostgreSQL Temporary Tables Work

All temporary tables in PostgreSQL are session-local by default. This means:

Both the table definition (DDL) and data are visible only within the session that created them
The table is automatically dropped when the session ends
Other sessions cannot see or access the table structure or data

The Migration Challenge

While you can work around this limitation using techniques like permanent unlogged tables to replicate GTT functionality, the real difficulty arises during Oracle-to-PostgreSQL migrations.

You’ll need to rewrite PL/pgSQL code everywhere GTTs are used to align with the application logic—a time-consuming and error-prone process.

What Exactly Is a Global Temporary Table?

A Global Temporary Table (GTT) is a database object where:

No need to recreate the table definition in each session
The table structure is permanent and visible across all sessions
The data is session-specific or transaction-specific
Each session sees only its own data, maintaining isolation

pgtt Extension

To overcome the problem of temporary tables at local scope and to avoid difficulty of rewriting the plpgsql code everywhere GTT used pgtt extension came into limelight

Pgtt extension offers us the functionality of using the global temporary table syntax and mimics the behaviour same as Oracle’s GTT. This helps alot when we are migrating a oracle database to postgres. Moreover we can create indexes and constraints on global temporary tables like any other tables

This extension was developed by Gilles Darold, Julien Rouhaud and it is a free software distributed under the PostgreSQL Licence.

Pgtt extension can be installed on top of your postgres by following the below steps

Installation

Installing the pgtt extension from the github.

sudo apt-get install build-essential postgresql-server-dev-17 git -y
cd /tmp 
git clone https://github.com/darold/pgtt.git 
cd pgtt

# Build and install 
make 
sudo make install
sudo apt-get update

Verify Installation

# Check extension files
ls /usr/share/postgresql/17/extension/pgtt*

# Check shared library
ls /usr/lib/postgresql/17/lib/pgtt.so

# List all pgtt-related files
ls /usr/share/postgresql/17/extension/ | grep pgtt

Configure

Configure by changing the session_preload_libraries parameter connecting to database.

ALTER DATABASE postgres SET session_preload_libraries= 'pgtt';

Enable the Extension

Create the extension where you want to use Global Temporary Tables

CREATE EXTENSION pgtt;

postgres=# \dx pgtt
List of installed extensions
-[ RECORD 1 ]---+---------------------------------------------------------------
Name            | pgtt
Version         | 4.4.0
Default version | 4.4.0
Schema          | pgtt_schema
Description     | Extension to add Global Temporary Tables feature to PostgreSQL

postgres=#

The pgtt extension use a dedicated schema to store related object in pgtt_schema the extension take care of having this schema at end of search_path always.

#Check pgtt schema and tables
postgres=# \dn pgtt_schema
    List of schemas
    Name     |  Owner   
-------------+----------
 pgtt_schema | postgres
(1 row)

postgres=# \dt pgtt_schema.*
                     List of tables
   Schema    |         Name          | Type  |  Owner   
-------------+-----------------------+-------+----------
 pgtt_schema | pg_global_temp_tables | table | postgres
(1 row)

postgres=#

Testing

Let’s create a Global temporary table and insert the data

#Create a global temporary table
CREATE GLOBAL TEMPORARY TABLE temp_invoice_data (
    invoice_id bigint,
    invoice_number varchar(50),
    total_amount numeric(10,2)
) ON COMMIT PRESERVE ROWS;

#Insert test data
INSERT INTO temp_invoice_data VALUES 
    (1, 'INV-001', 1000.00),
    (2, 'INV-002', 2500.50);

postgres=# SELECT * FROM temp_invoice_data;
invoice_id | invoice_number | total_amount 
------------+----------------+--------------
          1 | INV-001        |      1000.00
          2 | INV-002        |      2500.50
(2 rows)

#View all global temporary tables
postgres=# SELECT * FROM pgtt_schema.pg_global_temp_tables;
 relid |   nspname   |      relname      | preserved |              code               
-------+-------------+-------------------+-----------+---------------------------------
 16407 | pgtt_schema | temp_invoice_data | t         |                                +
       |             |                   |           |     invoice_id bigint,         +
       |             |                   |           |     invoice_number varchar(50),+
       |             |                   |           |     total_amount numeric(10,2) +
       |             |                   |           | 
(1 row)

postgres=#

Let’s check the table structure and data in a session.

#Check the data
postgres=# select count(*) from temp_invoice_data;
-[ RECORD 1 ]
count | 2

#Check the table structure
postgres=# \d+ temp_invoice_data
                                              Table "pg_temp_3.temp_invoice_data"
     Column     |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 invoice_id     | bigint                |           |          |         | plain    |             |              | 
 invoice_number | character varying(50) |           |          |         | extended |             |              | 
 total_amount   | numeric(10,2)         |           |          |         | main     |             |             | 
Access method: heap

Let’s exit the current session and reconnect to the database and check the table structure and data.

#Exit the current session
postgres=# \q
postgres@kushwanth:~$

#Login to the new session
postgres@kushwanth:~$ psql
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2), server 17.7 (Ubuntu 17.7-3.pgdg24.04+1))
Type "help" for help.

postgres=#

#Check the data 
postgres=# select count(*) from temp_invoice_data;
 count 
-------
     0
(1 row)

postgres=#

#Check the table structure
postgres=# \d+ temp_invoice_data
                                              Table "pg_temp_4.temp_invoice_data"
     Column     |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 invoice_id     | bigint                |           |          |         | plain    |             |              | 
 invoice_number | character varying(50) |           |          |         | extended |             |              | 
 total_amount   | numeric(10,2)         |           |          |         | main     |             |              | 
Access method: heap

postgres=#

Here, we can see that the table structure is retained but not the data.

Conclusion

Having an extension like pgtt on your postgres is a true productiveness especially when you are migrating oracle database to PostgreSQL by having the same syntax as GTT without any functional changes or rewriting plpgsql code.

Additional Resources

pgtt GitHub Repository: https://github.com/darold/pgtt