VALUES

Synopsis

Use the VALUES statement to generate a row set specified as an explicitly written set of explicitly written tuples.

Syntax

values ::= VALUES ( expression_list ) [ ,(expression_list ... ]  
           [ ORDER BY { order_expr [ , ... ] } ]  
           [ LIMIT { int_expression | ALL } ]  
           [ OFFSET int_expression [ ROW | ROWS ] ]  
           [ FETCH { FIRST | NEXT } int_expression { ROW | ROWS } ONLY ]

expression_list ::= expression [ , ... ]

Semantics

expression_list

A comma separated list of parenthesized expression lists. The degenerate form is just a single constant, thus:

values ('dog'::text);

This is the result:

 column1
---------
 dog

The result has as many columns named "column1", "column2", ... "columnN" as there are expressions in the expression list, thus:

values
  (1::int, '2019-06-25 12:05:30'::timestamp, 'dog'::text),
  (2::int, '2020-07-30 13:10:45'::timestamp, 'cat'::text);

This is the result:

 column1 |       column2       | column3
---------+---------------------+---------
       1 | 2019-06-25 12:05:30 | dog
       2 | 2020-07-30 13:10:45 | cat

If an expression is written without a typecast, then its data type is inferred. For example, 'dog' is inferred to have datatype text and 4.2 is inferred to have data type numeric.

Each successive parenthesized expression list must specify the same number of expressions with the same data types. Try this counter example:

values
  (1::int, '2019-06-25 12:05:30'::timestamp, 'dog'::text),
  (2::int, '2020-07-30 13:10:45'::timestamp, 'cat'::text, 42::int);

It causes this error:

42601: VALUES lists must all be the same length

And try this counter example:

values (1::int), ('x'::text);

It causes this error:

42804: VALUES types integer and text cannot be matched

The ORDER BY, LIMIT, OFFSET, and FETCH clauses

These clauses have the same semantics when they are used in a VALUES statement as they do when they are used in a SELECT statement.

Example

A VALUES statement can be used as a subquery by surrounding it with parentheses, and giving this an alias, in just the same way that a SELECT statement can be so surrounded and so used. Try this first:

select chr(v) as c from (
  select * from generate_series(97, 101)
  ) as t(v);

This is the result:

 c
---
 a
 b
 c
 d
 e

Now use a VALUES statement (on line #2) within the parentheses instead of the SELECT statement:

select chr(v) as c from (
  values (100), (111), (103)
  ) as t(v);

This is the result:

 c
---
 d
 o
 g