Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 29 Jun 2017, at 19:06, Jens Alfke wrote:

>> On Jun 29, 2017, at 12:13 AM, Hick Gunter  wrote:
>>
>> Double quotes is specifically for building identifiers that "look strange" 
>> (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because 
>> it tends to clutter up the statement.
>
> I agree that if you’re generating the schema by hand you should avoid 
> creating names that require quoting.
>
> However, if tables/columns/indexes are being generated dynamically, it can be 
> very convenient to name them based on the external item that uses them, and 
> that name might involve “strange” characters.

Yes, I wrote a big function that strips all non-conforming chars, words etc. 
from column names (since we use '...' and I got some errors) because we import 
CSV user data into a table where the columns are the CSV columns.

I might x-check to see if I can switch to "..." and avoid all the hassle at all.


> In my case, I create indexes on the fly based on JSON paths like 
> “address[0].zipcode”, so I use the path as part of the index name. This is 
> simpler than escaping the punctuation, using a digest of the path, or making 
> up an identifier that then has to be stored somewhere else.

Good point. We need to do the same in the future.

> It also makes the schema a lot easier to understand when looking at generated 
> statements or poking around in the sqlite3 tool.

True too.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Jens Alfke]

> On Jun 29, 2017, at 12:13 AM, Hick Gunter  wrote:
> 
> Double quotes is specifically for building identifiers that "look strange" 
> (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because it 
> tends to clutter up the statement.

I agree that if you’re generating the schema by hand you should avoid creating 
names that require quoting.

However, if tables/columns/indexes are being generated dynamically, it can be 
very convenient to name them based on the external item that uses them, and 
that name might involve “strange” characters.

In my case, I create indexes on the fly based on JSON paths like 
“address[0].zipcode”, so I use the path as part of the index name. This is 
simpler than escaping the punctuation, using a digest of the path, or making up 
an identifier that then has to be stored somewhere else. It also makes the 
schema a lot easier to understand when looking at generated statements or 
poking around in the sqlite3 tool.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 5:20 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> SQLite isn't helping the confusion in this case, because it allows 
> double-quotes to be regarded as string values IF an identifier with that name 
> doesn't exist. This is of course all good and well until you misspell a 
> column name...

Shades of REXX.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 1:22 AM, "sqlite-users on behalf of Robert M. Münch" 
 wrote:
> Hi, sorry, should have mentioned that this doesn't work in my case, because 
> we are building the column placeholders dynamically. So, we would have to 
> handle putting the necessary column names in there all the time, which is not 
> feasible.

I have been generating SQL dynamically on a number of projects over the past 
10+ years, and have found that generating INSERT with column names in is (a) 
not really that much extra work, and (b) eliminates a whole class of bugs 
involving schema changes or even schema regeneration. It’s genuinely worth 
taking the time to do it right.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[R Smith]

On 2017/06/29 8:15 AM, Robert M. Münch wrote:

On 27 Jun 2017, at 22:11, David Raymond wrote:


Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


I had this misconception at some point too.  Double quotes are for 
specifying Identifiers (The names of stuff) in SQLite so as to not 
confuse a possible column name (aka identifier) with say an internal 
keyword or a string value.


SQLite isn't helping the confusion in this case, because it allows 
double-quotes to be regarded as string values IF an identifier with that 
name doesn't exist. This is of course all good and well until you 
misspell a column name...


To demonstrate the difference and possible pitfalls:

  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE QTest(
  "ID" INT,
  Value1 TEXT,
  Value2 TEXT
);

INSERT INTO QTest(ID, Value1) VALUES
 (1, 'Gorilla Conflict')
,(2, 'Moroccan Coffee Beans')
,(3, "Monaco Raceway")
;
-- All these were regarded as strings.

UPDATE QTest SET Value2 = 'Value1' || ' in the Jungle.';

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | -
  --   1  | Value1 in the Jungle.
  --   2  | Value1 in the Jungle.
  --   3  | Value1 in the Jungle.

-- Here we expected that outcome because using single quotes means
-- that can be nothing other than the string 'Value1'


UPDATE QTest SET Value2 = "Value1" || " in the Jungle.";

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | 
  --   1  | Gorilla Conflict in the Jungle.
  --   2  | Moroccan Coffee Beans in the Jungle.
  --   3  | Monaco Raceway in the Jungle.

-- Here "value1" is correctly regarded as an identifier but
-- " in the Jungle" is regarded as a string, even though it's
-- in double-quotes. This time the weirdness helped us


UPDATE QTest SET Value2 = "Valeu1" || " in the Jungle.";

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | -
  --   1  | Valeu1 in the Jungle.
  --   2  | Valeu1 in the Jungle.
  --   3  | Valeu1 in the Jungle.

-- Here our troubles start. It's exactly the same format as before,
-- but because of the spelling mistake, our needed-to-be-an-identifier
-- value1 simply got transformed to a string, no error.
-- (In fact, this entire script runs without errors, you can copy-paste
-- it into your own DB script mechanism)


UPDATE QTest SET Value2 = Value1 || ' in the Jungle.';

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | 
  --   1  | Gorilla Conflict in the Jungle.
  --   2  | Moroccan Coffee Beans in the Jungle.
  --   3  | Monaco Raceway in the Jungle.

-- This is the most correct way to do it. The same spelling mistake
-- here would error out.

-- The only time it is needed (or really a good idea) to use double
-- quotes, is when an identifier name either contains weird
-- characters or the identifier is the same as an internal Keyword.
-- These are not valid identifiers:  *1 A~;  ,  JOIN
-- But these definitely are valid:  "*1 A~;" , "JOIN"
-- In SQLite, even this is valid: CREATE TABLE " "(" " INT);
-- Yes - that is a table with the name SPACE and a column named SPACE.



DROP TABLE QTest;-- Cleanup

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.030s
  -- Total Script Query Time: 0d 00h 00m and 
00.001s

  -- Total Database Rows Changed: 15
  -- Total Virtual-Machine Steps: 289
  -- Last executed Item Index:11
  -- Last Script Error:
  -- 




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Hick Gunter]

Double quotes is specifically for building identifiers that "look strange" 
(i.e. embedded spaces, keywords, ...) which IMHO should be avoided because it 
tends to clutter up the statement.

Single quotes is for building strings.

Integer is a keyword, "integer" is an identifier and 'integer' a string.

asql> create temp table test (id integer primary key, "integer" integer default 
'integer');
asql> .desc test
+-++++
|   Name  |Datatype|Size| Hidden |
+-++++
| id  | integer|UNKNOWN ||
| integer | integer|UNKNOWN ||
+-++++
Field count: 2
asql> insert into test (id) values (1);
rows inserted
-
1
asql> select * from test;
id  integer
--  --
1   integer

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Robert M. Münch
Gesendet: Donnerstag, 29. Juni 2017 08:16
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] INSERT ... VALUES / want to "skip" default values

On 27 Jun 2017, at 22:11, David Raymond wrote:

> Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


> So there is no method to do something like...
>
> INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

That's what I want to do.

> PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
> the default.

I tried NULL and as you said, that doesn't work because NULL is put in.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Clemens Ladisch]

Robert M. Münch wrote:
> Is this behaviour standard or a SQLite variant?

Autoincrementing is an SQLite variant.
Default values are standard SQL.

It should be noted that standard SQL (above Entry SQL level) allows
DEFAULT in row value constructors.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 28 Jun 2017, at 14:51, Simon Slavin wrote:

> Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
> were converted to the default value for a column (which is usually NULL but 
> can be overridden with a DEFAULT clause).

I had exactly the same understanding. BTW: Is this behaviour standard or a 
SQLite variant?

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 28 Jun 2017, at 9:49, Maks Verver wrote:

> I'm surprised nobody mentioned that you can specify the columns to be
> inserted in the query:
>
>   INSERT INTO test(a, c, d) VALUES (1, 2 3);
>
> (Note that `b` is missing it `a, c, d`. It will take the default value,
> which will be NULL, unless a different default was specified explicitly in
> the CREATE TABLE statement.)

Hi, sorry, should have mentioned that this doesn't work in my case, because we 
are building the column placeholders dynamically. So, we would have to handle 
putting the necessary column names in there all the time, which is not feasible.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 27 Jun 2017, at 22:24, David Raymond wrote:

> If you have to provide 4 values then the way you can use null to do that is 
> to add in a trigger to set the default, since NULL _is_ a value and _is_ 
> legal for that field.

Ha, that's a very good idea. I didn't have triggers in the radar. Great, I 
think that's solving my problem. Thanks a lot!

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 27 Jun 2017, at 22:11, David Raymond wrote:

> Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


> So there is no method to do something like...
>
> INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

That's what I want to do.

> PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
> the default.

I tried NULL and as you said, that doesn't work because NULL is put in.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Bond, Liz]

Dear all,

Please can any e-mail address that ends "@sentec.co.uk" be removed from the 
mailing list as I am receiving a number of e-mails for ex-employees but there 
is no way to unsubscribe on the e-mails.

Many thanks

With kind regards

Liz

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Maks Verver
Sent: 28 June 2017 08:50
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values

I'm surprised nobody mentioned that you can specify the columns to be inserted 
in the query:

  INSERT INTO test(a, c, d) VALUES (1, 2 3);

(Note that `b` is missing it `a, c, d`. It will take the default value, which 
will be NULL, unless a different default was specified explicitly in the CREATE 
TABLE statement.)

It's usually preferable to specify column names in an INSERT query explicitly, 
because it makes it easier to see what the values are supposed to mean. It 
prevents mistakes like swapping the meaning of two adjacent columns, or 
inserting a phone number in an email field, and things like that.

On Tue, Jun 27, 2017 at 10:24 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you have to provide 4 values then the way you can use null to do 
> that is to add in a trigger to set the default, since NULL _is_ a 
> value and _is_ legal for that field.
>
> CREATE TRIGGER test_populate_b
>   AFTER INSERT ON test
>   WHEN new.b is null
>   BEGIN
> UPDATE test
> SET b = '-'
> WHERE rowid = new.rowid;
>   END;
>
> INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');
>
> a   b   c   d
> --  --  --  --
> field a -   field c field d
>
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Tuesday, June 27, 2017 4:08 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default 
> values
>
>
>
> On 27 Jun 2017, at 8:13pm, Robert M. Münch 
> <robert.mue...@saphirion.com>
> wrote:
>
> > CREATE TABLE test(a, b DEFAULT "-", c, d)
> >
> > Now I would like to use
> >
> > INSERT VALUES(a,?,c,d)
> >
> > Where ? is something that the default value is used and not the 
> > provided
> value. Is this possible at all?
>
> You provide the text "NULL" (not in any quotes) for that value:
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')
>
> If you’ve set up a statement with parameters …
>
> INSERT INTO test VALUES(?1, ?2, ?3, ?4)
>
> … you can leave that paramater unbound (all parameters are bound to 
> NULL by default) or you can explicitly bind it to NULL using 
> sqlite3_bind_null() .
>
> Do not confuse NULL, which is the NULL value, with 'NULL' in those 
> quotes, which is a four character string.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Liz Bond, Finance Controller

Sentec Ltd  phone:  +44(0) 1223 303800
5 The Westbrook Centre  fax:+44(0) 1223 303801
Milton Road  
Cambridge   email:  eb...@sentec.co.uk
CB4 1YG, UK web:www.sentec.co.uk

This email is confidential. If you have received it in error, please notify 
Sentec Ltd UK at postmas...@sentec.co.uk immediately,
delete it from your system and note that you may not copy, distribute or use 
its contents.

Sentec Limited is registered at the above address UK Company Number 3452194.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Petite Abeille]

> On Jun 28, 2017, at 4:15 PM, R Smith  wrote:
> 
> I did ponder whether it would be a nice "feature" to use the default if both 
> a DEFAULT and a NOT NULL constraint existed on a column - but then again, 
> that will go against strict design principles and can cause a lot of 
> confusion later.

Some databases, which we shall not name, provide both options [1]:

- DEFAULT for columns without an explicit value
- DEFAULT ON NULL for columns with an explicitly null

[1] 
https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1#nulls

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Scott Robison]

On Jun 28, 2017 6:51 AM, "Simon Slavin"  wrote:



On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for
default values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs
were converted to the default value for a column (which is usually NULL but
can be overridden with a DEFAULT clause).  Thanks for the correction.


Depending on needs, one can make a column not null with on conflict
replace, at which point trying to insert or update with null will use the
default, but that is only available if your column can never be null.


Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[R Smith]

On 2017/06/28 2:51 PM, Simon Slavin wrote:



An explicit NULL works only for the autoincrement column, but not for default 
values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
were converted to the default value for a column (which is usually NULL but can 
be overridden with a DEFAULT clause).  Thanks for the correction.


if this was the case, how would you insert values which you WANT to be 
NULL into a DB?


The only time a NULL gets converted is for a Primary Key Auto-increment 
column, because those can ever be NULL (except of course in SQLite's 
case, but the exception survived for other legacy reasons).


I did ponder whether it would be a nice "feature" to use the default if 
both a DEFAULT and a NOT NULL constraint existed on a column - but then 
again, that will go against strict design principles and can cause a lot 
of confusion later.


Omitting a column from the Insert prototype or specifying DEFAULTS for 
it will do the trick in SQLite - I'm not entirely sure if this holds 
true for all other SQL DB systems, but I suppose strictly it should.


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Simon Slavin]

On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for default 
> values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
were converted to the default value for a column (which is usually NULL but can 
be overridden with a DEFAULT clause).  Thanks for the correction.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Clemens Ladisch]

Simon Slavin wrote:
> On 27 Jun 2017, at 8:13pm, Robert M. Münch  
> wrote:
>> CREATE TABLE test(a, b DEFAULT "-", c, d)
>>
>> Now I would like to use
>>
>> INSERT VALUES(a,?,c,d)
>>
>> Where ? is something that the default value is used and not the provided 
>> value. Is this possible at all?
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

An explicit NULL works only for the autoincrement column, but not for default 
values.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Maks Verver]

I'm surprised nobody mentioned that you can specify the columns to be
inserted in the query:

  INSERT INTO test(a, c, d) VALUES (1, 2 3);

(Note that `b` is missing it `a, c, d`. It will take the default value,
which will be NULL, unless a different default was specified explicitly in
the CREATE TABLE statement.)

It's usually preferable to specify column names in an INSERT query
explicitly, because it makes it easier to see what the values are supposed
to mean. It prevents mistakes like swapping the meaning of two adjacent
columns, or inserting a phone number in an email field, and things like
that.

On Tue, Jun 27, 2017 at 10:24 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you have to provide 4 values then the way you can use null to do that
> is to add in a trigger to set the default, since NULL _is_ a value and _is_
> legal for that field.
>
> CREATE TRIGGER test_populate_b
>   AFTER INSERT ON test
>   WHEN new.b is null
>   BEGIN
> UPDATE test
> SET b = '-'
> WHERE rowid = new.rowid;
>   END;
>
> INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');
>
> a   b   c   d
> --  --  --  --
> field a -   field c field d
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Tuesday, June 27, 2017 4:08 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values
>
>
>
> On 27 Jun 2017, at 8:13pm, Robert M. Münch <robert.mue...@saphirion.com>
> wrote:
>
> > CREATE TABLE test(a, b DEFAULT "-", c, d)
> >
> > Now I would like to use
> >
> > INSERT VALUES(a,?,c,d)
> >
> > Where ? is something that the default value is used and not the provided
> value. Is this possible at all?
>
> You provide the text "NULL" (not in any quotes) for that value:
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')
>
> If you’ve set up a statement with parameters …
>
> INSERT INTO test VALUES(?1, ?2, ?3, ?4)
>
> … you can leave that paramater unbound (all parameters are bound to NULL
> by default) or you can explicitly bind it to NULL using sqlite3_bind_null()
> .
>
> Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes,
> which is a four character string.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[David Raymond]

If you have to provide 4 values then the way you can use null to do that is to 
add in a trigger to set the default, since NULL _is_ a value and _is_ legal for 
that field.

CREATE TRIGGER test_populate_b
  AFTER INSERT ON test
  WHEN new.b is null
  BEGIN
UPDATE test
SET b = '-'
WHERE rowid = new.rowid;
  END;

INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');

a   b   c   d
--  --  --  --
field a -   field c field d

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, June 27, 2017 4:08 PM
To: SQLite mailing list
Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values



On 27 Jun 2017, at 8:13pm, Robert M. Münch <robert.mue...@saphirion.com> wrote:

> CREATE TABLE test(a, b DEFAULT "-", c, d)
> 
> Now I would like to use
> 
> INSERT VALUES(a,?,c,d)
> 
> Where ? is something that the default value is used and not the provided 
> value. Is this possible at all?

You provide the text "NULL" (not in any quotes) for that value:

INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

If you’ve set up a statement with parameters …

INSERT INTO test VALUES(?1, ?2, ?3, ?4)

… you can leave that paramater unbound (all parameters are bound to NULL by 
default) or you can explicitly bind it to NULL using sqlite3_bind_null() .

Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes, 
which is a four character string.

Simon.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[David Raymond]

Single quotes should be used for strings, so DEFAULT '-'

Not quite sure what you're asking. Do you mean how to insert defaults in 
general?

INSERT INTO test (a, c, d) VALUES ('field a', 'field c', 'field d');
will get you
a   b   c   d
--  --  --  --
field a -   field c field d

You need to explicitly state which fields you are providing values for, and any 
field you don't specify/provide will get the default value, either what you 
defined or null. When using VALUES or bindings there is no way to explicitly 
say "use the default for this field", you have to not provide anything and 
exclude the field from the insert.

So there is no method to do something like...

INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

There is also no way to give it 4 values and have it use only 3 of them.

Hopefully that answers what you were looking for.

PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
the default.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Robert M. Münch
Sent: Tuesday, June 27, 2017 3:13 PM
To: SQLite mailing list
Subject: [sqlite] INSERT ... VALUES / want to "skip" default values

Hi, I have a table like:

CREATE TABLE test(a, b DEFAULT "-", c, d)

Now I would like to use

INSERT VALUES(a,?,c,d)

Where ? is something that the default value is used and not the provided value. 
Is this possible at all?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Simon Slavin]

On 27 Jun 2017, at 8:13pm, Robert M. Münch  wrote:

> CREATE TABLE test(a, b DEFAULT "-", c, d)
> 
> Now I would like to use
> 
> INSERT VALUES(a,?,c,d)
> 
> Where ? is something that the default value is used and not the provided 
> value. Is this possible at all?

You provide the text "NULL" (not in any quotes) for that value:

INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

If you’ve set up a statement with parameters …

INSERT INTO test VALUES(?1, ?2, ?3, ?4)

… you can leave that paramater unbound (all parameters are bound to NULL by 
default) or you can explicitly bind it to NULL using sqlite3_bind_null() .

Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes, 
which is a four character string.

Simon.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users