I tried this in SQLite Manager for Firefox.
CREATE TABLE "PRODUCTS" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
UNIQUE , "product" TEXT, "rank" INTEGER)
insert into products (product, rank) values ('gizmo', 1.0)
insert into products (product, rank) values ('widget', 2.0)
insert into products (product, rank) values ('foo', 2.2)
insert into products (product, rank) values ('foo2', 2.00)
select typeof(rank) from products
integer
integer
real
So it would appear that if the numeric value to be inserted can be coerced
to INTEGER without loss, it will in fact become an INTEGER, otherwise it
stay what it was, REAL.
TEST:
select cast(rank as integer), rank from products where cast(rank as
integer) <> rank
1 | 2.2
So, if the OP executes the TEST query above, it should discover any value
with something other than zero(s) to the right of the decimal point.
And you can convert that test into a check constraint to prevent non-integer
values from being inserted into the table:
CREATE TABLE "main"."PROD" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL UNIQUE , "product" TEXT,
"rank" INTEGER check (cast(rank as INTEGER) =rank) )
Regards
Tim Romano
Swarthmore PA
On Wed, Jul 14, 2010 at 10:19 AM, Bogdan Ureche <[email protected]> wrote:
> >
> >
> > SQLite Expert (which I use extensively in the Pro version) enforces
> > displaying types as declared. So if you declare your column as INTEGER
> > (or INT, ...) then it will display integers regardless of the actual
> > individual data type using common conversions. This is a side effect
> > of the Delphi grid component that Bogdan uses.
> >
> >
> This is the default behavior in SQLite Expert but can be overridden. If you
> store floating-point values in columns declared as INTEGER, you might want
> to change the default type mappings to INTEGER -> Float (or WideString) and
> you will see the floating-point values correctly in the grid.
>
> Bogdan Ureche
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
