Lookup tables / value tables

Sometimes you need a table with a few values for use in a larger query. Using a CTE, you can create that table of lookup values and have it scoped just to the lifetime of your query:

with
my_lookup_table(id, name)
as (
  values (1, 'foo'),
         (2, 'bar'),
         (3, 'baz'))
select * from my_lookup_table;
┌────┬──────┐
│ id │ name │
├────┼──────┤
│  1 │ foo  │
│  2 │ bar  │
│  3 │ baz  │
└────┴──────┘
(3 rows)

If you need to use this lookup table for a few queries and then discard it at the end of your session, just make it a temporary table:

create temporary table t as (
select * from (
values (1, 'foo'),
       (2, 'bar'),
       (3, 'baz'))
as x (id, name));

select * from t; rollback;
┌────┬──────┐
│ id │ name │
├────┼──────┤
│  1 │ foo  │
│  2 │ bar  │
│  3 │ baz  │
└────┴──────┘
(3 rows)