Re: VALUES clause quirk or bug? (create table x (col1, col2) as select ... parse bug?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug? (create table x (col1, col2) as select ... parse bug?

Keith Medcalf
On Saturday, 8 July, 2017 19:02, Simon Slavin <[hidden email]> wrote:
> On 9 Jul 2017, at 1:44am, Keith Medcalf <[hidden email]> wrote:
 
> > I can't visualize what you mean.  Something like:

> > create view constants (col1, col2) as values (1,1), (1,2);
>
> > seems the most straightforward to me, and allows you to assign column
> > names to the data.

> But that’s not SQL syntax, is it ?  (Read that in a puzzled voice.  I
> genuinely don’t know.) You’re just using the word VALUE as if it does what
> you want.

Yes, it is valid SQL ... at least as understood by SQLite.

SQLite version 3.20.0 2017-07-07 22:47:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view constants (col1, col2) as values (1,1), (1,2);
sqlite> .header on
sqlite> select * from constants;
col1|col2
1|1
1|2
sqlite>

> I was thinking of a CREATE TABLE command, and a SELECT command with "AS"
> clauses.  Or of something similar with a VIEW.

The syntax create table ... as select ...

appears to be buggy?

sqlite> create table x (col1, col2) as select * from (values (1,2), (2,3));
Error: near "as": syntax error

however, omitting the column names appears to work, but you get the same column names as the values clause directly.

create table x as select * from (values (1,2), (2,3));

sqlite> create table x as select * from (values (1,2), (2,3));
sqlite> select * from x;
|:1
1|2
2|3

in this case however

sqlite> create table x as values (1,2), (2,3);
sqlite> select * from x;
|:1
1|2
2|3

works just as well.

It would appear that you would need two statements to build a table with column names from a values clause:

create table x (col1, col2);
insert into x values (1,2), (2,3);

I would suspect that using a table would be far more efficient then dynamically re-generating the values every time they are needed anyway.

Of course, just using the values clause directly (without the select) seems to generate different column names that are not carried forth ...

sqlite> values (1,2),(2,3);
column1|column2
1|2
2|3

sqlite> values ("1",2), (3,4);
column1|column2
1|2
3|4





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...