A Complete Guide to PostgreSQL Numeric Data Types

As a database professional or developer working with PostgreSQL, having a solid grasp of the various data types is essential. The numeric data types in particular are some of the most commonly used when designing schemas and writing queries. Understanding the nuances, trade-offs, and best practices around numeric types will help you build more efficient, scalable, and bug-free applications.

In this in-depth guide, we‘ll take a close look at the numeric data types offered by PostgreSQL, including integers, decimals, floating-point numbers, and auto-incrementing serials. We‘ll discuss the characteristics of each type, when to use them, how they impact performance and storage, useful functions and operators, and common errors to watch out for.

By the end of this article, you‘ll be equipped with the knowledge to master numeric types in PostgreSQL and take your database skills to the next level. Let‘s dive in!

Why Numeric Data Types Matter

Before we get into the specifics of each numeric type, it‘s worth taking a step back to understand why having a variety of numeric types is valuable. Numeric data is extremely common – things like product prices, stock quantities, sensor readings, financial calculations, analytics, and more all rely heavily on storing and manipulating numbers.

However, not all numbers are the same. An integer (a whole number) is a fundamentally different thing than a decimal (a number with a fractional component). Likewise, numbers with a very large magnitude require different handling than those closer to zero.

This is where having multiple, specialized numeric data types comes into play. By matching the type of number you‘re working with to the appropriate data type, you gain benefits like:

Reduced storage usage. Integers take up less space than decimals, for example.
Improved performance. Operations on integers are faster than floating-point math.
Enforced data integrity. An integer column will reject non-whole number values.
Clearer semantics. Using a decimal type conveys that precision is important.

PostgreSQL provides a robust selection of numeric types so that you can optimize your schemas and queries for your particular application needs. Let‘s take a look at each of the key types, starting with integers.

Integer Types

Integer types are used for storing whole numbers, either positive, negative, or zero. These are numbers without any fractional or decimal component.

SMALLINT

The SMALLINT data type is a 2-byte (16-bit) signed integer with a range of -32,768 to 32,767. This makes it the smallest of the integer types. It‘s a good fit for storing numbers where you know the range will be limited, such as a person‘s age, the number of items in a small inventory, or values from a constrained set of options.

Here‘s an example of creating a table with a SMALLINT column:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  quantity SMALLINT
);

And inserting some values:

INSERT INTO products (name, quantity) 
VALUES 
  (‘Widget‘, 100),
  (‘Gadget‘, 50),
  (‘Gizmo‘, -25);

INTEGER

The INTEGER type, sometimes referred to as INT, is a 4-byte (32-bit) signed integer spanning from -2,147,483,648 to 2,147,483,647. This is the most common integer type and is often used as a default unless you have reason to use something else.

INTEGER can comfortably hold values for things like money amounts (in cents), large counters, timestamps, and primary key IDs. Here‘s an example:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INTEGER,
  amount INTEGER, -- e.g. $10.99 stored as 1099 cents
  order_date INTEGER -- Unix timestamp
);

BIGINT

For storing very large whole numbers, the 8-byte (64-bit) BIGINT type has you covered. It can handle values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. That‘s over 9 quintillion!

BIGINT is useful for things like large-scale analytics, scientific computing, or precise timestamps with microsecond resolution. Keep in mind though that operations on BIGINT are generally slower than on the smaller integer types.

CREATE TABLE analytics (
  event_id BIGINT,
  event_timestamp BIGINT, -- Microsecond precision
  user_id INTEGER,
  -- ... 
);

Decimal Types

When you need to store fractional numbers or do precise calculations, the decimal types are what you‘ll turn to. PostgreSQL offers both NUMERIC and DECIMAL, which are actually equivalent types and can be used interchangeably.

NUMERIC / DECIMAL

NUMERIC and DECIMAL are variable-precision data types, meaning they can store numbers with a very large number of digits. Up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point, to be exact! This makes them suitable for applications requiring exact storage and calculations on fractional numbers, like financial systems or scientific simulations.

When declaring a NUMERIC or DECIMAL column, you can optionally specify the precision and scale. The precision is the total count of digits, while the scale is the number of digits after the decimal point. So NUMERIC(5,2) could hold values like 123.45 but not 1234.5.

Here‘s an example table using NUMERIC:

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY, 
  amount NUMERIC(10,2),
  description VARCHAR(100)
);

And some sample INSERT statements:

INSERT INTO transactions (amount, description)
VALUES
  (109.99, ‘Grocery shopping‘),
  (9.90, ‘Streaming service‘),
  (3.50, ‘Coffee‘);

Note that exact storage and calculations come with a trade-off: NUMERIC/DECIMAL are significantly slower than integer types or floating-point numbers. Be sure to use them only when that level of precision is truly required.

Floating-Point Types

For applications that can tolerate small inaccuracies, like games or analytics, the floating-point data types provide better performance than decimals while still supporting fractional values.

REAL

The REAL data type, also known as FLOAT4, is a 4-byte (32-bit) floating-point number. It follows the IEEE 754 standard binary format and offers 6 decimal digits of precision.

Because of the way floating-point is implemented, storage and computation using REAL is much faster than NUMERIC but may introduce small rounding errors. This is fine for use cases where exactness isn‘t critical.

CREATE TABLE sensors (
  id SERIAL PRIMARY KEY,
  reading REAL,
  recorded_at TIMESTAMP
);

DOUBLE PRECISION

When you need higher precision than REAL provides, DOUBLE PRECISION (or FLOAT8) is the next step up. As the name implies, it uses 8 bytes (64 bits) to store numbers and provides 15 decimal digits of precision.

Like REAL, DOUBLE PRECISION is an IEEE 754 binary floating-point format with the same caveats around inexact representation and rounding. It‘s a good default choice though when working with fractional numbers that don‘t require exact storage, like ML model weights or physics simulations.

CREATE TABLE ai_models (
  model_id INTEGER,
  weights DOUBLE PRECISION[],
  bias DOUBLE PRECISION  
);

Auto-Incrementing Types

A common pattern in database schemas is having a unique, auto-incrementing identifier for each row in a table. While you could manage this yourself using an INTEGER column and carefully track the next available value, PostgreSQL provides the SERIAL types to handle this for you.

SERIAL

The SERIAL type is a 4-byte auto-incrementing integer. Under the hood, it‘s actually just an INTEGER column with a default value pulled from a sequence. This sequence automatically manages the incrementing of the number each time a row is inserted.

SERIAL is most frequently used for synthetic primary keys – identifiers that don‘t have any meaning outside the database but provide a convenient way to uniquely reference rows.

To add a SERIAL column to a table, just use the SERIAL keyword in place of INTEGER:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) 
);

Now, any time you perform an INSERT without specifying the id, PostgreSQL will automatically assign the next number in the sequence:

INSERT INTO users (name, email) 
VALUES 
  (‘Alice‘, ‘[email protected]‘),
  (‘Bob‘, ‘[email protected]‘);

SELECT * FROM users;

 id | name  | email
----+-------+-------------------
  1 | Alice | [email protected]
  2 | Bob   | [email protected]

The other SERIAL types work similarly but for different integer ranges:

SMALLSERIAL: 2-byte auto-incrementing integer
BIGSERIAL: 8-byte auto-incrementing integer

Choosing the Right Numeric Type

With all these numeric types to choose from, you may be wondering how to decide which one is appropriate for a given situation. Here are some general guidelines:

Prefer integer types for whole numbers and counting things. They offer the best performance and simplest storage. SERIAL is great for auto-incrementing IDs.
If you‘re working with money, store the amount in the smallest unit of your currency (e.g. cents) as an integer to avoid rounding errors, then divide by 100 to format the decimal amount.
Floating-point types are appropriate when you need to do calculations on fractional numbers but can tolerate small inaccuracies. Reach for REAL unless you really need the extra precision of DOUBLE.
Only use NUMERIC/DECIMAL when exact storage and calculations on arbitrary precision numbers is required. The performance hit usually isn‘t worth it otherwise.
Watch out for the range limitations on each type. If you try to store a number outside the allowable range, you‘ll get an error. Choose a type with enough headroom for your expected values.

At the end of the day, being deliberate about your numeric types will pay dividends in the form of a more efficient, maintainable, and bug-free application. Don‘t just settle for defaults!

Numeric Functions and Operators

PostgreSQL provides a variety of built-in functions and operators for working with numeric types. Here are a few of the most commonly used:

+, -, *, / – Perform addition, subtraction, multiplication, and division.
% – Perform modulo division, returning the remainder.
ABS(x) – Return the absolute value of x.
ROUND(x) – Round x to the nearest integer. Use ROUND(x,n) to round to n decimal places.
CEIL(x) / FLOOR(x) – Round x up or down to the nearest integer.
POWER(x,y) / SQRT(x) – Raise x to the power of y, or return the square root of x.
RANDOM() – Return a random number between 0 and 1. Useful for sampling.
AVG(x) – Return the average of all input values x, ignoring any NULL values.

For example, to calculate a 15% discount on all products:

UPDATE products 
SET price = price * 0.85;

Or to find the most expensive product under $100:

SELECT name, price
FROM products
WHERE price < 10000 -- Assuming price in cents
ORDER BY price DESC
LIMIT 1;

Performance Considerations

The numeric type you choose can have a big impact on query performance and database efficiency. Here are a few things to keep in mind:

Integer types are almost always faster than floating-point or decimal types for storage, calculations, and comparisons. If you don‘t actually need the extra features, stick with integers.
Smaller types like SMALLINT are slightly more efficient than larger types like BIGINT, but usually the difference is negligible. Optimize for ease of use and correctness first.
Avoid casting between numeric types in queries if possible, especially to or from strings. While PostgreSQL will implicitly cast types when needed, it‘s better to store numbers in their native types to start with.
If you‘re doing a lot of calculations on large numbers, consider breaking the work into smaller chunks to avoid overflow errors. The generate_series function is handy for this.
Pay attention to the query planner output and execution times. If you see costly operations involving numeric types, that‘s a good place to start optimizing.

PostgreSQL vs Other Databases

Most relational databases offer similar basic numeric types – integers, fixed and floating-point decimals, etc. However, there are a few areas where PostgreSQL stands out:

PostgreSQL has a true arbitrary precision decimal type in the form of NUMERIC and DECIMAL. MySQL only offers DECIMAL up a certain size, and SQLite has no arbitrary precision type at all.
The different SERIAL types make auto-incrementing more convenient compared to other databases where you have to manually create and manage sequences.
PostgreSQL‘s floating point types fully conform to IEEE 754 for maximum portability and accuracy. Some other databases like SQL Server have their own custom binary formats.

This isn‘t to say that other databases are lacking, but if you need the most robust and standards-compliant numeric support, PostgreSQL is hard to beat.

Errors and Gotchas

To finish up, let‘s cover a few of the most common errors and pitfalls when working with PostgreSQL numeric types:

Integer overflow. If you try to store a number outside the valid range for an integer type, you‘ll get an overflow error. Always choose a type with enough capacity for your expected values, and be careful with very large numbers in calculations.
Floating-point precision. Due to the way floating-point types are stored, they don‘t always have an exact representation. Be aware that you may see small discrepancies in calculations as a result. Avoid equality comparisons on floats and use tolerant ranges instead.
Implicit casting. PostgreSQL will automatically cast between numeric types in many expressions, like comparing an integer to a float in a WHERE clause. This is usually helpful but can sometimes lead to unexpected results or slow performance. When in doubt, explicitly cast types to be sure.
Division by zero. Dividing any number by zero results in an error. If you anticipate zeros in your divisors, check for them first or use NULLIF to avoid divide-by-zero errors.
Forgetting to use a SERIAL type. It‘s an easy mistake to make – you create a regular old INTEGER column for your primary key, but forget to make it auto-incrementing. Then your INSERTs fail because you didn‘t specify a unique value. Get in the habit of using SERIAL for synthetic keys unless you have a good reason not to.

Conclusion

Phew, that was a lot of information! We covered all the main PostgreSQL numeric data types, how they‘re implemented, when to use them, performance considerations, comparisons to other databases, and common issues.

The key takeaway is that being deliberate about the numeric types you use and knowing their strengths and limitations will make you a better database designer and programmer. Don‘t just settle for the most obvious type – understand the real requirements and choose accordingly. Your future self will thank you!

I hope you found this guide informative and valuable. For more PostgreSQL tips and tricks, be sure to check out the official documentation at https://www.postgresql.org/docs/. Happy querying!