[sqlite] INSERT DEFAULT literal-value

On 2015/11/24 9:20 PM, chromedout64 at yahoo.com wrote: > Thanks for the reply. It would be the case of specifying a default value > among other columns but not wishing to remove it from the inserted fields -- > not just because I'm lazy, but also to reuse that same INSERT statement for > othe

[sqlite] Dont Repeat Yourself (DRY) and SQLite

On 24 Nov 2015, at 7:09pm, Domingo Alvarez Duarte wrote: > one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use > a default here ? Include "DEFAULT 'tuple'" just like you would in PostgreSQL. Otherwise I'm with Igor. I don't see why you're using TRIGGERs and I don't see

[sqlite] INSERT DEFAULT literal-value

Backward compatibility is an understandable concern, but since "DEFAULT" is one of the 124 official SQLite keywords, you would expect it not to be used for user-defined objects or else properly quoted. http://www.sqlite.org/lang_keywords.html Additionally, adding the capability of using a "DEFAU

[sqlite] Dont Repeat Yourself (DRY) and SQLite

Actually we can not use "select"? on constraints ! Cheers ! > Tue Nov 24 2015 8:43:57 pm CET CET from "Igor Tandetnik" > Subject: Re: [sqlite] Dont Repeat Yourself (DRY) and >SQLite > > On 11/24/2015 2:09 PM, Domingo Alvarez Duarte wrote: > >>I'm trying to migrate a database from PostgreS

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Hi For a left join with a subquery on the right-hand side, that subquery doesn't seem to be flattened. This seems to work well with an inner join. I have attached a reprex. It creates two tables with $n rows and one ID column each (200k is enough to show substantial slowdown), and joins them

[sqlite] Dont Repeat Yourself (DRY) and SQLite

Hello ! I'm trying to migrate a database from PostgreSQL to SQLite but could not find a way to do it and stay DRY, is it possible to do it ? See example bellow. Cheers ! == Using literals for one_type is not an acceptable option ! one_type varchar NOT NULL DEFAULT 'tuple' C

[sqlite] INSERT DEFAULT literal-value

On 2015/11/23 11:00 PM, chromedout64 at yahoo.com wrote: > Maybe there's a technical reason that this functionality wasn't added to > SQLite. Does anyone know? I am not sure exactly what you intend with this. Do you mean to Insert into a table a row with the default values? In that case you c

[sqlite] INSERT DEFAULT literal-value

Doesn't look like the formatting worked on the last one. Just an quick example using the sqlite3 CLI:db=/tmp/test.dbsqlite3 $db "create table t(id integer primary key,ts text default (datetime('localtime','now')));"for x in "'no_date'" "'invalid_date'" "NULL" "CURRENT_TIMESTAMP" "DEFAULT"; do?

[sqlite] INSERT DEFAULT literal-value

Thanks for the reply. It would be the case of specifying a default value among other columns but not wishing to remove it from the inserted fields -- not just because I'm lazy, but also to reuse that same INSERT statement for other values. Just an quick example using the sqlite3 CLI:db=/tmp/test.

[sqlite] regular expression in check constraint?

On 11/24/2015 6:07 PM, Richard Hipp wrote: > On 11/24/15, James Hartley wrote: >> I would like to add a check constraint which determines if a string >> contains all digits, ie. >> >> sqlite> select zip_code from zip_codes where regexp(zip_code, >> '^[[:digit:]]+$'); >> >> However, this generates

[sqlite] regular expression in check constraint?

On 11/24/15, James Hartley wrote: > I would like to add a check constraint which determines if a string > contains all digits, ie. > > sqlite> select zip_code from zip_codes where regexp(zip_code, > '^[[:digit:]]+$'); > > However, this generates the error: > > Error: no such function: regexp Mayb

[sqlite] regular expression in check constraint?

I would like to add a check constraint which determines if a string contains all digits, ie. sqlite> select zip_code from zip_codes where regexp(zip_code, '^[[:digit:]]+$'); However, this generates the error: Error: no such function: regexp Searching through sqlite.org points that this function

[sqlite] FTS5 prefix index documentation may be incorrect

The FTS5 prefix index documentation[1] seems to not be working. I've tried with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error messages. Examples: sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3'); Error: malformed prefix=... directive sqlite> CREATE VIRTUAL TABLE

[sqlite] Dont Repeat Yourself (DRY) and SQLite

How Postgres stores enum values is an implementation detail that should be ignored. You always use them using string syntax, that is proper. The SQL syntax for comparisons is the same =, <, > etc for all types, there is no distinct "string comparison". See http://www.postgresql.org/docs/9.4/

[sqlite] Dont Repeat Yourself (DRY) and SQLite

On 11/24/2015 2:58 PM, Domingo Alvarez Duarte wrote: > Actually we can not use "select" on constraints ! And that's a problem because... ? Your triggers don't refer to any other table either. Color me dense, but I utterly fail to grasp the nature of the difficulty. -- Igor Tandetnik

[sqlite] Dont Repeat Yourself (DRY) and SQLite

On 11/24/2015 2:09 PM, Domingo Alvarez Duarte wrote: > I'm trying to migrate a database from PostgreSQL to SQLite but could not find > a way to do it and stay DRY, is it possible to do it ? SQLite supports CHECK constraints, just like Postrgress. Why do you feel you need triggers? > Using litera