lundi 9 février 2015

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 found was to do an union like:



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


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


Aucun commentaire:

Enregistrer un commentaire