My problem is getting handling unsigned integers that have the high
bit set (i.e. negative)
(assume 64bit)
if I insert 0x8000000000000000 (i.e. 9223372036854775808), I would
like to be able to select and get the same unsigned decimal number
back.
select 0x8000000000000000,cast(9223372036854775808 as
integer),printf('%lu %ld 0x%0X 0x%0X',
0x8000000000000000,0x8000000000000000,0x8000000000000000,9223372036854775808);
-9223372036854775808|9223372036854775807|9223372036854775808
-9223372036854775808 0x8000000000000000 0x7FFFFFFFFFFFFFFF
The above select shows the issues.
On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth <row...@dug.com> wrote:
> sqlite is pretty loose about types. The column definitions don't constrain
> what is stored in the rows at all:
>
> sqlite> CREATE TABLE a(c INTEGER);
> sqlite> INSERT INTO a VALUES ("fourty-two");
> sqlite> SELECT * FROM a;
> fourty-two
>
> So "UNSIGNED" seems kind of pointless as it's implies a further constraint
> which is not going to be honoured. Note that sqlite does support actual
> constraints via the CHECK clause:
>
> sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
> sqlite> INSERT INTO b VALUES (-15);
> Error: constraint failed
> sqlite> INSERT INTO b VALUES (15);
>
> Although this is still allowed:
>
> sqlite> INSERT INTO b VALUES ("twenty");
> sqlite> SELECT * FROM b;
> 15
> twenty
>
> You can disallow it if you get even more specific:
>
> sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
> 0));
> sqlite> INSERT INTO b2 VALUES ("twenty");
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (0);
> sqlite> INSERT INTO b2 VALUES (-1);
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (1);
> sqlite> SELECT * FROM b2;
> 0
> 1
>
> Note that the type in the column definition does have an effect - it
> defines the column's "affinity" and may change the way data is stored. For
> example:
>
> sqlite> INSERT INTO b2 VALUES ("2");
> Error: constraint failed
>
> The TYPEOF check rejects this, but without that constraint:
>
> sqlite> INSERT INTO b VALUES ("2");
> sqlite> SELECT c, TYPEOF(c) FROM b;
> 15|integer
> twenty|text
> 2|integer
>
> ie. the text data we tried to insert was converted to an integer for
> storage.
>
> Further reading: https://www.sqlite.org/datatype3.html
>
> -Rowan
>
>
> On 21 August 2018 at 14:46, D Burgess <dburges...@gmail.com> wrote:
>
>> Is there a historical reason why sqlite does not have a UNSIGNED type
>> to go with INTEGER?
>> _______________________________________________
>> 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
