[sqlite] Enhance the SELECT statement?
On Mon, 17 Aug 2015 20:08:23 +0200, "R.Smith" wrote: > CORRECTION: It seems one of the two options I've mentioned earlier, > namely the CREATE TABLE AS SELECT... does not actually work on the back > of a WITH clause. The other option still do, but this request has more > appeal now. CREATE TABLE works if you swap the order of CREATE and WITH: $ cat ~/sql/test.sql .head on .echo on -- syntax error WITH RECURSIVE generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno < 10) CREATE TABLE table_of_i1 AS SELECT seqno FROM generate ORDER BY seqno; -- accepted CREATE TABLE table_of_i2 AS WITH RECURSIVE generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno < 10) SELECT seqno FROM generate ORDER BY seqno; PRAGMA table_info(table_of_i2); -- alternative syntax CREATE TABLE table_of_i3 (seqno INTEGER PRIMARY KEY NOT NULL); WITH RECURSIVE generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno < 10) INSERT INTO table_of_i3 (seqno) SELECT seqno FROM generate ORDER BY seqno; PRAGMA table_info(table_of_i3); .dump -- Regards, Cordialement, Groet, Kees Nuyt
[sqlite] Enhance the SELECT statement?
On 2015-08-17 08:15 PM, Petite Abeille wrote: >> On Aug 17, 2015, at 8:08 PM, R.Smith wrote: >> >> CORRECTION: It seems one of the two options I've mentioned earlier, namely >> the CREATE TABLE AS SELECT... does not actually work on the back of a WITH >> clause. The other option still do, but this request has more appeal now. > Hmmm?!? > > create table foo as > > with > DataSet( position ) > as > ( >select 1 as position >union all >select DataSet.position + 1 as position >fromDataSet >where DataSet.position < 10 > ) > select * > fromDataSet; > > select * from foo; Right you are, thanks for pointing it out - as long as the CTE follows the CTA and not precedes it, this script demonstrates, in case anyone was following: -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql -- Script Items: 5 Parameter Count: 0 SQLitespeed v2.1 -- 2015-08-17 20:11:50.524 | [Info] Script Initialized, Started executing... -- DROP TABLE IF EXISTS table_of_integers; CREATE TEMP TABLE table_of_integers AS WITH generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno<10 ) SELECT seqno FROM generate ORDER BY seqno; WITH RECURSIVE generate AS ( SELECT 20 AS seqno UNION ALL SELECT seqno+5 FROM generate WHERE seqno<100 ) INSERT INTO table_of_integers SELECT seqno FROM generate ORDER BY seqno; SELECT * FROM table_of_integers; -- seqno -- -- 1 -- 2 -- 3 -- 4 -- 5 -- 6 -- 7 -- 8 -- 9 -- 10 -- 20 -- 25 -- 30 -- 35 -- 40 -- 45 -- 50 -- 55 -- 60 -- 65 -- 70 -- 75 -- 80 -- 85 -- 90 -- 95 -- 100 > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Enhance the SELECT statement?
> On Aug 17, 2015, at 8:08 PM, R.Smith wrote: > > CORRECTION: It seems one of the two options I've mentioned earlier, namely > the CREATE TABLE AS SELECT... does not actually work on the back of a WITH > clause. The other option still do, but this request has more appeal now. Hmmm?!? create table foo as with DataSet( position ) as ( select 1 as position union all select DataSet.position + 1 as position fromDataSet where DataSet.position < 10 ) select * fromDataSet; select * from foo;
[sqlite] Enhance the SELECT statement?
CORRECTION: It seems one of the two options I've mentioned earlier, namely the CREATE TABLE AS SELECT... does not actually work on the back of a WITH clause. The other option still do, but this request has more appeal now. On 2015-08-17 07:24 PM, R.Smith wrote: > > > On 2015-08-17 05:44 PM, John McKown wrote: >> I use both SQLite3 and PostgreSQL. One thing that would be really useful >> for me in SQLite3 which is in PostgreSQL is the INTO phrase. The >> PostgreSQL >> documentation this is here: >> http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > Hi John, firstly I agree that it is useful, but you do know it is > already possible with two other SQLite operations - namely, if the > table already exists, you can just do the INTO like this: > > WITH RECURSIVE generate AS >( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno INSERT INTO table_of_integers SELECT seqno FROM generate ORDER BY seqno; > > > and, secondly, if the table does not exist yet, you can do this: > > WITH RECURSIVE generate AS >( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno CREATE TABLE table_of_integers AS SELECT seqno FROM generate ORDER BY > seqno; > > > I realize that you probably know these methods well and have some kind > of reason to prefer the INTO done as per your example - and, If it > offers something more than the above statements, I (for one) would > like to know the detail of it if you don't mind sharing. > > Thanks! > Ryan > > >> >> The main reason that I could use this would be in conjunction with >> the WITH >> clause. A minor example would be: >> >> WITH RECURSIVE generate AS >> ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE >> seqno> SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; >> >> This could also solve the "problem" that another person had, wanting a >> sorted sequence of random numbers: >> >> WITH RECURSIVE generate AS >> ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE >> seqno<), >> random_number_generator >> ( SELECT RANDOM() AS random_number FROM generate) >> SELECT random_number FROM random_number_generator ORDER BY >> random_number; >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Enhance the SELECT statement?
> On Aug 17, 2015, at 5:44 PM, John McKown > wrote: > > One thing that would be really useful for me in SQLite3 which is in > PostgreSQL is the INTO phrase. CTAS? AKA 'create table as'?
[sqlite] Enhance the SELECT statement?
On 2015-08-17 05:44 PM, John McKown wrote: > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html Hi John, firstly I agree that it is useful, but you do know it is already possible with two other SQLite operations - namely, if the table already exists, you can just do the INTO like this: WITH RECURSIVE generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno > The main reason that I could use this would be in conjunction with the WITH > clause. A minor example would be: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; > > This could also solve the "problem" that another person had, wanting a > sorted sequence of random numbers: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<), > random_number_generator > ( SELECT RANDOM() AS random_number FROM generate) > SELECT random_number FROM random_number_generator ORDER BY random_number; >
[sqlite] Enhance the SELECT statement?
Hello ! The query you mention is not the same as: INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; Cheers ! > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > Subject: [sqlite] Enhance the SELECT >statement? > > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > The main reason that I could use this would be in conjunction with the WITH > clause. A minor example would be: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; > > This could also solve the "problem" that another person had, wanting a > sorted sequence of random numbers: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<), > random_number_generator > ( SELECT RANDOM() AS random_number FROM generate) > SELECT random_number FROM random_number_generator ORDER BY random_number; > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Enhance the SELECT statement?
On 2015-08-17 11:29 AM, John McKown wrote: > ?WONDERFUL! That just never entered my poor head. I guess that I was too > used to the way I do it in PostgreSQL. The way things are going for me > today, I feel that I'm in the lead role of "Moron Monday".? You know, PostgreSQL supports the same INSERT...SELECT syntax that SQLite does, and in any case that is the idiomatic way to do this operation. http://www.postgresql.org/docs/9.4/static/sql-insert.html So you can do it that way with both. The whole SELECT...INTO thing is more of a relic that should not be used in modern days even when supported, unless its the only option. AFAIK, INSERT...INTO was only ever for use within a SQL stored procedure for assigning to a lexical variable. At least I've never seen it used the way you introduced. -- Darren Duncan
[sqlite] Enhance the SELECT statement?
On Mon, Aug 17, 2015 at 1:15 PM, Petite Abeille wrote: > > > On Aug 17, 2015, at 8:08 PM, R.Smith wrote: > > > > CORRECTION: It seems one of the two options I've mentioned earlier, > namely the CREATE TABLE AS SELECT... does not actually work on the back of > a WITH clause. The other option still do, but this request has more appeal > now. > > Hmmm?!? > > create table foo as > > with > DataSet( position ) > as > ( > select 1 as position > union all > select DataSet.position + 1 as position > fromDataSet > where DataSet.position < 10 > ) > select * > fromDataSet; > > ?WONDERFUL! That just never entered my poor head. I guess that I was too used to the way I do it in PostgreSQL. The way things are going for me today, I feel that I'm in the lead role of "Moron Monday".? -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Enhance the SELECT statement?
On Mon, Aug 17, 2015 at 11:41 AM, sqlite-mail wrote: > Hello ! > > The query you mention is not the same as: > > INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; > ?Thanks for that. When I read the SQLite documentation, I somehow assumed that I _had_ to do a SELECT startement, not an INSERT INTO. I finally had some time to test and this works. ? > > Cheers ! > > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > > Subject: [sqlite] Enhance the SELECT > >statement? > > > > I use both SQLite3 and PostgreSQL. One thing that would be really useful > > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The > PostgreSQL > > documentation this is here: > > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > > > The main reason that I could use this would be in conjunction with the > WITH > > clause. A minor example would be: > > > > WITH RECURSIVE generate AS > > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > > seqno > SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; > > > > This could also solve the "problem" that another person had, wanting a > > sorted sequence of random numbers: > > > > WITH RECURSIVE generate AS > > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > > seqno<), > > random_number_generator > > ( SELECT RANDOM() AS random_number FROM generate) > > SELECT random_number FROM random_number_generator ORDER BY random_number; > > > > -- > > > > Schrodinger's backup: The condition of any backup is unknown until a > > restore is attempted. > > > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will > be. > > > > He's about as useful as a wax frying pan. > > > > 10 to the 12th power microphones = 1 Megaphone > > > > Maranatha! <>< > > John McKown > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Enhance the SELECT statement?
I use both SQLite3 and PostgreSQL. One thing that would be really useful for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL documentation this is here: http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html The main reason that I could use this would be in conjunction with the WITH clause. A minor example would be: WITH RECURSIVE generate AS ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE seqno), random_number_generator ( SELECT RANDOM() AS random_number FROM generate) SELECT random_number FROM random_number_generator ORDER BY random_number; -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
