DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • The Generic Way To Convert Between Java and PostgreSQL Enums
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • Data Software Design Pitfalls on Java: Should We Have a Constructor on JPA?
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps

Trending

  • Build a Serverless App Fast With Zipper: Write TypeScript, Offload Everything Else
  • Four Ways for Developers To Limit Liability as Software Liability Laws Seem Poised for Change
  • AI for Web Devs: Project Introduction and Setup
  • How To Use ChatGPT API in Python for Your Real-Time Data
  1. DZone
  2. Data Engineering
  3. Databases
  4. Fixed-Point and Floating-Point: Two Things That Don't Go Well Together

Fixed-Point and Floating-Point: Two Things That Don't Go Well Together

What goes together like oil and water? Mixing fixed- and floating-point numbers. See how pulling different data types can cause problems.

Dustin Marx user avatar by
Dustin Marx
·
Nov. 18, 16 · Tutorial
Like (4)
Save
Tweet
Share
10.74K Views

Join the DZone community and get the full member experience.

Join For Free

one of the more challenging aspects of software development can be dealing with floating-point numbers. david goldberg 's 1991 computing surveys paper what every computer scientist should know about floating-point arithmetic is a recognized classic treatise on this subject . this paper not only provides an in-depth look at how floating-point arithmetic is implemented in most programming languages and computer systems, but also, through its length and detail, provides evidence of the nuances and difficulties of this subject. the nuances of dealing with floating-point numbers in java and tactics to overcome these challenges are well documented in sources such as javaworld 's floating-point arithmetic , ibm developerworks 's java's new math, part 2: floating-point numbers and java theory and practice: where's your point? , dr. dobb's java's floating-point (im)precision and fixed, floating, and exact computation with java's bigdecimal , java glossary 's floating point , java tutorial 's primitive data types , and num04-j. do not use floating-point numbers if precise computation is required .

most of the issues encountered and discussed in java related to floating-point representation and arithmetic are caused by the inability to precisely represent (usually) decimal ( base ten ) floating point numbers with an underlying binary ( base two ) representation. in this post, i focus on similar consequences that can result from mixing fixed-point numbers (as stored in a database) with floating-point numbers (as represented in java).

the oracle database allows numeric columns of the number data type to be expressed with two integers that represent "precision" and "scale." the postgresql implementation of the numeric data type is very similar. both oracle's number(p,s) and postgresql's numeric(p,s) allow the same datatype to represent essentially an integral value (precision specified but scale not specified), a fixed-point number (precision and scale specified), or a floating-point number (neither precision nor scale specified). simple java/jdbc-based examples in this post will demonstrate this.

for the examples in this post, a simple table named doubles in oracle and doubles in postgresql will be created. the ddl statements for defining these simple tables in the two database are shown next.

createoracletable.sql

create table doubles
(
   int number(5),
   fixed number(3,2),
   floating number
);

createpgtable.sql

create table doubles
(
   int numeric(5),
   fixed numeric(3,2),
   floating numeric
);

with the doubles table created in oracle database and postgresql database, i'll next use a simple jdbc preparedstatement to insert the value of java.lang.math.pi into each table for all three columns. the following java code snippet demonstrates this insertion.

inserting math.pi into doubles columns

/** sql syntax for insertion statement with placeholders. */
private static final string insert_string =
   "insert into doubles (int, floating, fixed) values (?, ?, ?)";


final connection connection = getdatabaseconnection(databasevendor);
try (final preparedstatement insert = connection.preparestatement(insert_string))
{
   insert.setdouble(1, math.pi);
   insert.setdouble(2, math.pi);
   insert.setdouble(3, math.pi);
   insert.execute();
}
catch (sqlexception sqlex)
{
   err.println("unable to insert data - " + sqlex);
}

querying doubles columns

/** sql syntax for querying statement. */
private static final string query_string =
   "select int, fixed, floating from doubles";

final connection connection = getdatabaseconnection(databasevendor);
try (final statement query = connection.createstatement();
     final resultset rs = query.executequery(query_string))
{
   out.println("\n\nresults for database " + databasevendor + ":\n");
   out.println("math.pi :        " + math.pi);
   while (rs.next())
   {
      final double integer = rs.getdouble(1);
      final double fixed = rs.getdouble(2);
      final double floating = rs.getdouble(3);
      out.println("integer number:  " + integer);
      out.println("fixed number:    " + fixed);
      out.println("floating number: " + floating);
   }
   out.println("\n");
}
catch (sqlexception sqlex)
{
   err.println("unable to query data - " + sqlex);
}

the output of running the above java insertion and querying code against the oracle and postgresql databases respectively is shown in the next two screen snapshots.

comparing math.pi to oracle's number columns

image title


comparing math.pi to postgresql's numeric columns

image title


the simple examples using java and oracle and postgresql demonstrate issues that might arise when specifying precision and scale on the oracle number and postgresql numeric column types. although there are situations when fixed-point numbers are desirable, it is important to recognize that java does not have a fixed-point primitive data type and use bigdecimal or a fixed-point java library (such as decimal4j or java math fixed point library ) to appropriately deal with the fixed-point numbers retrieved from database columns expressed as fixed points. in the examples demonstrated in this post, nothing is really "wrong", but it is important to recognize the distinction between fixed-point numbers in the database and floating-point numbers in java because arithmetic that brings the two together may not have the results one would expect.

in java and other programming languages, one needs to not only be concerned about the effect of arithmetic operations and available precision on the "correctness" of floating-point numbers. the developer also needs to be aware of how these numbers are stored in relational database columns in the oracle and postgresql databases to understand how precision and scale designations on those columns can affect the representation of the stored floating-point number. this is especially applicable if the representations queried from the database are to be used in floating-point calculations. this is another (of many) examples where it is important for the java developer to understand the database schema being used.

Relational database Database Java (programming language) Software development PostgreSQL Data Types

Published at DZone with permission of Dustin Marx, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • The Generic Way To Convert Between Java and PostgreSQL Enums
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • Data Software Design Pitfalls on Java: Should We Have a Constructor on JPA?
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: