Numeric Data Types: INTEGER, NUMERIC, FLOAT

Today we're gonna talk about numeric data types in PostgreSQL. If you ever wondered how to store the number "42", do financial calculations, or maybe keep track of milliseconds — you're in the right place. Let's figure out which type fits each job, so you and your data can chill.

Numeric data types in PostgreSQL fall into three main groups:

1. Whole numbers (INTEGER): for storing numbers without a fractional part. These are your classic numbers like 1, 42, -7.
2. Exact numbers (NUMERIC): for storing numbers with a fixed number of digits after the decimal point. Super useful for financial calculations, where precision is more important than life itself.
3. Floating point numbers (REAL): for storing numbers that can be super big or insanely tiny. They're less precise than NUMERIC, but great for scientific calculations.

The INTEGER Type

INTEGER is the data type for whole numbers. Use it when you need to store numbers without any decimal part. PostgreSQL gives you three flavors of INTEGER to cover different ranges:

• SMALLINT: small integer type. Range: from -32,768 to 32,767.
• INTEGER (or INT): standard integer type. Range: from -2,147,483,648 to 2,147,483,647.
• BIGINT: for big numbers. Range: from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Let's say we have a students table that stores info about students: their name, age, and number of credits (academic points):

Now let's run a simple SQL query to get a list of students with their age and credits:

SELECT name, age, credits
FROM students;

Result:

When should you use INTEGER?

• Storing identifiers (id, order numbers).
• Storing counts of stuff (like number of products in stock, number of students).

The NUMERIC Type

NUMERIC is the data type for exact numbers with a fixed decimal point. If you need to store something like 123.456, and a mistake in one decimal place could cost you your reputation (or your cash), use NUMERIC.

Declaration format: NUMERIC(precision, scale), where:

• precision — total number of digits (including those before and after the decimal point).
• scale — number of digits after the decimal point.

For example, NUMERIC(6, 2) lets you store numbers with up to 6 digits, 2 of which are after the decimal point.

Let's make a table for tracking financial transactions:

Now let's show the list of transactions:

SELECT description, amount
FROM transactions;

Result:

When should you use NUMERIC?

• Financial calculations (product prices, salaries).
• Storing exact measurements (weight, length).

Table measurements

Now let's show the values:

SELECT mass, height
FROM measurements;

Result:

When should you use REAL?

• Scientific calculations (mass of atoms, distance to the Moon).
• Modeling data where some error is okay.

Comparing Numeric Types: When to Use What?

Aliases

In PostgreSQL, a lot of numeric types have aliases — these are alternative names for types that work the same but make your code easier to read or help with compatibility with other DBMSs.

Here's a quick and clear guide to numeric type aliases in PostgreSQL:

Aliases for integer types

Aliases for exact numbers

Aliases for floating point numbers

Common Mistakes

When working with numeric types, you might run into a few gotchas:

Choosing a type that's too small. If you use SMALLINT for people's ages, that's a mistake. The robot from "The Matrix" who's millions of years old will be offended.

Losing data when rounding. If you use REAL for financial calculations, you might lose a few cents. And then your clients' trust.

Comparing different types. If you compare INTEGER and REAL, you might get weird results because of how floating point numbers are represented.