VALUES

VALUES — Compute a set of rows

Synopsis

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 more commonly used to generate a "constant table" within a larger command, but it can also be used on its own.

When more than one row is specified, all rows must have the same number of elements. The column data types of the result table are determined by combining the explicit or inferred types of the expressions appearing in each column, using the same rules as for UNION.

In larger commands, VALUES is syntactically allowed wherever SELECT is allowed. Because the syntax treats it as a SELECT, an ORDER BY, LIMIT (or equivalent FETCH FIRST), and OFFSET clause can be used with a VALUES command.

Parameters

expression

A constant or expression to be computed and inserted at the corresponding position in the result table (row set). In a VALUES list appearing at the top level of an INSERT, expression can be replaced by DEFAULT to indicate that the default value of the target 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 can refer to the columns of the VALUES result using column1, column2, etc. See ORDER BY Clause in the SELECT documentation for details.

operator

A sort operator. See ORDER BY Clause in the SELECT documentation for details.

count

The maximum number of rows to return. See LIMIT Clause in the SELECT documentation for details.

start

The number of rows to skip before starting to return rows. See LIMIT Clause in the SELECT documentation for details.

Notes

VALUES lists with large numbers of rows should be avoided, as you may encounter out-of-memory failures or poor performance. VALUES appearing in INSERT is a special case (because the desired column types can be determined from the INSERT target table, and the VALUES list does not need to be scanned to infer types), so it can handle larger lists than in other contexts.

Examples

# A bare VALUES command:

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

# This returns a table with two columns and three rows. It is actually equivalent to:

SELECT 1 AS column1, 'one' AS column2

UNION ALL

SELECT 2, 'two'

UNION ALL

SELECT 3, 'three';

# More commonly, VALUES is used within a larger SQL command. It is most frequently used 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, items in a VALUES list can be DEFAULT to indicate that the default value for the column should be used 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 can 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;

# Note that when VALUES is used in a FROM clause, an AS clause is required, just as with SELECT. It is not necessary to specify names for all columns with the AS clause, but doing so is good practice (in Halo, the default column names for VALUES are column1, column2, etc., but this may differ in other database systems).

# When VALUES is used in INSERT, values are automatically coerced to the data type of the corresponding target column. When used in other contexts, it is necessary to specify the correct data type. If the items are all quoted string constants, coercing the first one is sufficient to establish the data type assumption for all items:

SELECT * FROM machines

WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'),

('192.168.1.43'));

See Also

INSERT, SELECT