PostgreSQL table variable

by

Use a temporary table in PostgreSQL. For your example:

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

The manual about 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.

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:

  • PostgreSQL: Which Datatype should be used for Currency?

Leave a Comment

tech