To use or not to use single quotes with integers

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

To use or not to use single quotes with integers

Markos
Hi,

I'm a beginner with sqliteand used to insert values in the fields of
datatype text with single quote and values of datatype integer without
single quote.

But I realized that sqlite accepts inserting both data types (text and
integer) with single quote without error.

For example the table:

CREATE TABLE foods(
   id integer PRIMARY KEY,
   type_id integer,
   name text );

I can insert type_id without single quote:

INSERT INTO foods (name, type_id) VALUES ('Rice', 16);

And also with single quote:

INSERT INTO foods (name, type_id) VALUES ('Bean', '17');

select * FROM foods;

...
423         16          Rice
424         17          Bean

What are the consequences of inserting values in fields of datatype
integer with single quotes?

What are the effects on database size and performance?

Can I have problems in the future to make a query using logical
operators in these fields?

I'm making this question because it is simpler to implement in my
interface a routine to assemble a list of fields and values by inserting
single quotation marks in all elements of the list of values.

Otherwise I would have to identify the datatype of each field to decide
whether or not to include the quotes.

Thank you,
Markos

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: To use or not to use single quotes with integers

Simon Slavin-3
On 8 Apr 2018, at 11:54am, Markos <[hidden email]> wrote:

> CREATE TABLE foods(
>  id integer PRIMARY KEY,
>  type_id integer,
>  name text );
>
> I can insert type_id without single quote:
>
> INSERT INTO foods (name, type_id) VALUES ('Rice', 16);
>
> And also with single quote:
>
> INSERT INTO foods (name, type_id) VALUES ('Bean', '17');
>
> select * FROM foods;
>
> ...
> 423         16          Rice
> 424         17          Bean
>
> What are the consequences of inserting values in fields of datatype integer with single quotes?

Well done for testing what happens rather than just assuming things worked the way you thought.

When you created the table you specified the affinity of each column.  If you define a column as INTEGER but supply a string, SQLite checks to see whether that string represents an integer.  If it does, then it stores the integer, effectively doing the translation for you.  The actual value stored is integer, and there is no trace that it was originally specified as a string.

A similar thing happens when you specify that a column has affinity of REAL.  In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion.  However for the number to be stored the conversion has to be reversible.  If SQLite reverses the conversion and doesn't get the original string back it stores the string instead.

You can test to see what SQLite did using

    SELECT id, type_id, typeof(type_id), name FROM foods

See section 3 (more usefully, the entire page) of

<https://www.sqlite.org/datatype3.html>

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
|

Re: To use or not to use single quotes with integers

David Raymond
In reply to this post by Markos
Insert the usual "Bobby Tables" reply here
https://xkcd.com/327/

You really want to just bind the values to the insert statement, both to prevent issues and make things simpler. It'll look slightly different in each language, but it's basically 1 SQL statement text which contains placeholders. Then for each record you want to insert/update, etc you give it the raw values separate from the SQL text, and your language of choice will do the magic of inserting that exact value, whatever type of value that is.

So it might look something like:

myInsertStatement = "insert into foods (name, type_id) values (?, ?);"
for some looping construct:
    nameVariable = some name
    type_idVariable = some ID thing of whatever type
    databaseCursor.execute(myInsertStatement, nameVariable, type_idVariable)
databaseConnection.commit()

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Markos
Sent: Sunday, April 08, 2018 6:54 AM
To: [hidden email]
Subject: [sqlite] To use or not to use single quotes with integers


I'm making this question because it is simpler to implement in my
interface a routine to assemble a list of fields and values by inserting
single quotation marks in all elements of the list of values.

Otherwise I would have to identify the datatype of each field to decide
whether or not to include the quotes.

Thank you,
Markos

_______________________________________________
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
|

Re: To use or not to use single quotes with integers

Markos
In reply to this post by Simon Slavin-3
Thanks Simon and David for your attention,
Markos

------------------------------------------------------------------------------------------------------------------------------------------------------------

Insert the usual "Bobby Tables" reply here
https://xkcd.com/327/

You really want to just bind the values to the insert statement, both to prevent issues and make things simpler. It'll look slightly different in each language, but it's basically 1 SQL statement text which contains placeholders. Then for each record you want to insert/update, etc you give it the raw values separate from the SQL text, and your language of choice will do the magic of inserting that exact value, whatever type of value that is.

So it might look something like:

myInsertStatement = "insert into foods (name, type_id) values (?, ?);"
for some looping construct:
     nameVariable = some name
     type_idVariable = some ID thing of whatever type
     databaseCursor.execute(myInsertStatement, nameVariable, type_idVariable)
databaseConnection.commit()



Em 08-04-2018 14:03, Simon Slavin escreveu:

> On 8 Apr 2018, at 11:54am, Markos <[hidden email]> wrote:
>
>> CREATE TABLE foods(
>>   id integer PRIMARY KEY,
>>   type_id integer,
>>   name text );
>>
>> I can insert type_id without single quote:
>>
>> INSERT INTO foods (name, type_id) VALUES ('Rice', 16);
>>
>> And also with single quote:
>>
>> INSERT INTO foods (name, type_id) VALUES ('Bean', '17');
>>
>> select * FROM foods;
>>
>> ...
>> 423         16          Rice
>> 424         17          Bean
>>
>> What are the consequences of inserting values in fields of datatype integer with single quotes?
> Well done for testing what happens rather than just assuming things worked the way you thought.
>
> When you created the table you specified the affinity of each column.  If you define a column as INTEGER but supply a string, SQLite checks to see whether that string represents an integer.  If it does, then it stores the integer, effectively doing the translation for you.  The actual value stored is integer, and there is no trace that it was originally specified as a string.
>
> A similar thing happens when you specify that a column has affinity of REAL.  In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion.  However for the number to be stored the conversion has to be reversible.  If SQLite reverses the conversion and doesn't get the original string back it stores the string instead.
>
> You can test to see what SQLite did using
>
>      SELECT id, type_id, typeof(type_id), name FROM foods
>
> See section 3 (more usefully, the entire page) of
>
> <https://www.sqlite.org/datatype3.html>
>
> 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
|

Re: To use or not to use single quotes with integers

Thomas Kurz
In reply to this post by Simon Slavin-3
Dear Simon,

> A similar thing happens when you specify that a column has affinity of REAL.  In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion.  However for the number to be stored the conversion has to be reversible.  If SQLite reverses the conversion and doesn't get the original string back it stores the string instead.

I don't think this is actually the case:

create table test (i integer, r real, s text);
insert into test (i, r, s) values (3.141592653589793238462643, 3.141592653589793238462643, 3.141592653589793238462643);
insert into test (i, r, s) values ('3.141592653589793238462643', '3.141592653589793238462643', '3.141592653589793238462643');
select * from test;

In any case except of actually storing the string to column s, I get the stored value truncated to 14 decimals.

select cast(s as text) from test;

Even here, the first value is truncated to 14 decimals. Only the 2nd is correct.

Kind regards,
Thomas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: To use or not to use single quotes with integers

David Raymond
http://www.sqlite.org/datatype3.html#type_affinity

The key sentence in that section is
"SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved"


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Kurz
Sent: Thursday, April 12, 2018 2:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] To use or not to use single quotes with integers

Dear Simon,

> A similar thing happens when you specify that a column has affinity of REAL.  In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion.  However for the number to be stored the conversion has to be reversible.  If SQLite reverses the conversion and doesn't get the original string back it stores the string instead.

I don't think this is actually the case:

create table test (i integer, r real, s text);
insert into test (i, r, s) values (3.141592653589793238462643, 3.141592653589793238462643, 3.141592653589793238462643);
insert into test (i, r, s) values ('3.141592653589793238462643', '3.141592653589793238462643', '3.141592653589793238462643');
select * from test;

In any case except of actually storing the string to column s, I get the stored value truncated to 14 decimals.

select cast(s as text) from test;

Even here, the first value is truncated to 14 decimals. Only the 2nd is correct.

Kind regards,
Thomas

_______________________________________________
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
|

Re: To use or not to use single quotes with integers

Simon Slavin-3
In reply to this post by Thomas Kurz
On 12 Apr 2018, at 7:49pm, Thomas Kurz <[hidden email]> wrote:

> [Simon Slavin wrote]
>
>> A similar thing happens when you specify that a column has affinity of REAL.  In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion.  However for the number to be stored the conversion has to be reversible.  If SQLite reverses the conversion and doesn't get the original string back it stores the string instead.
>
> I don't think this is actually the case:
>
> create table test (i integer, r real, s text);
> insert into test (i, r, s) values (3.141592653589793238462643, 3.141592653589793238462643, 3.141592653589793238462643);
> insert into test (i, r, s) values ('3.141592653589793238462643', '3.141592653589793238462643', '3.141592653589793238462643');
> select * from test;
>
> In any case except of actually storing the string to column s, I get the stored value truncated to 14 decimals.

From the above data (nice test dataset, by the way):

sqlite> SELECT i,typeof(i) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT r,typeof(r) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT s,typeof(s) FROM test;
3.14159265358979|text
3.141592653589793238462643|text

Where you specified that you wanted a string stored and provided a string in the INSERT command, SQLite stored a string.  In all other cases SQLite had to handle the value as a number at some point in the storage process, and it turned it into a floating point value as long as its floating point library can handle.  Since it would have to reduce the precision in this way before doing any mathematical operation on the resulting value, this counts as 'identical and reversible'.

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