I have a database table in which there is a column defined as Real(4,2); When I fetch values for this column and look at the column type value it is sometimes Double and sometimes Int, I guess depending on whether or not the stored value can be considered an Int or not. (EG if I store the value 3.5 it is typed as Double, but if I store the value 3.0 it is typed is Int).

My question is: is there any way to "force" sqlite3 to always return the value as Double? As a workaround, I created a VIEW on the table in which I select all the row column values but if I want the REAL value to always be Double I select it as 1.0*Column AS RColumn instead of just Column; then RColumn will always be Double.

Define the column as a type that actually exists: Real.
The extra (4,2) does nothing for SQLite.

You are probably using an ancient version of SQLite from before this was fixed several years ago -- and you failed to state the version in which you see this occur.

If you use the "current versions" do you see the same behaviour?

Note that the response that the typename is incorrect is incorrect. It is true that the blathering "(4,2)" is ignored, but so would be other blatherings like having the column declared as "quacking real duck" (aka col quacking real duck).

See https://sqlite.org/datatype3.html

Thanks for the replies!

I am using a current version: macOS 3.39.4 2022-09-07; however I had been experimenting with different affinities and the results I posted were actually using "decimal(4,2)" rather than "real". When I switch to "real" then the values are retrieved as I expected them to be (IE 4.5 is displayed as 4.5 and 3 is displayed as 3.0). Sorry for any confusion.

I think what you mean to say is that if the affinity is NUMERIC, then an integer will be stored as an integer, a real (floating point) will be stored as an integer if the fractional part is zero or as a real if the fractional part is not zero;

text -- if it looks like an integer (the fractional part is 0) will be stored as an integer, if it looks like floating point (has a non-zero fractional part) will be stored as real, otherwise it will be stored as text. A blob will be stored as a blob. NULL will be stored as a NULL.

"Anything else will be stored as text" is incorrect.

sqlite> create table x(x numeric);
sqlite> insert into x values (1), (2.0), (3.1), ('text'), ('12'), ('14.1'), ('15.0'), (NULL), (x'31');
sqlite> select * from x;
┌────────┐
│   x    │
├────────┤
│ 1      │
│ 2      │
│ 3.1    │
│ 'text' │
│ 12     │
│ 14.1   │
│ 15     │
│ NULL   │
│ x'31'  │
└────────┘

That's .mode qbox by the way, which makes it visually apparent that you are seeing what you get ...

This makes it even more apparent:

sqlite> select x, typeof(x) from x;
┌────────┬───────────┐
│   x    │ typeof(x) │
├────────┼───────────┤
│ 1      │ 'integer' │
│ 2      │ 'integer' │
│ 3.1    │ 'real'    │
│ 'text' │ 'text'    │
│ 12     │ 'integer' │
│ 14.1   │ 'real'    │
│ 15     │ 'integer' │
│ NULL   │ 'null'    │
│ x'31'  │ 'blob'    │
└────────┴───────────┘