What version is that? I have 3.8.1 handy (ancient I know), which doesn't
support hex literals but:
sqlite> select cast(9223372036854775808 as integer);
-9223372036854775808
Which is different to your result...
-Rowan
On 21 August 2018 at 17:19, D Burgess <dburges...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
