values(7)

1VALUES()                         SQL Commands                         VALUES()
2
3
4

NAME

6       VALUES - compute a set of rows
7
8

SYNOPSIS

10       VALUES ( expression [, ...] ) [, ...]
11           [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
12           [ LIMIT { count | ALL } ]
13           [ OFFSET start ]
14
15

DESCRIPTION

17       VALUES  computes  a  row  value or set of row values specified by value
18       expressions. It is most commonly used to generate a ``constant  table''
19       within a larger command, but it can be used on its own.
20
21       When  more  than  one row is specified, all the rows must have the same
22       number of elements. The data types of the resulting table's columns are
23       determined  by  combining the explicit or inferred types of the expres‐
24       sions appearing in that column, using the same rules as for UNION  (see
25       in the documentation).
26
27       Within  larger  commands, VALUES is syntactically allowed anywhere that
28       SELECT is. Because it is treated like a SELECT by the  grammar,  it  is
29       possible  to  use the ORDER BY, LIMIT, and OFFSET clauses with a VALUES
30       command.
31

PARAMETERS

33       expression
34              A constant or expression to compute and insert at the  indicated
35              place  in  the  resulting  table (set of rows). In a VALUES list
36              appearing at the top level of an INSERT, an  expression  can  be
37              replaced  by  DEFAULT  to indicate that the destination column's
38              default value should be inserted. DEFAULT cannot  be  used  when
39              VALUES appears in other contexts.
40
41       sort_expression
42              An  expression  or  integer  constant indicating how to sort the
43              result rows. This expression may refer to  the  columns  of  the
44              VALUES  result  as  column1,  column2, etc. For more details see
45              ORDER BY Clause [select(7)].
46
47       operator
48              A sorting operator. For details see ORDER BY Clause [select(7)].
49
50       count  The maximum number of rows to  return.  For  details  see  LIMIT
51              Clause [select(7)].
52
53       start  The  number of rows to skip before starting to return rows.  For
54              details see LIMIT Clause [select(7)].
55

NOTES

57       VALUES lists with very large numbers of rows should be avoided, as  you
58       may  encounter  out-of-memory  failures  or  poor  performance.  VALUES
59       appearing within INSERT is a special case (because the  desired  column
60       types  are  known  from  the  INSERT's  target  table,  and need not be
61       inferred by scanning the VALUES list), so it can  handle  larger  lists
62       than are practical in other contexts.
63

EXAMPLES

65       A bare VALUES command:
66
67       VALUES (1, 'one'), (2, 'two'), (3, 'three');
68
69       This  will  return  a  table of two columns and three rows. It's effec‐
70       tively equivalent to
71
72       SELECT 1 AS column1, 'one' AS column2
73       UNION ALL
74       SELECT 2, 'two'
75       UNION ALL
76       SELECT 3, 'three';
77
78
79       More usually, VALUES is used within a larger  SQL  command.   The  most
80       common use is in INSERT:
81
82       INSERT INTO films (code, title, did, date_prod, kind)
83           VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
84
85
86       In  the  context  of INSERT, entries of a VALUES list can be DEFAULT to
87       indicate that the column default should be used here instead of  speci‐
88       fying a value:
89
90       INSERT INTO films VALUES
91           ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),
92           ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);
93
94
95       VALUES  can also be used where a sub-SELECT might be written, for exam‐
96       ple in a FROM clause:
97
98       SELECT f.*
99         FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)
100         WHERE f.studio = t.studio AND f.kind = t.kind;
101
102       UPDATE employees SET salary = salary * v.increase
103         FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)
104         WHERE employees.depno = v.depno AND employees.sales >= v.target;
105
106       Note that an AS clause is required  when  VALUES  is  used  in  a  FROM
107       clause,  just  as  is  true  for SELECT. It is not required that the AS
108       clause specify names for all the columns, but it's good practice to  do
109       so.   (The default column names for VALUES are column1, column2, etc in
110       PostgreSQL, but these names might be different in other  database  sys‐
111       tems.)
112
113       When VALUES is used in INSERT, the values are all automatically coerced
114       to the data type of the corresponding  destination  column.  When  it's
115       used in other contexts, it may be necessary to specify the correct data
116       type. If the entries are all quoted  literal  constants,  coercing  the
117       first is sufficient to determine the assumed type for all:
118
119       SELECT * FROM machines
120       WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));
121
122
123              Tip:  For  simple  IN tests, it's better to rely on the list-of-
124              scalars form of IN than to write a VALUES query as shown  above.
125              The  list  of  scalars method requires less writing and is often
126              more efficient.
127
128

COMPATIBILITY

130       VALUES conforms to the SQL standard, except that LIMIT and  OFFSET  are
131       PostgreSQL extensions.
132