Using PostgreSQL VALUES beyond INSERT statements

PostgreSQL's VALUES is most commonly used when adding multiple records at once with INSERT:

INSERT INTO users(id, name) VALUES
  (1, 'Taro'),
  (2, 'Hanako'),
  (3, 'Michael');

However, it can be used not only with INSERT, but anywhere a SELECT can be written.
This is because VALUES represents a set of rows and is treated the same as a SELECT.

For example, writing VALUES alone looks like this:

VALUES
  (1, 'Taro'),
  (2, 'Hanako'),
  (3, 'Michael');

testdb=# VALUES
testdb-#   (1, 'Taro'),
testdb-#   (2, 'Hanako'),
testdb-#   (3, 'Michael');
 column1 | column2
---------+---------
       1 | Taro
       2 | Hanako
       3 | Michael
(3 rows)

Column names are assigned as column1 column2 column3 ... as "column" + sequential number.

If you want to assign column names, you can write it in a FROM clause and use AS to name them.

SELECT
  *
FROM
  (
    VALUES
      (1, 'Taro'),
      (2, 'Hanako'),
      (3, 'Michael')
  ) AS t (id, name);

testdb=# SELECT
testdb-#   *
testdb-# FROM
testdb-#   (
testdb(#     VALUES
testdb(#       (1, 'Taro'),
testdb(#       (2, 'Hanako'),
testdb(#       (3, 'Michael')
testdb(#   ) AS t (id, name);
 id |  name
----+---------
  1 | Taro
  2 | Hanako
  3 | Michael
(3 rows)

Uses of VALUES Other Than INSERT

Joining with Other Tables as a Constant Table

When performing aggregations based on fixed values, using VALUES as a constant table can be quite effective.

The following SQL aggregates the amount from the sales table for each specified range, defining these ranges as a constant table using VALUES.

SELECT
  range.amount_from
  , range.amount_to
  , COUNT(sales) AS count
FROM
  (
    VALUES
      (   0,   99),
      ( 100,  999),
      (1000, 9999)
  ) AS range (amount_from, amount_to)
  LEFT JOIN sales
    ON(sales.amount BETWEEN range.amount_from AND range.amount_to)
GROUP BY
  range.amount_from
  , range.amount_to
ORDER BY
  range.amount_from;

testdb=# SELECT
testdb-#   range.amount_from
testdb-#   , range.amount_to
testdb-#   , COUNT(sales) AS count
testdb-# FROM
testdb-#   (
testdb(#     VALUES
testdb(#       (   0,   99),
testdb(#       ( 100,  999),
testdb(#       (1000, 9999)
testdb(#   ) AS range (amount_from, amount_to)
testdb-#   LEFT JOIN sales
testdb-#     ON(sales.amount BETWEEN range.amount_from AND range.amount_to)
testdb-# GROUP BY
testdb-#   range.amount_from
testdb-#   , range.amount_to
testdb-# ORDER BY
testdb-#   range.amount_from;
 amount_from | amount_to | count
-------------+-----------+-------
           0 |        99 |     0
         100 |       999 |     3
        1000 |      9999 |     1
(3 rows)

Testing SQL with the WITH clause

When you want to quickly test how a SQL query behaves with certain values, it's a hassle to insert data into a table every time.

In such cases, it's convenient to create a constant table using WITH + VALUES and then run your SQL against it.

WITH users(id, name) AS (
  VALUES
    (1, 'Taro'),
    (2, 'ﾊﾅｺ'),
    (3, 'Ｍｉｃｈｅｌ')  
)
SELECT
  *
  , normalize(name, NFKC)
FROM
  users;

testdb=# WITH users(id, name) AS (
testdb(#   VALUES
testdb(#     (1, 'Taro'),
testdb(#     (2, 'ﾊﾅｺ'),
testdb(#     (3, 'Ｍｉｃｈｅｌ')
testdb(# )
testdb-# SELECT
testdb-#   *
testdb-#   , normalize(name, NFKC)
testdb-# FROM
testdb-#   users;
 id |     name     | normalize
----+--------------+-----------
  1 | Taro         | Taro
  2 | ﾊﾅｺ          | ハナコ
  3 | Ｍｉｃｈｅｌ | Michel
(3 rows)

You can do the same thing by writing VALUES in the FROM clause, but personally, this way feels more natural.
Perhaps it's because the target data is listed first, followed by the SQL acting on it, or because when recalling and editing from command history, you start from the bottom, which means there's less to go back through.
*Note: I wrote this article specifically because I wanted to share this point.

Reference: PostgreSQL Documentation

• VALUES