Re: [sqlite] single table data collapse with constraints

Hi Robert, I'm a bit late: I missed your post in the recent tsunami (of posts)! Your question relates to one of the basic primitives of temporal databases, "coalescing". Basic but far from simple if you want to express it in pure SQL. On the contrary, the answer is much simpler if you code

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

On February 1, 2020 10:03:28 a.m. EST, Peng Yu wrote: >But it doesn't explain what is "the temp database". Is it an actual >database file? Or it is just a in-memory database? It depends. See for details ans links to further reading. -- J.

Re: [sqlite] importing data to a table that has generated-columns

On Mon, 27 Jan 2020 12:00:52 -0700 "Keith Medcalf" wrote: > If you mean importing into a pre-existing table as above that is > declared with "check typeof(a) in ('integer', 'real')" then yes. The > check constraint is run before affinity is applied rather than after > (unfortunately) ... Thank

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

On Mon, 27 Jan 2020 17:18:45 -0500 Richard Hipp wrote: > But more recently, "serverless" has become a popular buzz-word that > means "managed by my hosting provider rather than by me." Many > readers have internalized this new marketing-driven meaning for > "serverless" and are hence confused

Re: [sqlite] "Standard SQL" ?

On Thu, 30 Jan 2020 19:19:53 + Simon Slavin wrote: > Have any of you been using this term for a meaning other than "Fully > conforming to SQL:2019 (or whatever version you think current) ? Do > you have documentation somewhere ? Or are my suspicions correct and > there's no such thing ? I

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

Also these links which maybe easier to grok: https://sqlite.org/tempfiles.html (sections 2.6 and 3) https://sqlite.org/tempfiles.html#tempstore -Neal On Sat, Feb 1, 2020 at 10:09 AM J. King wrote: > On February 1, 2020 10:03:28 a.m. EST, Peng Yu

[sqlite] Shadowing a table name with a common table expression

If we create a table and then declare a common table expression of the same name, `SELECT`s seem to refer to the table, while `INSERT` targets refer to the CTE. For example: ``` CREATE TABLE foo (x); INSERT INTO foo SELECT 1; WITH foo (x) AS (SELECT 10) INSERT INTO foo SELECT x + 1 FROM foo;

Re: [sqlite] Check constrain execution timing change? (Now a bug)

Does this mean there will be no possibility to prevent inserting a string into an integer column anymore? create table x (x integer check (typeof(x) == 'integer')); insert into x values ('1'); --> will pass in future versions??? - Original Message - From: Richard Hipp To: SQLite

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

But it doesn't explain what is "the temp database". Is it an actual database file? Or it is just a in-memory database? On 2/1/20, Simon Slavin wrote: > On 1 Feb 2020, at 1:30pm, Peng Yu wrote: > >> https://www.sqlite.org/lang_createtable.html >> >> "The database in which the new table is

Re: [sqlite] Check constrain execution timing change? (Now a bug)

On 2/1/20, Thomas Kurz wrote: > Does this mean there will be no possibility to prevent inserting a string > into an integer column anymore? > > create table x (x integer check (typeof(x) == 'integer')); > insert into x values ('1'); > > --> will pass in future versions??? I think that is what it

[sqlite] Is the table created by "create temp table" in the database file or in memory?

Hi, https://www.sqlite.org/lang_createtable.html "The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database." It is not clear to me in the doc. If a table is created by "create temp table", is it temporarily a

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

On 1 Feb 2020, at 1:30pm, Peng Yu wrote: > https://www.sqlite.org/lang_createtable.html > > "The database in which the new table is created. Tables may be created in the > main database, the temp database, or in any attached database." > > It is not clear to me in the doc. If a table is