VALUES

VALUES is a table constructor - a clause that can be used to define tabular data.

Tip

  • Use VALUES in conjunction with INSERT statements to insert a set of one or more rows.

Note

The maximum number of values in the VALUE clause is limited to 500.

Permissions

This clause requires no special permissions.

Syntax

values_expr ::=
     VALUES ( value_expr [, ... ] ) [, ... ]
    ;

Notes

Each set of comma-separated value_expr in parentheses represents a single row in the result set.

Column names of the result table are auto-generated. To rename the column names, add an AS clause.

Examples

Tabular data with VALUES

master=> VALUES (1,2,3,4), (5,6,7,8), (9,10,11,12);
1,2,3,4
5,6,7,8
9,10,11,12
3 rows

Using VALUES with a SELECT query

To use VALUES in a select query, assign a name to the VALUES clause with AS

master=> SELECT t.* FROM (VALUES (1,2,3,'a'), (5,6,7,'b'), (9,10,11,'c')) AS t;
1,2,3,a
5,6,7,b
9,10,11,c

3 rows

You can also use this to rename the columns

SELECT t.* FROM (VALUES (1,2,3,'a'), (5,6,7,'b'), (9,10,11,'c')) AS t(a,b,c,d);

Creating a table with VALUES

Use AS to assign names to columns

CREATE TABLE cool_animals AS
   (SELECT t.*
   FROM   (VALUES (1, 'dog'),
                  (2, 'cat'),
                  (3, 'horse'),
                  (4, 'hippopotamus')
           ) AS t(id, name)
   );