robbat2 | [Gentoo] Numeric Precision in DBs - Oracle/DB2/Firebird/MicrosoftSQLServer/{Sy,Front,Open}base (Reply)

Have you got access to DB2/Firebird/{Front,Sy,Open}Base ~~Oracle/DB2/SQLServer~~
Mind doing a little testcase for me?
You may need to change things slightly for other databases - I'd like to know what as well.

Give me the complete output - including what commands fail, and the exact numeric output.

CREATE TABLE test1 (i INTEGER PRIMARY KEY, n DECIMAL(30,10));
INSERT INTO test1 VALUES (1,12345678901234567890.0123456789);
INSERT INTO test1 VALUES (2,'12345678901234567890.0123456789');
INSERT INTO test1 VALUES (3,0.123456789012345678900123456789E20);
INSERT INTO test1 VALUES (4,'0.123456789012345678900123456789E20');
SELECT * FROM test1;

CREATE TABLE test2 (i INTEGER PRIMARY KEY, n DECIMAL);
INSERT INTO test2 VALUES (3,2.1);
INSERT INTO test2 VALUES (4,2.2);
INSERT INTO test2 VALUES (5,2.3);
INSERT INTO test2 VALUES (6,2.4);
INSERT INTO test2 VALUES (7,2.5);
INSERT INTO test2 VALUES (8,2.6);
INSERT INTO test2 VALUES (9,2.7);
INSERT INTO test2 VALUES (10,2.8);
INSERT INTO test2 VALUES (11,2.9);
INSERT INTO test2 VALUES (12,0.1234567890123456789012345678901234567890123456789012345678901234567890);
INSERT INTO test2 VALUES (13,1234567890123456789012345678901234567890123456789012345678901234567890);
INSERT INTO test2 VALUES (14,'0.1234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO test2 VALUES (15,'1234567890123456789012345678901234567890123456789012345678901234567890');
SELECT * FROM test2;

Followup (2006/06/18 14h58):

So far I have submissions for SQL Server, Oracle 8i, Informix and PostgreSQL. I should have mentioned that I had already tested MySQL/Postgresql/Sqlite2/Sqlite3 myself ;-). These tests however have revealed some very interesting problems in using high precision numbers. What good is a column of DECIMAL(30,10), if Oracle decides to store it as 1.2346E+19? That's only 5 digits of precision. Informix stores it as 1.23456789012e19 - 12 digits of precision. Postgresql gets it right from the start.

The second interesting issue is SQL Server. If it's inserted in an unnormalized form, it works perfectly fine. However try to use a normalized form, and you get capped to 17 digits of precision. Alternatively, try to work around the apparent casting by giving it as a string (which incidently is required for MySQL), and it rejects the normalized form entirely.

Does anybody know how to get more precision out of Oracle? Or use normalized numbers in SQL Server without being truncated? - I don't want to have to store numbers in varchar(255) here - but if it comes to that, I may have to write code that optionally allows people to do that.

Bit of background: I'm trying to write complete decimal support for Ruby on Rail's ActiveRecord ;-).