VALUES

A VALUES statement is used to create one or more rows that can be used as a virtual table.

Contents

• VALUES

  • Syntax

  • VALUES

  • EXPLAIN

  • Examples

Syntax

VALUES <expression>, ...
VALUES ROW(<expression>, ...), ...

VALUES

For each expression, the names COL1, COL2, etc will be used. The names of the columns cannot be overridden directly in the VALUES statement, but rather need to be wrapped in a SELECT:

SELECT * FROM (VALUES ROW(1, 2, 3)) AS t1 (foo, bar, baz);

EXPLAIN

The query planner will uses a plan operation for the VALUES and it can be explained:

EXPLAIN VALUES ROW('hello', 1.22);
-- EXPLAIN: VALUES ROW('hello', 1.22)

EXPLAIN SELECT *
FROM (VALUES ROW(123, 'hi'), ROW(456, 'there')) AS foo (bar, baz);
-- EXPLAIN: VALUES ROW(123, 'hi'), ROW(456, 'there') AS FOO (BAR, BAZ)

Examples

VALUES ROW('cool', 12.3);
-- COL1: cool COL2: 12.3

SELECT * FROM (VALUES ROW(1, 'foo', TRUE));
-- COL1: 1 COL2: foo COL3: TRUE

SELECT * FROM (VALUES ROW(1, 'foo', TRUE)) AS t1 (abc, col2, "f");
-- ABC: 1 COL2: foo f: TRUE

SELECT * FROM (VALUES ROW(123, 'hi'), ROW(456, 'there'));
-- COL1: 123 COL2: hi
-- COL1: 456 COL2: there