Ugly edge case of VALUES

In SQL, VALUES query is used to construct a “relation literal”. Using PostgreSQL, that would look like this:

VALUES ('Hello', 42), ('world', 13), ('!', 0)

But what if one would like to construct a relation with no columns but with, say, 3 rows?

VALUES (), (), ()

psql:commands.sql:1: ERROR:  syntax error at or near ")"
LINE 1: VALUES (), (), ();
                ^

… it’s syntactically incorrect.

One might wonder why such usage would ever be needed, but to that I say: relations are the core primitive of SQL. The fact that some edge case of a relation cannot be easily constructed, points to a major flaw in language design by itself. Also, this is a valid use-case:

WITH
  rooms AS (VALUES (), (), ())
SELECT 'Room ' || ROW_NUMBER() OVER () FROM rooms

Additionally, not all SQL is written by hand - in many cases we are generating it depending on some application conditions. If these conditions align just perfectly, we might also want to create relations with no columns. And for those edge cases, we need to put in some extra effort for this workaround:

SELECT FROM (VALUES (NULL), (NULL), (NULL)) _

All in all, this behavior is just another testament that SQL is not a programming language; it is a very strict natural language that databases happen to understand.

A few notes:

• MySQL (and MariaDB) use a different syntax (VALUES ROW('Hello', 42), ROW('world', 13)), which has an explicit error message for zero columns.

• In MySQL, MariaDB, and SQLite the workaround will not work, since at least one column is required in the SELECT clause.

• Where do names column1 and column2 come from? They are generated and are real column names. Read more here.

• MySQL (and MariaDB) with name the columns column_0, column_1 and so on.