CODEWITHSUNDEEP
sqlite
Marek Wieckowski
Marek Wieckowski

Reputation: 78

SQLite: how to name columns in a "values" subselect

In postgres I can say:

test=# select * from (values(1),(3),(7)) as foo(id);
 id
----
  1
  3
  7
(3 rows)

This means that such a subselect can subsequently be joined with other tables using foo.id etc.

In sqlite I can say:

sqlite> select * from (values(1),(3),(7)) as foo;

----------
1
3
7

but if I say foo(id) I'll get an error:

sqlite> select * from (values(1),(3),(7)) as foo (id);
Error: near "(": syntax error

Obviously with usual subselects (e.g. "(select ... as a, ... as b from... ) as foo" ) you could simply name each field.

The only simple workaround I've found to name columns in such a situation is to do an union like:

sqlite> select * from (select 1 as id where 1=0 union values(1),(3),(7)) foo;
id        
----------
1         
3         
7

Is there a better way to name columns in this kind of "subselects" in SQLite?

Upvotes: 4

Views: 390

Answers (1)

CL.
CL.

Reputation: 180080

The columns created by VALUES have names that you would not want to use (although it's possible):

sqlite> .mode columns
sqlite> .header on
sqlite> select * from (values(1,2,3));
            :1          :2        
----------  ----------  ----------
1           2           3         
sqlite> select "", ":1", ":2" from (values(1,2,3));
            :1          :2        
----------  ----------  ----------
1           2           3

This cannot be changed; there is no better way than to prefix a 'real' SELECT with UNION ALL.

Upvotes: 4

Related Questions