SQL values as in-query tables

25 Feb 2016

The SQL values command can be used to create a table right inside a query, like so:

with inflation_types as (
             values ('housing', 50.0),
                    ('food',     1.1),
                    ('gas',      0.5),
                    ('clothing', 1.0))
  select p.price * it.column2 as "inflated_price",
         p.type
    from purchases as p
    join inflation_types as it 
      on p.type = it.column1
   where p.id = 1234;

Notice now the values table has no column names. In PostgreSQL, the column names for such an on-the-fly table are named "column1", "column2", etc.