Re: [sqlite] INSERT ... VALUES / want to "skip" default values
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
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
>
> 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
> 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
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
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
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
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
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
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
> 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
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 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
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
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
