[sqlite] 'where ... isnull' in create index

but it is not elegant to me, TIA, ingo ---%<--%<--%<--- CREATE TABLE IF NOT EXISTS person ( pid INTEGER NOT NULL, full_name TEXT, ts_to TEXT DEFAULT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS idx_person ON person (pid, ts_to) WHERE ts_to ISNULL ; INSERT INTO person (pid,

Re: [sqlite] [EXTERNAL] 'where ... isnull' in create index

l > > It also states that NULL1 == NULL2 is TRUE in UNION and DISTINCT contices. > Thanks, ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] upsert unique partial index

clause does not match any PRIMARY KEY or UNIQUE constraint ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upsert unique partial index

On 5-6-2019 12:52, Richard Hipp wrote: > WHERE param='_' query executed, thanks, ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LSM INT key problem

r: key type should be INT, TEXT, or BLOB Using TXT or BLOB there is no error. Did I go wrong somewhere? Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] misuse of aggregate function max()

() is not deterministic, or because current_timestamp is not, or both? Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] misuse of aggregate function max()

played with is two indexes, one unique on (id, ts_from) to find the last version if eol is not null and one unique on (id, ts_eol) where eol = null to find the current active version of id. Thanks, Ingo ___ sqlite-users mailing list sqlite-users@mailinglis

Re: [sqlite] nested set tree: how to change order of one node?

Sam, Can't answer your question directly, maybe the closure extension is something for you. To read a bit about it: http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ ingo On 18-6-2019 14:19, Sam Carleton wrote: > My thou

[sqlite] json_group_array( json_object())

ot;:\"notebook\",\"id\":2,\"attributes\":{\"book\":\"brew\",\... etc the array is a string? besides manually constructing with: || json_quotes('data') || ":[" || group_concat( json_object( ) ) how should it be done properly in one

Re: [sqlite] json_group_array( json_object())

ult in strange results and not in an error. Thanks Richard, Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...

art with the diagrams and if the SQL does not work, find the exception why in the text. Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] wal

his happen and wondered while setting up SQLTools on Sublime for SQLite. It doesn't/can't create a persistent connection) Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal

Haha :) Thanks for the insight. Hadn't looked at it that way. Ingo On 28-6-2019 11:52, Warren Young wrote: > You’ve basically got it backwards. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

f advantage to put only that one table in a separate database and attach it when needed. There would be no need then to drop it, one could just detach and delete the db. Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://maili

Re: [sqlite] Upsert inside trigger?

On 16-6-2019 15:11, Adrian Ho wrote: > Common table expression are not supported for statements inside of > triggers. Ah, I searched the docs for 'upsert', 'with' ... Thanks. ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqli

[sqlite] Upsert inside trigger?

Upon creation, the trigger below gives a syntax error near INSERT. Without the trigger surrounding it, the query works well. The docs give me no clue to what goes wrong. Ingo ---%<--%<--%<--- CREATE TRIGGER IF NOT EXISTS update_balances AFTER INSERT ON journal BEGIN WITH inup(a

Re: [sqlite] New word to replace "serverless"

n "without a server". > client-serverless? although I've always thought of it as an in-proces DB-library. ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

Can we clone (push, pull, sync) the forum fossil? Ingo On 12-3-2020 21:17, Richard Hipp wrote: The Forum is powered by Fossil. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo

Re: [sqlite] Re: How to sort not binary?

Yves Goergen wrote: > I guess that doesn't work when I'm accessing the database through the > System.Data.SQLite interface in .NET? Fortunately your guess is wrong. ;-) System.Data.SQLite supports user defined collation sequences. See TestCases.cs of the source distribution for samples how to

Re: [sqlite] Re: How to sort not binary?

Yves Goergen schrieb: > On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: >> On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: >> >>> Ah, now I realised that I'd also like to have that "natural sorting", >>> meaning this: >>> >>> 2 >>> 8 >>> 9 >>> 10 >>> 11 >>> 23 >> select from from

[sqlite] Subselect question

Hi, I've got a question concerning a query with subselects. I have a table with stores pairs of events. one of the events is kind of a start event and the other one is a stop event. Each event is stored in its own row. What I'm trying to achive is to get a view which contains rows with the start

Re: [sqlite] Subselect question

t that too, but I didn't want to keep track of an additional ID in the application but instead let the database do the work. Maybe an additional table for the current eventid and a on insert trigger could do the trick without changing the applicatio

Re: [sqlite] Subselect question

f triggers, database procedures and functions). Normally, if handled internally, the database is much faster to do the adequate things than an application through an interface can do. And as a result of that this is the single point on my wish list for SQLite: Stored procedures and functions wi

Re: [sqlite] Subselect question

y hero ;-) I've tried a subselect in the join but I've missed the (somehow obvious) min(ID) part. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [PATCH] clean up sqlite3(1) manual

. * Mark up the -init option in the text. * Use the standard AUTHORS section instead of a custom AUTHOR section. Thank you for maintaining sqlite! Yours, Ingo -- Ingo Schwarze mandoc developer - http://mdocml.bsd.lv/ Index: sqlite3.1

Re: [sqlite] search time in FTS3 tables sometimes very long

Hi Scott! You're great! I checked the attached modification and found no search taking longer than 20s now! It's a great improvement. I didn't find any other problems, so I will leave the modification in my FTS3 compilation. Many thanks! Ingo Scott Hess schrieb: 2007/12/4 Scott Hess