Re: [sqlite] Merge two sqlar files

Be aware however that the resulting sqlar table is incomplete. It does not have a "create unique index sqlar_autoindex_1 on sqlar(name)", and you may not be able to create one because there may be duplicate name entries notwithstanding that the rows are unique. To do this properly and

Re: [sqlite] Merge two sqlar files

OK. I made a mistake. UNION of multiple tables should work. On Fri, Sep 6, 2019 at 7:28 PM Peng Yu wrote: > > Hi, I think that there is no way to UNION 3 tables in one command is > it. What is the best way to merge 3 and more sqlar files? Thanks. > > On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf

Re: [sqlite] Merge two sqlar files

Hi, I think that there is no way to UNION 3 tables in one command is it. What is the best way to merge 3 and more sqlar files? Thanks. On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf wrote: > > > UNION is for "unioning" the output of two select statements (with duplicate > rows removed). It does

Re: [sqlite] Merge two sqlar files

OK. It works. But it seems that `.ar` is not necessary. import sqlite3 infile1, infile2, outfile = sys.argv[1:] conn=sqlite3.connect(outfile) c=conn.cursor() c.execute('ATTACH DATABASE ? AS d1', [infile1]) c.execute('ATTACH DATABASE ? AS d2', [infile2]) c.execute('CREATE TABLE sqlar AS SELECT *

Re: [sqlite] Merge two sqlar files

UNION is for "unioning" the output of two select statements (with duplicate rows removed). It does not matter if the tables are in the same database or even on the same planet, as long as they can be accessed on the same connection. Sqlar creates nothing more than a standard sqlite database

[sqlite] Merge two sqlar files

Hi, UNION is for tables in the same db file. Is there an easy command to combine two sqlar files? Thanks. https://www.sqlitetutorial.net/sqlite-union/ -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Why is a multiple MATCH clause not allowed in an FTS query?

> On Aug 30, 2019, at 12:43 AM, mailing lists wrote: > > SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two'); > > What is the reason that the above query is not allowed I'm curious too. This limitation is documented somewhere, but not the reason why. For programs that

Re: [sqlite] non-aggregate columns in aggregate queries

On 6 Sep 2019, at 11:40pm, Jeff Rogers wrote: > Could a pragma or build-time flag be added to enforce "standard" behavior, > i.e., raising an error rather than handling it as currently documented? I think this is one of the things mentioned in

[sqlite] non-aggregate columns in aggregate queries

Hi all, It's a documented quirk that sqlite allows the inclusion of non-aggregate, non-group by columns in an agregate query: https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause The benefits aside, it's not "standard

Re: [sqlite] Is pcre available on homebrew?

There is a standard extension for the regexp function. You have to compile it yourself, however. ext/misc/regexp.c in the source distribution. Then load the module. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

[sqlite] Is pcre available on homebrew?

I'd like to use regex. https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query But I don't find pcre. https://formulae.brew.sh/formula/sqlite Does anybody know how to make regex available for slqite3 installed by homebrew? Thanks. -- Regards, Peng

Re: [sqlite] Query for Many to Many

On 6 Sep 2019, at 7:36am, Rowan Worth wrote: > I was surprised when this behaved differently in other SQL engines. eg. in > SQLite you can write: > > SELECT col1, col2 FROM table1, table2 USING But please don't, for the reason you gave. Not only is it ambiguous but different SQL engines

Re: [sqlite] [EXTERNAL] Endless loop in update recursive query with UNION ALL

Does your "parent" relationship contain (at least one) loop(s)? UNION will break the loop by eliminating already visited rows, whereas UNION ALL will run faster precisely because it does not keep track of the visited rows. -Ursprüngliche Nachricht- Von: sqlite-users

[sqlite] Endless loop in update recursive query with UNION ALL

I have query that enters into endless loop in update recursive query with UNION ALL, but when I remove ALL from the UNION it's works fine : WITH recursive parentitems(itemid) AS ( VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a") *UNION ALL * SELECT

[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] Query for Many to Many

On Tue, 3 Sep 2019 at 22:17, Keith Medcalf wrote: > And the "," in the list of tables may be replaced by the word JOIN. It is > merely an alternate spelling. > I was surprised when this behaved differently in other SQL engines. eg. in SQLite you can write: SELECT col1, col2 FROM table1,