In PostgreSQL, the VALUES command returns a set of one or more rows as a table. It’s a table value constructor that can be used as part of a larger command, or as a standalone SQL statement.

Syntax

The official syntax goes like this:

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

Example

Here’s a simple example to demonstrate how it works:

VALUES (1, 2, 3), (4, 5, 6);

Result:

+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
|       1 |       2 |       3 |
|       4 |       5 |       6 |
+---------+---------+---------+

The resulting columns are implicitly named column1, column2, column3 and so on (although, this can be changed – see the SELECT statement option later in this article).

Each row is defined by one or more values enclosed within parentheses. When more than one row is specified, all the rows must have the same number of elements

Here’s another example:

VALUES ('Peter', 'Griffin'), ('Bart', 'Simpson');

Result:

+---------+---------+
| column1 | column2 |
+---------+---------+
| Peter   | Griffin |
| Bart    | Simpson |
+---------+---------+

The ORDER BY Clause

The syntax allows for the use of the ORDER BY clause in order to order the results.

Example:

VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) ORDER BY column1 DESC;

Result:

+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
|       7 |       8 |       9 |
|       4 |       5 |       6 |
|       1 |       2 |       3 |
+---------+---------+---------+

The LIMIT Clause

We can use the LIMIT clause to limit the number of rows that are output:

VALUES 
    ('Peter', 'Griffin'), 
    ('Homer', 'Simpson'), 
    ('Ned', 'Flanders')
LIMIT 2;

Result:

+---------+---------+
| column1 | column2 |
+---------+---------+
| Peter   | Griffin |
| Homer   | Simpson |
+---------+---------+

The OFFSET Clause

Here’s an example of using the OFFSET clause with the VALUES command:

VALUES 
    (1, 'Peter', 'Griffin'), 
    (2, 'Homer', 'Simpson'), 
    (3, 'Ned', 'Flanders'), 
    (4, 'Barney', 'Rubble'), 
    (5, 'George', 'Costanza')
OFFSET 3;

Result:

+---------+---------+----------+
| column1 | column2 | column3  |
+---------+---------+----------+
|       4 | Barney  | Rubble   |
|       5 | George  | Costanza |
+---------+---------+----------+

The FETCH Clause

Here’s an example of using the FETCH clause:

VALUES 
    (1, 'Peter', 'Griffin'), 
    (2, 'Homer', 'Simpson'), 
    (3, 'Ned', 'Flanders'), 
    (4, 'Barney', 'Rubble'), 
    (5, 'George', 'Costanza')
FETCH FIRST 3 ROWS ONLY; 

Result:

+---------+---------+----------+
| column1 | column2 | column3  |
+---------+---------+----------+
|       1 | Peter   | Griffin  |
|       2 | Homer   | Simpson  |
|       3 | Ned     | Flanders |
+---------+---------+----------+

Within a SELECT Statement

We can also use the VALUES statement within a SELECT statement, as if the VALUES table constructor were an actual table:

SELECT
   FirstName,
   LastName
FROM
   (VALUES 
        (1, 'Peter', 'Griffin'), 
        (2, 'Homer', 'Simpson'), 
        (3, 'Ned', 'Flanders')
   ) AS Idiots(IdiotId, FirstName, LastName)
WHERE IdiotId = 2;

Result:

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Homer     | Simpson  |
+-----------+----------+

Each Row Constructor Must Contain the Same Number of Values

Each row constructor in the same VALUES statement must have the same number of values in its value list.

Therefore, we can’t do this:

VALUES (1, 2), (3);

Result:

ERROR:  VALUES lists must all be the same length
LINE 1: VALUES (1, 2), (3);
                        ^

Using VALUES to Insert Data

We can use the VALUES statement in conjunction with the INSERT statement to insert data into a table.

Example:

CREATE TABLE Idiots (
    IdiotId int,
    FirstName varchar(50),
    LastName varchar(50)
    );

INSERT INTO Idiots VALUES 
    (1, 'Peter', 'Griffin'), 
    (2, 'Homer', 'Simpson'), 
    (3, 'Ned', 'Flanders');

That created a table called Idiots and inserted three rows into it.

We can now use a SELECT statement to see the new values in the table:

SELECT * FROM Idiots;

Result:

+---------+-----------+----------+
| idiotid | firstname | lastname |
+---------+-----------+----------+
|       1 | Peter     | Griffin  |
|       2 | Homer     | Simpson  |
|       3 | Ned       | Flanders |
+---------+-----------+----------+