PostgreSQL table variable

Question

Is there anything like table variables in T-SQL?
In Sql Server it looks like this:

DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

Then in procedure I can:

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
  FROM [Order Details]
  GROUP BY ProductID

And manipulate with this variable like an ordinary table.

Here is description: http://odetocode.com/Articles/365.aspx

If I good remember you can not declare a variable as TABLE, there is no type TABLE in postgresql. — cojack
– cojack, Commented May 28, 2012 at 14:07
The functionality is the same as a temporary table. It is just that the article claims the table variable performs best in SQL Server. — Clodoaldo Neto
– Clodoaldo Neto, Commented May 28, 2012 at 14:36
@ClodoaldoNeto in SQL Server there are two options for this use case: the first is to use a temp table just as in Postgres, the second is to use a table variable. There are advantages to each depending on the circumstances. — Rob_vH
– Rob_vH, Commented Sep 12, 2019 at 18:27

Hannah Vernon · Accepted Answer · 2025-05-12 15:18:42Z

50

Use a temporary table in PostgreSQL. For your example:

CREATE TEMP TABLE product_totals (
   product_id int
 , revenue money
);

From the manual for CREATE TABLE:

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). The default search_path includes the temporary schema first and so identically named existing permanent tables are not chosen for new plans while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

Temporary tables are only visible within the same database session and they are dropped at the end of the session, in all versions of Postgres. You can even have separate temp tables for the same db user when connected several times (several sessions). Internally, a separate temp schema is used for each session.

Unlogged tables in Postgres 9.1 or later are a somewhat related feature. They save disk writes by not writing to WAL. Here is a discussion of the features by Robert Haas:

Aside, concerning the money data type:

Which datatype should be used for currency?

edited May 12 at 15:18

Hannah Vernon

3,4711 gold badge34 silver badges53 bronze badges

answered May 28, 2012 at 20:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dleavitt Over a year ago

This thread suggests that pg arrays may be the equivalent of a T-SQL memory table, and that temporary tables may introduce a lot of additional overhead.

Qwertiy Over a year ago

This doesn't work if connection to db is readonly.

granadaCoder Over a year ago

DROP TABLE IF EXISTS product_totals; /* this also works if you need something more explicit than end of session auto magic :) */

Lars Blumberg · Accepted Answer · 2023-08-06 19:56:47Z

9

You can use an array of composite type instead

CREATE TABLE xx(a int, b int);

CREATE OR REPLACE FUNCTION bubu()
RETURNS void AS $$
DECLARE _x xx[];
BEGIN
   _x := ARRAY(SELECT xx FROM xx);
   RAISE NOTICE '_x=%', _x;
   ...

edited Aug 6, 2023 at 19:56

Lars Blumberg

21.8k15 gold badges101 silver badges135 bronze badges

answered Jun 9, 2012 at 7:19

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

1 Comment

uudecode Over a year ago

Indeed no:

CREATE TABLE xx(a int, b int); insert into xx values(1,2); insert into xx values(3,4); DO $$DECLARE _x xx[]; BEGIN    _x := ARRAY(SELECT * FROM xx);   RAISE DEBUG '_x is %',_x; END$$;

code` [2016-04-05 21:47:45] parse <unnamed>: DO $$DECLARE _x xx[]; BEGIN _x := ARRAY(SELECT * FROM xx); RAISE DEBUG '_x is %',_x; END$$ [2016-04-05 21:47:45] bind <unnamed> to <unnamed> [2016-04-05 21:47:45] [42601] ERROR: subquery must return only one column Где: PL/pgSQL function inline_code_block line 3 at assignment`

Collectives™ on Stack Overflow

PostgreSQL table variable

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related