Re: [sqlite] CSV import using CLI (header, NULL)

To import a csv file with headers into an existing table, you can use .import '| tail -n +2 yourfile.csv' yourtable to skip the header line. On Sat, Feb 29, 2020, 4:30 AM Christof Böckler wrote: > Hi, > > I want to share some thoughts and make some suggestions about the SQLite > 3 command

Re: [sqlite] Cannot export 'sqlite3' file to CSV

Use the standard sqlite3 shell program: https://www.sqlite.org/cli.html#csv_export On Thu, Feb 27, 2020, 1:48 AM Rachael Courts wrote: > Hi All, > > I'm a couple of months into my PhD, studying bioacoustics. I am using > SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV

Re: [sqlite] Compiling on AIX OS

$tcl_patchLevel > 8.4.7 > % > > > On 26/2/20 10:19, Shawn Wagner wrote: > > puts $tcl_patchLevel > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mail

Re: [sqlite] Compiling on AIX OS

What version of tcl do you have installed? (At the tclsh prompt, do a `puts $tcl_patchLevel`) On Wed, Feb 26, 2020 at 1:05 AM Domingo Alvarez Duarte wrote: > Hello ! > > Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on AIX > 7.1 operating system for exercise and to know the

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite > > > ‐‐‐ Original Message ‐‐‐ > On Saturday, 21 December 2019 21:37, Shawn Wagner > wrote: > > > Without seeing your table definition, this is just a guess, but maybe the > > duplicate ke

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

Without seeing your table definition, this is just a guess, but maybe the duplicate keys are stored as different types, with the primary key column having an affinity that doesn't force one particular storage class: sqlite> CREATE TABLE test(id PRIMARY KEY); sqlite> INSERT INTO test

Re: [sqlite] CVE-2019-19317

Addendum: I suspect it's the one mentioned as being filed in this earlier thread: https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg117794.html On Sat, Dec 14, 2019, 2:12 PM Shawn Wagner wrote: > This appears to be a CVE pertaining to a feature that hasn't even b

Re: [sqlite] CVE-2019-19317

This appears to be a CVE pertaining to a feature that hasn't even been released yet (generated columns will be in the upcoming 3.31; they're certainly not in the referenced 3.30.1). Unless you're using the development snapshot from the download page or following trunk on fossil and haven't updated

[sqlite] Missed index opportunities with sorting?

Consider: sqlite> CREATE TABLE test1(a, b); sqlite> CREATE INDEX test1_idx ON test1(a, b ASC); sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b; QUERY PLAN `--SCAN TABLE test1 USING COVERING INDEX test1_idx sqlite> EXPLAIN QUERY PLAN SELECT * FROM test1 ORDER BY a, b DESC; QUERY PLAN

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

Does running an ANALYZE have any impact? On Fri, Dec 6, 2019 at 12:47 AM radovan5 wrote: > Also tested with latest version 3.30.1. For showing problem I need just > 2 tables (for join). > > -- cca 30.000.000 recs > CREATE TABLE ARHDAJ( >ID_ARHDAJ integer primary key autoincrement, >

Re: [sqlite] Official Github Mirror? Of the Amalgamation?

There is an official github mirror of the fossil repository: https://github.com/sqlite/sqlite The amalgamation is two files, though. When you want to upgrade a bundled sqlite, to a new version it's trivial to update them. Setting up submodules or whatever seems like massive overkill. On Mon,

Re: [sqlite] Can SQLite import Latin1 data?

15, 2019, 1:15 PM Jose Isaias Cabrera wrote: > > Shawn Wagner, on Friday, November 15, 2019 04:01 PM, wrote... > > > > If you're on Windows, which cp1252 suggests, just make sure that you > don't > > end up with a BOM at the start of the file when you convert it. Window

Re: [sqlite] Can SQLite import Latin1 data?

If you're on Windows, which cp1252 suggests, just make sure that you don't end up with a BOM at the start of the file when you convert it. Windows tools that output utf-8 are sometimes prone to add one even though it's pointless to have. On Linux etc., you can try .import '| iconv -f cp1252 -t

[sqlite] sqlite3 shell .parameter command evaluating arguments when it shouldn't.

Consider: sqlite> .parameter init sqlite> .parameter set :date '2019-11-15' sqlite> .parameter list :date 1993 How do I make it treat the value argument as a string and not as a numeric expression that gets evaluated? ___ sqlite-users mailing list

Re: [sqlite] database disk image is malformed

:57:25AM +0200, Jukka Marin wrote: > > On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote: > > > This line stood out: > > > > > > > The main process opens the databases and then forks the other > processes > > > which can then perform database

Re: [sqlite] database disk image is malformed

This line stood out: > The main process opens the databases and then forks the other processes which can then perform database operations using the already opened databases. From https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_ : > Do not open an SQLite

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

this to reliably do efficiently). On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke wrote: > On 2019-11-07 11:15, Shawn Wagner wrote: > > Does that toolchain use gcc, or a different compiler? If gcc, are you > using > > the same CFLAGS as on the redhat box (you're turning on a bunch of ext

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

with -O0 and -fno-builtin are strange unless you're planning on spending some quality time in a debugger stepping through code, and -malign-double is already the default on x86-64 so kind of pointless) On Thu, Nov 7, 2019 at 8:23 AM Dennis Clarke wrote: > On 2019-11-07 11:15, Shawn Wagner wr

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

For that matter, is the Solaris box x86 or sparc? If the latter, this particular code path obviously wouldn't ever be used on it. On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke wrote: > On 2019-11-07 11:01, Shawn Wagner wrote: > > Compiling with -std=iso9899:1999 is the culprit. Strict

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

Does that toolchain use gcc, or a different compiler? If gcc, are you using the same CFLAGS as on the redhat box (you're turning on a bunch of extra non-default options there)? On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke wrote: > On 2019-11-07 11:01, Shawn Wagner wrote: > > Compiling

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables gcc extensions like inline asm. On Thu, Nov 7, 2019, 7:57 AM Dennis Clarke wrote: > > While building sqlite from the zip file sqlite-src-3300100.zip I saw > what seems to be a normal configure and then madness : > > >

Re: [sqlite] Possible bug in storing text values in numeric columns

That's what I told the guy having the original issue to do, yes. That's not important. My concern is why a conversion that's only supposed to happen if it's lossless is in fact happening and causing data loss. On Sun, Oct 13, 2019, 4:48 PM Igor Tandetnik wrote: > On 10/13/2019 7:25 PM, Sh

Re: [sqlite] Possible bug in storing text values in numeric columns

Yes, I know. (This all comes from someone on stack overflow who used "string" as a column type and thus ran into this issue because that of course results in numeric affinity) On Sun, Oct 13, 2019, 4:27 PM Simon Slavin wrote: > On 13 Oct 2019, at 10:11pm, Shawn Wagner wrote: >

Re: [sqlite] Possible bug in storing text values in numeric columns

I wouldn't call that conversion, or any other, lossless unless it can be turned back into a string that's character for character identical with the one that was originally inserted. On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik wrote: > On 10/13/2019 5:11 PM, Shawn Wagner wr

Re: [sqlite] Possible bug in storing text values in numeric columns

3.30, though it goes back to at least 3.8.7 (the oldest version I have available to test with) On Sun, Oct 13, 2019, 3:03 PM Igor Korot wrote: > Hi, > > On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner > wrote: > > > > The documentation for a column with NUMERIC affinity

[sqlite] Possible bug in storing text values in numeric columns

The documentation for a column with NUMERIC affinity says > When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. But consider: sqlite> create table foo(bar

Re: [sqlite] [EXTERNAL] Re: Date time input

So, I have a bunch of sqlite extension modules that I really should polish up for an official release Real Soon Now... I just added a basic interface to the POSIX strptime() function to the string functions library: sqlite> .load ./libstring_funcs sqlite> select date(strptime('%m/%d/%Y',

Re: [sqlite] Comparison of incompatible types

https://www.sqlite.org/datatype3.html#comparison_expressions has the rules for how values of different types are ordered and when type conversions automatically happen. Numbers are always less than strings, so in that example you should be using round() instead of printf() as you discovered. On

[sqlite] Bug with json_group_array() as a window function

Using 3.29 and a 3.30 snapshot: When using json_group_array() as a window function, it loses the tagging of its argument as being JSON, treating JSON objects etc. as strings instead when creating arrays. Sample table: CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT); INSERT INTO testjson

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

The syntax diagrams are created by a tcl script, not drawn by hand: https://wiki.tcl-lang.org/page/Generating+Syntax+Diagrams+Using+Tk I think a lot of the documentation assumes the reader already knows the basics of sql... What kind of examples are you thinking of? On Tue, Jun 25, 2019, 8:16

Re: [sqlite] Unexpected/undocumented REPLACE() behavior

With most functions, including replace(), if any of the arguments are null, it returns null. On Mon, Jun 10, 2019 at 4:29 PM Tony Papadimitriou wrote: > Example: > > --- CUT --- > create table t(s text); > insert into t values ('1'),('null'),('3'); > > .print 'BEFORE' > select rowid,* from t; >

Re: [sqlite] Unexpected parsing of an invalid CREATE TABLE statement

Forgiving, yes, but usually not /that/ forgiving. It's certainly caused some wasted time going down the wrong path trying to debug an issue. On Mon, Jun 10, 2019 at 3:39 PM Richard Hipp wrote: > On 6/10/19, Shawn Wagner wrote: > > Consider: > > > > CREATE TABLE a(i

[sqlite] Unexpected parsing of an invalid CREATE TABLE statement

Consider: CREATE TABLE a(id INTEGER PRIMARY KEY); CREATE TABLE b(id INTEGER PRIMARY KEY); CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id, FOREIGN KEY (a_id) REFERENCES a(id) FOREIGN KEY (b_id) REFERENCES b(id)); Note the lack of comma between the two foreign key

Re: [sqlite] sqlite3_reset() bug?

If you tweak that to include some error checking: if (sqlite3_bind_int64(stmt, 1, vals[i]) != SQLITE_OK) { fprintf(stderr, "BIND ERROR: %s\n", sqlite3_errmsg(db)); } You'll see output like: BOUND 4 FOUND 1,4 BIND ERROR: bad parameter or other API misuse BOUND 5 FOUND 1,4 BIND

Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4

What is typeof(dancename)? I bet it's not TEXT. sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, dancename TEXT); sqlite> INSERT INTO test(dancename) VALUES ('Waltz'), (cast('Waltz' AS BLOB)); sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename = 'Waltz'; id dancename

Re: [sqlite] Have SQLite handle values of my own type

Don't forget IPv6 addresses. On Thu, May 23, 2019, 3:37 AM Simon Slavin wrote: > On 23 May 2019, at 3:55am, Keith Medcalf wrote: > > > Technically, COLLATE only works on TEXT. Most people declare their own > types as binary blobs and the programmer has to keep track of what is in > there and

Re: [sqlite] CSV import does not handle fields with a comma surrounded by double

Still applies; just badly worded. On Tue, May 21, 2019 at 8:47 AM Dominique Devienne wrote: > On Tue, May 21, 2019 at 5:36 PM Shawn Wagner > wrote: > > > I have a handy script that can handle that sort of input with extra > spaces > > (With the --strip option), and othe

Re: [sqlite] CSV import does not handle fields with a comma surrounded by double

I have a handy script that can handle that sort of input with extra spaces (With the --strip option), and other stuff that csv .import doesn't always deal well with: https://github.com/shawnw/useful_sqlite_extensions/tree/master/tools On Tue, May 21, 2019 at 6:14 AM Faheem Mitha wrote: > > Hi,

[sqlite] .import into databases other than main doesn't work

The .import command doesn't play well with attempts to use tables in schemas other than main: sqlite> .mode csv sqlite> .import test.csv temp.test Error: no such table: temp.test It creates the table if needed, but fails on the insert part because it tries to execute `INSERT INTO

Re: [sqlite] Improving CSV import

Late follow up: > .import "tail -n +2 foo.csv |" mytable Found out today that this works (Though the pipe character has to be the first character, not the last) and apparently has for years, though I can't find it documented anywhere. On Thu, Mar 21, 2019 at 4:06 PM Shawn

[sqlite] Feature request: FILTER on aggregate functions

I just found out that postgres (And possibly others?) supports FILTER on aggregate functions in general, not just when they're used as a window function. Trivial example: SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah which is a lot cleaner than SELECT count(*),

Re: [sqlite] Use cases for sqlite3_value_frombind()?

The commit that added it ( https://www3.sqlite.org/cgi/src/info/b3f2c3205a28dc21) says that it's used to improve fts3_tokenizer(), so maybe look at the diffs of that function to see how it's used there to get an idea for what it's intended for? On Wed, Apr 17, 2019, 9:22 AM Jens Alfke wrote: >

[sqlite] sqlite3_db_config() documentation issue

The sqlite3_db_config() function is a variable argument one, taking a database handle, a config option, and then what the documentation says is 'Subsequent arguments vary depending on the configuration verb.'. Aside from SQLITE_DBCONFIG_MAINDBNAME and SQLITE_DBCONFIG_LOOKASIDE, they all require

Re: [sqlite] Inserting the same column multiple times

On Sun, Apr 14, 2019 at 1:16 AM Luuk wrote: > > > Because, i do think, that it would never be possible to specify more > than the number of columns in an insert statement? > > The original issue was with some java/android sqlite binding that has a method that builds an insert statement on the

[sqlite] Inserting the same column multiple times

Discovered this tonight answering a question on stack overflow: sqlite> create table foo(a, b); sqlite> insert into foo(a,b,a,b) values(1,2,3,4); sqlite> select * from foo; a b -- -- 1 2 Inserting a column multiple times only uses the first corresponding

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

From the documentation (https://www.sqlite.org/lang_update.html) If a single column-name appears more than once in the list of assignment expressions, all but the rightmost occurrence is ignored. On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou wrote: > create table t(s varchar(5)); > >

Re: [sqlite] Help with sqlite3_value_text

Welcome to the wonderful world of multibyte encodings, and Unicode in particular. Unless you're looking for an ASCII character, you're looking for a substring, not a character. And you're really looking for what's called a codepoint (The entire concept of character gets kind of fuzzy with

Re: [sqlite] Does sqlite3 support alter table add primary key?

I don't think I've ever seen a decent sqlite page on tutorialspoint. Some, like this one, appear to have been written by people who have never actually used it. On Wed, Apr 10, 2019, 5:11 AM Peng Yu wrote: > Hi, > > I got the following error. Does sqlite3 support alter table add primary > key?

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

myself without any special configure arguments, and the stock Ubuntu one). I don't have a Windows version handy to test right now. It's apparently different for some reason? On Wed, Apr 10, 2019, 3:46 AM Graham Holden wrote: > Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner < > s

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

It should be pragma_function_list(). Note the parentheses. On Wed, Apr 10, 2019, 2:29 AM Luuk wrote: > > On 10-4-2019 10:28, Kees Nuyt wrote: > > On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: > > > >> I have not used extensions before. I understand that some are included > >> in the

Re: [sqlite] Can I get help with db design for SQLite use?

Yay Perl! My favorite language. DBD::SQLite is definitely one of the better, fuller featured sqlite bindings out there. Though Tom is using perl6, not perl5. I have no idea how its version compares. (At the risk of going off topic, File::Slurp has issues. File::Slurper is a better alternative.)

Re: [sqlite] read-only database in WAL mode and temporary files

Even a read only database needs to create the wal journal if it doesn't exist at the moment for a database that uses that mode: https://www.sqlite.org/wal.html#read_only_databases You might look into the immutable option mentioned there and see if it's appropriate for your needs. On Sun, Mar

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

Use single quotes, not double quotes, for strings. Double quotes are for identifiers. And you have a column named white... On Fri, Mar 22, 2019, 8:11 AM Steve Horvath wrote: > Hi, > > I found an issue with SQLite 3.27.2 and is also existent in 3.20.1. > > Of the four queries below, the second

Re: [sqlite] Improving CSV import

I thought about suggesting that, but I can see it breaking backwards compatibility with existing scripts. I set .header on in my ~/.sqliterc for example, and have things that don't change it before importing csv files and would thus miss a row. (I also have a handy perl script that does all this

[sqlite] Improving CSV import

Importing a CSV file in the command line shell has two different behaviors: if the table to import to doesn't exist, treat the first row of the file as a header with column names to use when creating the table. If the table does exist, treat all rows as data. A way to skip a header row when the

Re: [sqlite] .import fails CHECK constraint on valid data

ted before this conversion. On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden wrote: > On Sun, 10 Mar 2019 17:04:46 -0400 > "James K. Lowden" wrote: > > > Why does the .import command cause the CHECK constraint to fail, when > > an ordinary INSERT does not? > > On

Re: [sqlite] Backing up a SQLite database without the CLI

If the php sqlite bindings are incomplete and don't support the backup functions, write a small program in C that uses them to copy a database, and execute that from the php code? On Mon, Mar 18, 2019, 8:24 AM Jonathan Moules wrote: > Hi Simon, > > Thanks for your thoughts. Sorry, I should have

Re: [sqlite] difference between *.db *.db3 *.sqlie2 *.slite3

A sqlite database is just a file. You can name it whatever you want. People who like file extensions often use such ones (I assume you meant .sqlite2 and .sqlite3? The first would likely be used to indicate a sqlite 2 database, assuming you can find any software that still uses it...) On Fri, Mar

Re: [sqlite] [EXTERNAL] Missing docs

Or just boring old autocorrect shenanigans... On Fri, Mar 15, 2019, 12:20 AM Hick Gunter wrote: > Which release of sqlite are you referrring to? I am unaware of any > function call with a prefix of sqldark in release 3.24? Maybe this is an > addon/wrapper/whatever from the Sith? > >

Re: [sqlite] .import fails CHECK constraint on valid data

The check constraint is probably being evaluated (with t as a string) before any type conversion to match the column affinity is done. On Sun, Mar 10, 2019, 2:05 PM James K. Lowden wrote: > $ sqlite3 db "create table T (t integer not null);" > $ sqlite3 db "create table Tc (t integer not null >

Re: [sqlite] How to backup a memory database to a memory stream?

See https://www.sqlite.org/c3ref/serialize.html (You might have to compile a custom version of sqlite if the one you're using wasn't built with support for serialization enabled) On Thu, Feb 21, 2019, 10:58 PM heribert wrote: > Is there any way to backup a memory database directly to a memory

Re: [sqlite] LIKE operator and collations

Look into using the ICU extension. If you're compiling sqlite yourself, just define SQLITE_ENABLE_ICU to 1 (And link with the ICU libraries), otherwise you'll have to grab the source and compile it as a loadable module. https://www3.sqlite.org/cgi/src/dir?ci=03c4f00317233a34=ext/icu for details.

Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him

Are you using a new enough version of sqlite (3.25 or better) that you can use window functions? On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde Hi! > > So I have been trying to write a query that needs to be really fast (under > 1 ms would be ideal) to a very large database (around 20 000 rows and

Re: [sqlite] Tips for index creation

Some useful reading: https://use-the-index-luke.com/ https://www.sqlite.org/queryplanner.html (and the pages it links to) There's also the .expert command in the sqlite shell: sqlite> .expert sqlite> SELECT ... FROM ...; will suggest indexes that will benefit a particular query. On Wed, Feb

Re: [sqlite] Why am I not getting anything?

SELECT * FROM pragma_foreign_key_list('temp1'); The table name needs to be a string for the pragma_foreign_key_list virtual table. (It's not for the corresponding pragma statement.) On Sat, Feb 9, 2019, 11:31 PM Igor Korot Hi, ALL, > > [code] > sqlite> SELECT * FROM sqlite_master WHERE name

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

Reread the rules at the start of 3.1. TIME doesn't have any of the substrings used to indicate a particular other affinity, so it's treated as NUMERIC. On Tue, Feb 5, 2019, 8:53 AM Simon Slavin On 5 Feb 2019, at 4:11pm, David Raymond wrote: > > > "Time(8)" ends up as numeric affinity for that

Re: [sqlite] "Pickling" an in-memory SQLite database.

sqlite3_serialize() and deserialize would work: https://www.sqlite.org/c3ref/serialize.html On Thu, Jan 31, 2019, 4:47 PM Randall Smith Hi, guys. > > I have an application that allows me to embed a (potentially large) block > of data into its application file at close, and read the block back on

Re: [sqlite] Compile error with certain compile time options

Did you read all of https://www.sqlite.org/compile.html#omitfeatures ? In particular it looks like you're trying to build the amalgamation version instead of using the full source version to build a custom amalgamation file like it suggests. Plus of course even if you do that, > Some

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

Try using json_group_array(json(o)) On Tue, Jan 8, 2019, 1:50 AM Eric Grange Thanks. > > I think I may have encountered a "real" bug while ordering in a subquery. > I have simplified it in the following exemples: > > select json_group_array(o) from ( >select json_object( > 'id', sb.id

Re: [sqlite] [OT] Re: i Know i should use 'AS', but ....

Using '.mode column" in conjunction with ".headers on" you're already using makes it a lot more obvious. On Mon, Dec 24, 2018, 12:20 PM Luuk On 24-12-2018 19:21, Peter Johnson wrote: > > The headers are present in all three queries you pasted. > > > > The first result shows two rows, the top row

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

I'd start by making the employees table a normal rowid one with an INTEGER PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE column types to TEXT (or NUMERIC for the dates depending on the values they hold). Then add foreign key constraints so the relations between the

Re: [sqlite] ALTER TABLE, modifying columns

You're using a workflow that https://www.sqlite.org/lang_altertable.html explicitly calls out as incorrect and error prone... Try to create a new table, copy data over, drop the original and then rename the new one to see if that fixes the issue. On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz Dear

Re: [sqlite] Transaction commits hangs up

Do you still have the issue with a current version of the library? On Tue, Dec 11, 2018, 12:53 AM Aydin Ozgur Yagmur I have been using sqlite3.8.11 on fat32 file system. Sometimes, while > committing a transaction commit process hangs up and database became > locked. I am trying to find a cause

Re: [sqlite] Bug? Confused data entry with column name

Use single quotes, not double. Double quotes are used for identifiers, not strings, so that matches rows where id and description have the same value. On Wed, Nov 28, 2018, 1:19 AM Dale Mellor THIS VERSION > SQLite 3.25.3 2018-11-05 20:37:38 >

Re: [sqlite] sqlite3 Asynchronous I/O

By asynchronous i/o, do you mean the vfs module described here: https://www.sqlite.org/asyncvfs.html ? On Tue, Nov 27, 2018, 1:24 AM Prajeesh Prakash Hi Members, > > I am using sqlite3 Asynchronous I/O on my application. So is there any way > to get the status of the DB update (After the write

Re: [sqlite] Possible bug in Alter Table

It's waiting on a plain single quote to end the string. You have a Unicode smart quote character U+2019 (’) instead of a ' at the end before the semicolon, which doesn't count. On Mon, Nov 26, 2018, 11:19 AM Petite Abeille > > > On Nov 26, 2018, at 20:11, Richard Hipp wrote: > > > > though

Re: [sqlite] DELETE does not accept AS alias despite documentation

Works for me with 3.25.3. What version of sqlite are you using? On Thu, Nov 15, 2018, 12:05 PM Jake I know the fact that DELETE and UPDATE don't accept a table alias has come > up here a couple of years ago, but I think this should be filed as a bug > because the syntax documentation explicitly

Re: [sqlite] about upsert feature.

Show an actual insert statement that causes an error? On Wed, Nov 7, 2018, 2:22 AM 畑宏和 Hi, I have a question. > > We can use upsert after v3.24.0. > > Document says that > `INSERT INTO table-name (column-name) select-stmt upsert-clause` > But when I try

Re: [sqlite] INSERT OR REPLACE and foreign keys

That's normal. It deletes the conflicting row and inserts a new one. On Tue, Oct 23, 2018, 10:58 AM Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > Dear SQLiters, > > I am using INSERT OR REPLACE to update a table which holds a column which > servers as a foreign key. But I noticed a

Re: [sqlite] Custom serialization/deserialization of in-memory DB

https://www.sqlite.org/c3ref/serialize.html might help. On Mon, Oct 22, 2018, 11:31 AM Zoltan Demeter wrote: > Hello there, > > I am working on a small plugin for a bigger application. I am bound by > the in-house app framework, for example I cannot create arbitrary > files or use anything

Re: [sqlite] 64-column covering index limit clarification

s I would expect. > > - Deon > > > On Oct 18, 2018, at 12:34 PM, Shawn Wagner > wrote: > > > > Does a normal non-partial index make a difference in the query plan? > > > >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: > >> > >> Hi,

Re: [sqlite] 64-column covering index limit clarification

Does a normal non-partial index make a difference in the query plan? On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: > Hi, > > I seem to have run into a limit where SQLITE doesn't use an index > correctly if an indexed column is over the 64th column in the table. It's a > partial index like:

Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11

Amen. I'm about ready to unsubscribe from this list and not come back until it transitions to a forum (which is the interface I prefer anyways) because of all this nattering clogging up my inbox the last few days. I thought I was a grumpy old fart set in my ways, but I don't have anything on some

Re: [sqlite] CLI feature request: .import option -h

A more flexible approach might be to add a way to have .import run a command and use its standard output as the data source, instead of a file. sqlite> .import "!tail +2 data.csv" mytable for this example. Lets you do basic ETL stuff for any import format without having to use temporary files or

[sqlite] Possible bug in window function queries.

Been playing around with the new window functions in 3.25, and ran into something that looks like an infinite loop. Working with the t1 table many of the examples in the documentation use, I've come up with a minimal test case: sqlite> select id, b, lead(c, 1) over (order by c) as x from t1