What you are talking about would be feature creep for SQLite. Yes...other databases do respect NUMBER(10,2) on SELECT's.
SQLite is "light weight" and does no such magic for you. So it does take an extra step. You'll note that SQLite does provide a GUI for you to play with. If it did it would probably allow you to format columns. This keeps the library small and lightweight. Can you simply use round to do what you want? CREATE TABLE stuff(f number(10,2)); INSERT INTO "stuff" VALUES(1.0/3.0); sqlite> select f from stuff; 0.333333333333333 sqlite> select round(f,2) from stuff; 0.33 Doesn't work, of course, for generic "table edit" in some GUI. For that you could create a view. sqlite> create view fview as select round(f,2) from stuff; sqlite> select * from fview; -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden Sent: Monday, May 06, 2013 7:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is there a way to select a precision? > A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > SQLite will attempt to store (string) values as integers first and floats > second before giving up and storing strings. > This has nothing to do with my reply and I understand how it works. > > You do realize that there are decimal numbers that have infinite binary > expansions? > I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know they are the same in SQLite). > > You are also talking presentation (as in formatting) of numeric values as > opposed to representation (as in storing/retrieving). The former is best > handled in the user interface while the latter is the subject of database > engines. > My point was about not storing binary junk - the part of a number that has no meaning because the accuracy of the inputs is limited. When you have a generic db manager that can show any table or if you are looking at the results of your own SQL statement, it helps to reduce clutter on the screen. The data also compresses better. > > Fatihful reproduction of formatting would be possible using TEXT affinity > and calling sqlite3_bind_text. Performing arithmetic with these "numbers" > would however be tricky, slow and would still not guarantee that calculated > values would conform to the desired formatting. > > Of course, but in most cases we don't need to go this far. My main point is about rounding before binding; my secondary point that scale in a column definition can be desirable to avoid it. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
