Search code examples
postgresqltemp-tablesglobal-temp-tablespostgresql-17

Oracle style global temporary table in PostgreSQL

While migrating database from Oracle to PostgreSQL, I came across "Global Temporary Table" which I need to migrate equivalent to PostgreSQL.

Document say's

Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated

Try 1: PostgreSQL Global Temporary Table v2.0

CREATE EXTENSION pgtt;

Error:

SQL Error [0A000]: ERROR: extension "pgtt" is not available
Detail: Could not open extension control file "C:/Program 
Files/PostgreSQL/17/share/extension/pgtt.control": No such file or directory.
Hint: The extension must first be installed on the system where PostgreSQL is running.

Try 2: How to Leverage Local Temporary Table for Global Temporary Table in EDB Postgres

There are multiple database objects used to achieve Global temporary table functionality in PostgreSQL.

  1. UNLOGGED TABLE
  2. CREATE VIEW
  3. CREATE FUNCTION
  4. CREATE TRIGGER

Thoughts : Worried about performance with this approach.

What will be the equivalent in PostgreSQL?

Solution

  • What will be the equivalent in PostgreSQL?

    There is none. The global/local distinction is tied to SQL modules that have no use and don't exist in PostgreSQL. Quoting the doc:

    The SQL standard also distinguishes between global and local temporary tables, where a local temporary table has a separate set of contents for each SQL module within each session, though its definition is still shared across sessions. Since PostgreSQL does not support SQL modules, this distinction is not relevant in PostgreSQL."

    SQL Error [0A000]: ERROR: extension "pgtt" is not available

    Unfortunately, the create extension doesn't come with support of a package manager - whatever it can load, must've been bundled with your installation or added externally. You need to install pgtt and make it available to Postgres before you can enable it with create extension.

    Worried about performance with this approach.

    Test and only continue worrying if it really turns out to drag things down. If you narrowed down the subset of features of GTT you're interested in, it would be easier to discuss which approach is most suitable. By the looks of it, pgtt aims to add full emulation of GTT behaviour in Postgres, possibly removing the need to translate/port things.

    If you only need a temp table, but shared by everyone, that's an unlogged. If you need one for everybody, but each seeing only their own content, that's the same thing but with RLS. The more you keep adding, the more complicated it'll get, and the closer you move towards what pgtt does. The unlogged+RLS thing seems to be what they did in their previous version.