How do I name the output column in a sqlite values clause?

Question

If I run this query in sqlite3 (3.17.0)

select T.* from (values (1),(2),(3),(4),(5)) as T;

I get the below output where there is no name for the first column of T.

----------
1
2
3
4
5

How do I give the first column of T a name/alias, or alternatively, is there any way to refer to it by index somehow?

Answer 1

with cte(my_column_alias) as 
  (values (1),(2),(3),(4),(5))
select * from cte;

Answer 2

The VALUES form of a query does not have any mechanism for you to specify the column name. (The VALUES clause is intended to be used in CTEs or views where you can specify the column names elsewhere.)

As it happens, the columns returned by VALUES do have names (but they are undocumented):

sqlite> .header on
sqlite> .mode columns
sqlite> values (42);
column1
----------
42

In any case, even if that name does not survive the subquery, an empty column name is no problem at all:

select "" from (values (1),(2),(3),(4),(5));

To apply column name(s), wrap a CTE around it:

WITH T(my_column) AS (
  VALUES (1),(2),(3),(4),(5)
)
SELECT * FROM T;

or use a compound query (the WHERE 0 suppresses the row from the first query):

SELECT NULL AS my_column WHERE 0
UNION ALL
VALUES (1),(2),(3),(4),(5);