Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.

But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.

When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.

Example

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

VALUES 
  (1, 'Cat'), 
  (2, 'Dog'), 
  (3, 'Bird');

Result:

 column1 | column2 
---------+---------
       1 | Cat
       2 | Dog
       3 | Bird

We can see how each row is defined within parentheses separated by a comma, and each column value is specified within the parentheses, also separated by a comma.

Note that the syntax for PostgreSQL is slightly different to some other RDBMSs, such as MySQL, which require each row to be defined with the ROW keyword (e.g. like ROW(1, 'Cat') instead of just (1, 'Cat').

The above statement is equivalent to the following:

SELECT 1 AS "column", 'Cat' AS "column2"
UNION
SELECT 2, 'Dog'
UNION
SELECT 3, 'Bird';

Result:

 column1 | column2 
---------+---------
       1 | Cat
       3 | Bird
       2 | Dog

This requires us to use the UNION clause to combine the results of multiple SELECT statements. Our VALUES example didn’t require this (although we can certainly use the UNION clause in our VALUES statements if we need to).

Sorting the Results

We can use an ORDER BY clause to sort the results:

VALUES 
  (1, 'Cat'), 
  (2, 'Dog'), 
  (3, 'Bird')
ORDER BY column2;

Result:

 column1 | column2 
---------+---------
       3 | Bird
       1 | Cat
       2 | Dog

Limiting the Results

We can use a LIMIT clause to limit the results:

VALUES 
  (1, 'Cat'), 
  (2, 'Dog'), 
  (3, 'Bird')
LIMIT 2;

Result:

 column1 | column2 
---------+---------
       1 | Cat
       2 | Dog

Offsetting the Results

We can use an OFFSET clause to offset the results:

VALUES 
  (1, 'Cat'), 
  (2, 'Dog'), 
  (3, 'Bird')
OFFSET 1;

Result:

 column1 | column2 
---------+---------
       2 | Dog
       3 | Bird

Here, I offset the result by 1, so that it started at the second row.

Naming the Columns

As seen above, PostgreSQL automatically names the columns column1, column2, column3, etc. But we can provide our own column names if we want.

We can do this by placing the VALUES statement into a subquery, and then specifying column aliases like this:

SELECT * FROM (
  VALUES 
    (1, 'Cat'), 
    (2, 'Dog'), 
    (3, 'Bird')
  ) AS t1 (id, animal);

Result:

 id | animal 
----+--------
  1 | Cat
  2 | Dog
  3 | Bird

Here I specified column aliases of id and animal, which provides more context for our data than just column1 and column2.

Note that when we put VALUES in a subquery like this, we also need to provide an alias for the table. In this case I provided an alias of t1.