A couple of suggestions (I don’t know if they have come up before):
1. I often update the same table (e.g., a phone directory table) in various
independent databases and it’s simpler to cascade the changes by doing this:
sql a.db “.mode insert table” “select * from table” | sql b.db
than with t
create table t(s varchar(5));
insert into t values('US'),('USA');
update t set s = replace(s, 'USA', '___'),
s = replace(s,'US','USA'),
s = replace(s,'___','USA');
select * from t;
-- Expected answer:
-- USA
-- USA
--
--
I know this, thanks. I simply made a test case that can be run in MySQL,
Postgreq and SQLite3.
-Original Message-
From: Chris Locke
create table t(s varchar(5));
Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This
True, but SQLite3 is known to provide several conveniences that are not
necessarily standard SQL.
-Original Message-
From: Simon Slavin
Sent: Friday, April 12, 2019 7:11 PM
On 12 Apr 2019, at 5:00pm, Tony Papadimitriou wrote:
update t set s = replace(s,
My SQLite3 repo does not update to the latest. It stopped at 2019-04-17
[a3ab588329]
c:\sqlite3>fossil up
---
checkout: 03f2e78899fad99b0a0951b3a408268276954d4c 2019-04-17 21:12:05 UTC
tags: trunk
comment:
Oops! autonsync somehow was off. Thanks and apologies for noise.
-Original Message-
From: Richard Hipp
Sent: Saturday, April 20, 2019 3:46 PM
To: SQLite mailing list
Subject: Re: [sqlite] Can't update repo
On 4/20/19, Tony Papadimitriou wrote:
My SQLite3 repo does not u
It’s quite often (for me, at least) the case I need to do something like this
from the command line:
>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text copied
>from some other app’)
The problem is the multi-line text cannot be copy-pasted directly into the
command line as
27;));
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Tony Papadimitriou
Sent: Friday, May 17, 2019 12:34 PM
To: SQLite mailing list
Subject: [sqlite] readfile() enhancement request
It’s quite often (for me, at least) the case I need to
Can someone explain the behavioral differences of the following shell commands?
.backup ?DB? FILEBackup DB (default "main") to FILE
.clone NEWDB Clone data into NEWDB from the existing database
.save FILE Write in-memory database into FILE
They all seem to create
Is it possible to change the delimiter with the CSV virtual table extension?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Example:
--- CUT ---
create table t(s text);
insert into t values ('1'),('null'),('3');
.print 'BEFORE'
select rowid,* from t;
update t set s = replace(s,'null',null)
--where s = 'null' --adding this works of course but that’s not my point
;
.print 'AFTER'
select rowid,* from t;
--- CUT ---
Th
An example to reproduce a problem I noticed:
create table tab(val);
insert into tab values(1);
with t(rowid,val) as (
select rowid,val from tab
union
select rowid,tab.val
from tab join t on t.rowid = tab.rowid
--from tab join t using(
Heaven says a
lot about anticipated traffic volume.
-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou
Sent: Sunday, 12 November, 2017 11:21
To: General Discussion of SQLite Database
Subject: [sqlite] Is this error ex
is the
correct approach).
-Original Message-
From: Tony Papadimitriou
It's actually even simpler to show this (without CTE defining a rowid):
create table t1(v); insert into t1 values(12345);
create table t2(v); insert into t2 values(54321);
select * from t1 join t2 using(
Strike the last, my bad. It works correctly. I forgot to define the id
part. So, just inconsistent.
select * from (values(1,12345)) join (values(1,54321)) using(column1);
-Original Message-
From: Tony Papadimitriou
Sent: Sunday, November 12, 2017 10:37 PM
To: SQLite mailing list
Possible something like this may work:
create table fruit(id integer primary key, name text);
insert into fruit values
(1,'apple'),
(2,'pear'),
(3,'kiwi');
select * from fruit order by id;
begin;
update fruit set id = -id where id > 1;
update fruit set id = 1-id where id < 0;
end;
insert into
I don’t know if this has come up before.
Example:
select cast((julianday('now')-julianday(dob))/365.25 as int) age,
87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy
(... assuming 87 year average life span)
This works, but as you see the age calculation has to be r
I’ve noticed this (very annoying) behavior:
select column1*(24/100) wrong from (values(100));
Removing the parentheses yields the correct result:
select column1*24/100 correct from (values(100));
This obviously behaves like integer math is used and (24/100) gets truncated to
zero.
If I add a
-Original Message-
From: J. King
Someone please correct me if I'm wrong, but I believe it's mandated by the
SQL standard that integer division is used when both operands are integers.
I really don't know what the standard says, but here are two different
opinions in implementation.
A couple of questions about printf
1. Does it work with UTF-8? If so, how?
2. Does it understand \n and \t? I put actual line breaks inside the string
which is OK if run from script file but it won’t work with one-liners on the
command-line.
Thank you
_
-Original Message-
From: R Smith
On 2017/12/19 8:37 PM, Tony Papadimitriou wrote:
A couple of questions about printf
1. Does it work with UTF-8? If so, how?
- Yes.
- Very nicely.
I'm using SQL v3.21 and UTF-8 does not work correctly. (Not from the
command line.)
I tried
Great! Didn't think of the char() function at all. (Although I would prefer
a platform independent \n)
Thanks.
-Original Message-
From: Keith Medcalf
So, is there any way to advance to next line from a command line
printf()?
sqlite> select printf('%s%s%s', 'line 1', char(10), 'li
(SQLite v3.21.0 with FTS5 enabled)
If instead of ‘’ (empty string) one uses NULL for the 5th parameter in the
snippet() function it consistently crashes.
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sql
Is there some way to get a list of all available pragma options from the
SQLite3 shell?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter
(VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument
On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,
MySQL does not seem to have a problem with it.
-Original Message-
From: Scott Robison
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from
sqlite3_analyzer.c(207289): fatal error C1083: Cannot open include file:
'tcl.h': No such file or directory
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
You can use .output file and .print for most cases (except those that need a
calculated result) to simplify your script a bit.
-Original Message-
From: Shane Dev
I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then
I reported this same issue in May 2017
(http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
I too consider this a problem (diffing dumps is one good reason why) but,
unfortunately, this was an intentional change by this check-in:
[7359fcac] Increase the num
If it helps, I can reproduce with the mentioned binary on Win7 but I cannot
with my own compiled version (using MSVC).
-Original Message-
From: Ralf Junker
On 23.01.2018 15:31, Richard Hipp wrote:
I'm still unable to reproduce this problem.
sqlite3.exe from this ZIP:
https://ww
You're probably right.
You can try this shorter schema and get the same problem.
create table xxx("" text);
However, my point was that if SQLite3 allows it, SQLDIFF should be able to
handle it also.
Thanks.
-Original Message-
From: Tim Streater
Sent: Monday, November 30, 2015 12:41 P
BTW, the following
with xxx(current_date,date) as (
select '2000-01-01',current_date
)
select *,current_date,date from xxx;
outputs:
current_date datecurrent_date date
-- --
2000-01-012016-02-10 2016-02-102016-02-10
As
Is it possible for .DUMP to produce table/field names quoted with ` (backquote)
instead of ? (double quote) for compatibility with MySQL?
Or is this already adjustable by some setting I missed?
Thank you.
Windows!
-Original Message-
From: Richard Hipp
Sent: Friday, May 06, 2016 1:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP output compatibility
On 5/5/16, Tony Papadimitriou wrote:
> Is it possible for .DUMP to produce table/field names quoted with `
> (backquote) inst
>> > 1. Why SQLite is popular.
>>
>> The answers to those question mentioned in the podcast may be good ones
>> but I
>> think the main reason is that it's free. Completely, unmistakably, free.
>
>Necessary but not sufficient.
>It's free, and the license is as non-restrictive as it is possible to
Well, I got different results (so maybe it's version related). I tried it
on MySQL v5.7.16-log:
++--+
| id | v|
++--+
| 2 | two |
| 10 | one |
++--+
++--+
| id | v|
++--+
| 10 | one |
| 40 | two |
++--+
++---+
| id | v |
+-
Here's one possibility (simplified table for example):
create table desktops(
indexno integer not null unique,
nametextnot null primary key
);
insert into desktops values
(1,'CompA'),
(2,'CompB'),
-- we want to insert new record here bumping all above by one
(3,'CompD'),
(4,'CompE');
The following dump cannot be used to rebuild the database because the column
name is not properly quoted giving an error.
To reproduce:
sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
sql .dump xxx.db | sql
Error: near line 4: near "zone": syntax error
_
-Original Message-
From: Josh Hunsaker
On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
On 4/7/17, Tony Papadimitriou wrote:
sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
sql .dump xxx.db | sql
I'm unable to rep
qlite.org]
On Behalf Of Tony Papadimitriou
Sent: Friday, 7 April, 2017 17:04
To: SQLite mailing list
Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0
-Original Message-
From: Josh Hunsaker
>On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
>> On 4/7/17, Tony Papadim
-Original Message-
From: Simon Slavin
I notice that the command on those lines is "sql" rather than the "sqlite3"
I would expect. Tony, do you get the same error if you type those things
into the SQLite3 shell, rather than feeding them in using >your command
shell ? It works fine
-Original Message-
From: no...@null.net
What would be useful (at least via the shell CLI) is a "list_pragmas"
pragma that shows for example something like this:
sqlite> PRAGMA list_pragmas;
PRAGMA list;
would be less redundant.
___
sqli
LIKE is case insensitive, while = is not.
-Original Message-
From: lizhu...@whaty.com
when I query :
select * from downloadVideo_table where sectionId =
'402814a34b823b23014bfc228fe9588c'
then I query the length of 'sectionId', it is 32.that is right.
select length(sectionId) from d
While trying to search/replace some text from an SQLite3 dump I noticed that,
unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT
on the same values.
I know all about expected floating point inaccuracies, but I don’t see why it
should matter in this case as we have tw
Apparently, this was an intentional change by this check-in :
[7359fcac] Increase the number of significant digits in floating point literals
on ".dump" output from the shell.
I don't know what problem this change actually solved (as in my case it
introduced one) but for my own copy I changed %
Does this also affect FOSSIL, or is auto vacuum never used there?
-Original Message-
From: D. Richard Hipp
Sent: Thursday, June 08, 2017 6:57 PM
To: sqlite-annou...@mailinglists.sqlite.org ;
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Version 3.19.3 containing an important
When I give the command FOSSIL TIM it lists all changes in all branches (and
tags).
But what if I want to see changes in only, say, the TRUNK branch? Is there
a way? Or maybe items having a given tag?
TIA
___
sqlite-users mailing list
sqlite-us
Oops! That was meant for the fossil group, sorry!
-Original Message-
From: Tony Papadimitriou
Sent: Thursday, March 27, 2014 7:26 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Is it possible to see timeline for given branch (or
eventag)?
When I give the command
Not possible directly from SQLite.
Some weeks ago I suggested a possible way to have this capability added to
the shell version of SQLite3 but there seems to be zero interest from the
developers. So, don't hold your breath. Better yet, write a Lua script or
something to do it. (If you do, m
Unless I'm misusing it, I believe the shell readfile() function reads a file
as text, not binary. I haven't checked writefile() for the same problem on
writing.
This is how I use it, is this not correct?
create table t1(a blob);
insert into t1 values(readfile('binfile'));
select * from t1;
My recommendation is to dump the SQLite database into a corresponding .sql
file, and then compress that ASCII file using your favorite compressor (ZIP,
RAR, etc.)
In my view, this method has two advantages over copying the actual binary
file for backup purposes, but no obvious disadvantages.
Hi all,
Is there any an equivalent function to the MySQL
IF(condition,true_expr,false_expr) function?
For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT"));
If not, please add to wish list :)
TIA
___
sqlite-users mailing list
s
#x27; ELSE 'Adult' END
On 2014/10/07 11:15, Tony Papadimitriou wrote:
Hi all,
Is there any an equivalent function to the MySQL
IF(condition,true_expr,false_expr) function?
For example, SELECT AGE,IF(AGE < 3,"BABY",IF(
QLite Database
Subject: Re: [sqlite] Is there equivalent to MySQL IF() function?
On Tue, Oct 7, 2014 at 1:13 PM, Tony Papadimitriou wrote:
> As you can see, the second select gives unexpected output, and according
> to the syntax diagram it's not supposed to be a valid variation of the
OK, I think I managed to figure out what it means by reading a little more
about the two CASE cases.
'CASE expr WHEN' compares the base expr with the WHEN expr, whereas
'CASE WHEN' compares the WHEN expr to true
So, the difference is more than just how many times 'x' is evaluated.
(Maybe the d
Well, it is exactly because I understand the difference between a boolean
expression and a non-boolean expression, along with a bit misleading
documentation, that I got confused.
It is usually those who are used to only the C-like treatment of a boolean
result as being equivalent to an integer
Totally agree. And this is exactly why my natural first reaction was to
expect a syntax error, because in my mind you can't compare a non-boolean
expr to a boolean expr.
I need to start C-ing everything differently. Not easy... :)
Thank you (and I didn't imply there was malice, ... just a po
Is there a function (or method), e.g., row(), to return the sequence number
of the selected row? This is not the same as ROWID. row() should give a
sequence number always starting from 1 up the to the number of rows returned
by the view/select etc.
If not, then please add to the wish list.
n of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT
therowid)
I can't wait to try
order by row_number desc
On 1 Jul 2013, at 10:33, Tony Papadimitriou wrote:
Is there a function (or method), e.g., row(), to return the sequence
number of the selected r
Please! Just because you can select something doesn't mean you have to be
able to sort by it. Can you sort by *
(select * by table sort by *)? So, why make it sound like I don't know what
I'm talking about?
-Original Message-
From: Alex Bowden
Sent: Monday, July 01, 2013 2:07 PM
To
Thanks! (At least some understand what I mean!)
And people giving examples of how it can be done in C (or Python, for me) or
whatever language miss the point. We're not talking how it can be
programmatically. This is easy!!! How does one do it via pure SQL is the
real question.
As for th
I agree with you completely! It has to be relatively simple to implement.
And certainly it adds a very important missing functionality (for those who
can see it, that is).
Some kind of internal cursor has to iterate to return rows to the user after
the query is completely executed. This can ad
OK, you don't agree. Your opinion! (That doesn't make you right, though!)
I'm sure there will be a SQL engine somewhere that will do it for you.
We're talking about SQLite here, aren't we? If some other database can do
it, then you should also consider that it may also be able to do what t
With the SQL query at the end of the dump like you say, try this:
SQLite3 -init dump.sql
Then, no matter how many times you repeat the query from within SQLite3
prompt, it works!
As soon as you do ANALYZE, the query stops working...
Maybe this helps pin point the problem.
(Replacing " SD_MA
This is documented, I think.
Based on 2.1 (bullet 5) of http://www.sqlite.org/datatype3.html the default
affinity is numeric. Since STRING is not understood, it has numeric
affinity.
-Original Message-
The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL
How about:
maybe(COLUMN LIKE '%pattern%',.95)
or (as percent using integer value in 0..100)
maybe(COLUMN LIKE '%pattern%',95)
with a default value of (possibly) 50% (or .5) for the optional second arg?
-Original Message-
From: Richard Hipp
Sent: Tuesday, September 10, 2013 10:26 PM
See the REPLACE(x,y,z) function. Use UPDATE table SET field =
REPLACE(field,)
-Original Message-
From: john white
Sent: Monday, September 16, 2013 1:30 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] quetion on editing a sqlite database
I have a bit of a problem. I am moving a
A "temp" view, however, can access table from different DBs.
-Original Message-
From: Gerry Snyder
I was under the impression that a view is limited to the tables in the
db file where it resides, and have received error messages whenever I
tried to access another file's tables.
___
One obvious shortcut would be to factor out the common part of the select
using WITH
(Hopefully accurate) example:
with t as (
select GroupName, JobName, Start, End, Status, (strftime('%s', End) -
strftime('%s', Start)) as Length from ReportJobs where PlanDate =
'2014-02-13'
)
select * fro
I forgot to also factor out the ORDER BY. So, the updated query is:
-
with t as (
select GroupName, JobName, Start, End, Status,
(strftime('%s', End) - strftime('%s', Start)) as Length
from ReportJobs
where PlanDate = '2014-02-13'
order by Length desc
)
sel
OK, if everyone if putting down their wish list for 2015, here's mine:
Using the SQLite shell is very convenient for quick retrieval or
modification of data any SQLite database but there are two significant
problems (in my view) that make this of very limited utility, and this
proposed featur
From: Nelson, Erik - 2
Sent: Tuesday, December 23, 2014 5:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Whish List for 2015
Tony Papadimitriou Sent on Tuesday, December 23, 2014 10:26 AM
Problem 1: Currently, any scripts have to be stored outside the database
in separate
I tried your example.
'.schema' and '.fullschema' do show the problem you describe.
However, for me, '.dump' worked correctly placing the semicolon on a line by
itself right after the comment.
- Original Message -
From: "sqlite-mail"
To:
Sent: Wednesday, August 12, 2015 1:14 AM
Subje
Recently CTRL-C was improved to abort the query and stay in the CLI. This is
very good.
However, if (accidentally, e.g., key bounce) a second CTRL-C is entered, it
will escape back to the console.
Could it be changed so that either:
1. Only CTRL-D (Linux) or CTRL-Z (Windows) is used to exit t
Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and
each database to determine which one produces the smallest file, is there some
quicker way?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://
From: David Raymond
Sent: Wednesday, July 31, 2019 10:48 PM
To: SQLite mailing list
Subject: Re: [sqlite] Quick way to determine optimal page size?
Not that I'm aware of no. How much of a difference are you seeing for your
database size depending on the page size you try?
-Original M
Consider this:
.print Issue with generated columns
create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;
create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;
delete from a;
insert into a select * from b
Consider this:
.print Issue with generated columns
create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;
create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;
delete from a;
insert into a select * from b
From here: https://www.sqlite.org/faq.html#q5
> (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round
> up?
This example seems to be no longer valid. Although the explanation is still
valid in general, the particular example “SELECT ROUND(9.95,1)” actually
returns 10.0
80 matches
Mail list logo