INSERT ... VALUES / want to "skip" default values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
22 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

INSERT ... VALUES / want to "skip" default values

Robert M. Münch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3


On 27 Jun 2017, at 8:13pm, Robert M. Münch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

David Raymond
In reply to this post by Robert M. Münch
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

David Raymond
In reply to this post by Simon Slavin-3
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:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
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:[hidden email]]
> 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 <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]] On Behalf Of Maks Verver
Sent: 28 June 2017 08:50
To: SQLite mailing list <[hidden email]>
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 <[hidden email]>
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:[hidden email]]
> 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
> <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
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:  [hidden email]
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 [hidden email] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Clemens Ladisch
In reply to this post by Simon Slavin-3
Simon Slavin wrote:

> On 27 Jun 2017, at 8:13pm, Robert M. Münch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3


On 28 Jun 2017, at 9:45am, Clemens Ladisch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Scott Robison-2
In reply to this post by Simon Slavin-3
On Jun 28, 2017 6:51 AM, "Simon Slavin" <[hidden email]> wrote:



On 28 Jun 2017, at 9:45am, Clemens Ladisch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Petite Abeille-2
In reply to this post by R Smith

> On Jun 28, 2017, at 4:15 PM, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by David Raymond
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by David Raymond
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by Maks Verver
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by Simon Slavin-3
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Hick Gunter
In reply to this post by Robert M. Münch
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:[hidden email]] Im Auftrag von Robert M. Münch
Gesendet: Donnerstag, 29. Juni 2017 08:16
An: SQLite mailing list <[hidden email]>
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

R Smith
In reply to this post by Robert M. Münch

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter da Silva
In reply to this post by Robert M. Münch
On 6/29/17, 1:22 AM, "sqlite-users on behalf of Robert M. Münch" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter da Silva
In reply to this post by R Smith
On 6/29/17, 5:20 AM, "sqlite-users on behalf of R Smith" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...