Skip to main content

VALUES

Computes a set of rows.

Syntax​

VALUES ( <expression> [, ...] ) [, ...]
   [ORDER BY <sort_expression> [ ASC | DESC | USING <operator> ] [, ...] ]
   [LIMIT { <count> | ALL } ] 
   [OFFSET <start> [ ROW | ROWS ] ]
   [FETCH { FIRST | NEXT } [<count> ] { ROW | ROWS } ONLY ]

Description​

VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own.

If multiple rows are specified, they must all contain the same number of elements. The data types for the resulting table columns are decided by combining the types from the expressions in that column, following the rules used for UNION.

In larger commands, VALUES can be used wherever SELECT is allowed. Because VALUES is treated like SELECT by the grammar, you can use ORDER BY, LIMIT (or FETCH FIRST), and OFFSET clauses with a VALUES command.

Parameters​

  • <expression>

    A constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the default value of the destination column should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

  • <sort_expression>

    An expression or integer constant indicating how to sort the result rows. This expression may refer to the columns of the VALUES result as column1, column2, and so on. For more information, see "The ORDER BY Clause" in the parameters for SELECT.

  • <operator>

    A sorting operator. For more information, see "The ORDER BY Clause" in the parameters for SELECT.

  • LIMIT <count> or OFFSET <start>

    The maximum number of rows to return. For more information, see "The LIMIT Clause" in the parameters for SELECT.

Usage notes​

It is recommended that you avoid using VALUES lists with a large number of rows due to the risk of out-of-memory failures or poor performance. The use of VALUES within INSERT commands is an exception. This is because the targeted table of the INSERT provides the desired column types, eliminating the need to infer them by scanning the VALUES list. This allows it to handle larger lists than in other contexts.

Examples​

A bare VALUES command:

VALUES 
   (1, 'one'), 
   (2, 'two'), 
   (3, 'three');

This will return a table of two columns and three rows. It is effectively equivalent to:

SELECT 1 AS column1, 
    'one' AS column2
UNION ALL
SELECT 
    2, 
    'two'
UNION ALL
SELECT 
    3, 
    'three';

In most cases, VALUES is used within a larger SQL command. The most common use is in INSERT:

INSERT INTO films 
(
    code, 
    title, 
    did, 
    date_prod, 
    kind
)
VALUES 
(
    'T_601', 
    'Yojimbo', 
    106, 
    '1961-06-16', 
    'Drama'
);

In the context of INSERT, entries of a VALUES list can be DEFAULT to indicate that the column default should be used here instead of specifying a value:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 
minutes'),
    ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);

VALUES can also be used where a sub-SELECT might be written, for example in a FROM clause:

SELECT f.* 
FROM films f, 
    (
        VALUES('MGM', 'Horror'), 
        ('UA', 'Sci-Fi')
    ) AS t (studio, kind) 
WHERE f.studio = t.studio 
    AND f.kind = t.kind;
UPDATE employees 
SET salary = salary * v.increase 
FROM 
    (
        VALUES(1, 200000, 1.2), 
        (2, 400000, 1.4)
    ) AS v (depno, target, increase) 
WHERE employees.depno = v.depno 
    AND employees.sales >= v.target;

Please be aware that the AS clause is mandatory when VALUES is used in a FROM clause, similar to its usage in SELECT. Although it is not necessary for the AS clause to name all columns, it is recommended for clarity and good coding practice. In Relyt, the default column names for VALUES are column1, column2, and so on. However, these default names can vary in other database systems.

When you use VALUES in an INSERT clause, all values are automatically adapted to match the data type of the respective destination column. In other usage contexts, you may need to manually specify the correct data type. If all entries are quoted literal constants, it will be sufficient to adapt the first one, as this will set the assumed type for all subsequent entries:

SELECT * FROM machines WHERE ip_address IN 
(VALUES('192.168.0.1'::inet), ('192.168.0.10'), 
('192.0.2.43'));
note

For simple IN tests, it is better to rely on the list-of-scalars form of IN than to write a VALUES query as shown above. The method that involves a list of scalars requires less writing and is often more efficient.

SQL standard compatibility​

VALUES conforms to the SQL standard. LIMIT and OFFSET are Relyt extensions. See also under SELECT.