Relation Constructors

A relation constructor puts a relation directly in the FROM clause — rows written into the statement, or generated by it, rather than read from storage.

These are not temporary tables. Nothing is created, named or persisted: a constructor is part of the query's own text and exists only while that statement runs. Opteryx has no CREATE TEMPORARY TABLE. For a named relation that outlives a single statement, see CREATE VIEW; to name a constructor for reuse within one statement, wrap it in a CTE (WITH ... AS (...)).

Using VALUES

VALUES allows you to create a multi-column temporary relation where the values in the relation are explicitly defined in the statement.

A simple example is as follows:

SELECT * 
  FROM (
         VALUES ('High', 3),
                ('Medium', 2),
                ('Low', 1)
       ) AS ratings (name, rating);

Result:

 name   | rating
--------+--------
 High   |      3
 Medium |      2
 Low    |      1

Using UNNEST

UNNEST allows you to create a single column temporary relation where the values in the relation are explicitly defined in the statement.

A simple example is as follows:

SELECT *
  FROM UNNEST((1,2,3)) AS TEMP;

Result:

 unnest 
--------
      1
      2
      3

Be Aware

The values in the UNNEST function are in two sets of parenthesis. The function accepts a list of values, parenthesis is used to wrap parameters to functions and also used to define lists.

Using generate_series

generate_series allows you to create series by defining the bounds of the series, and optionally, an interval to step between values in the created series.

generate_series supports the following variations:

One parameter Example:

SELECT *
  FROM generate_series(3) AS series;

 series 
--------
      1
      2
      3

Two parameter Example:

SELECT *
  FROM generate_series(2, 4) AS series;

 series 
--------
      2
      3
      4

Three parameter NUMERIC Example:

SELECT *
  FROM generate_series(-5, 5, 5) AS series;

 series 
--------
     -5
      0
      5

Three parameter TIMESTAMP example. The bounds must be explicitly cast to a temporal type — string literals are not implicitly cast, and bare strings are rejected as a numeric series:

SELECT *
  FROM generate_series('2020-01-01'::DATE, '2025-12-31'::DATE, '1y') AS series;

           series 
------------------
 2020-01-01 00:00
 2021-01-01 00:00
 2022-01-01 00:00
 2023-01-01 00:00
 2024-01-01 00:00
 2025-01-01 00:00

The interval is a string in the notation below ('1y', '1mo', '1h30m') — not an INTERVAL literal. INTERVAL '1' YEAR in this position is rejected.

Interval Definitions

Intervals are defined quantifying one or more periods which make up the interval, supported periods and their notation are:

Recognized interval parts for the GENERATE_SERIES function are:

Where required, periods can be combined to define more complex intervals, for example 1h30m represents one hour and 30 minutes.