Numeric Types

Choosing the right numeric type matters. Using FLOAT for currency will eventually cause rounding bugs. Using NUMERIC(20,10) everywhere wastes storage and slows queries. This lesson covers PostgreSQL's numeric types and when to use each one.

Integer Types

PostgreSQL offers three integer types that differ in their range:

When to Use Each

• SMALLINT: Quantities, ages, ratings, or status codes with small ranges
• INTEGER: The default choice for most whole-number data -- IDs, counts, quantities
• BIGINT: Very large numbers like social media post IDs, nanosecond timestamps, or financial amounts in cents at global scale

-- Integers in action
SELECT
    42 AS an_integer,
    -100 AS negative_int,
    2147483647 AS max_integer;

SERIAL: Auto-Incrementing IDs

SERIAL is not a true data type -- it is a shortcut that creates an INTEGER column backed by an auto-incrementing sequence:

When you define a column as SERIAL, PostgreSQL automatically:

1. Creates the column as INTEGER NOT NULL
2. Creates a sequence object
3. Sets the column's default to nextval('sequence_name')

-- Creating a table with a SERIAL primary key
CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- When inserting, you can omit the id column
INSERT INTO example (name) VALUES ('Alice');
-- id will automatically be 1
INSERT INTO example (name) VALUES ('Bob');
-- id will automatically be 2

Modern alternative: PostgreSQL 10+ supports GENERATED ALWAYS AS IDENTITY, which is the SQL-standard way to achieve auto-incrementing IDs:

CREATE TABLE example (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

Decimal/Numeric Types

For exact decimal numbers (especially money), use NUMERIC or DECIMAL:

NUMERIC(precision, scale)

• precision: Total number of digits (both sides of the decimal point)
• scale: Number of digits after the decimal point

SELECT
    123.456::NUMERIC(10, 2) AS rounded_to_cents,
    123.456::NUMERIC(5, 3) AS three_decimals;

The first expression returns 123.46 (rounded to 2 decimal places). The second returns 123.456.

Key point: NUMERIC and DECIMAL are identical in PostgreSQL -- they are two names for the same type.

Floating-Point Types

Floating-point types store approximate numeric values using the IEEE 754 standard:

SELECT
    1.0 / 3.0 AS division_result,
    (1.0 / 3.0)::REAL AS as_real,
    (1.0 / 3.0)::DOUBLE PRECISION AS as_double;

The Floating-Point Trap

Floating-point arithmetic can produce surprising results:

SELECT 0.1::REAL + 0.2::REAL = 0.3::REAL AS is_equal;
-- This might return false!

This is not a PostgreSQL bug -- it is how floating-point math works in all programming languages. The binary representation of 0.1 is slightly inexact.

Rule of thumb: Use NUMERIC for money and any value that must be exact. Use FLOAT/DOUBLE PRECISION for scientific calculations where tiny rounding errors are acceptable and performance matters.

Type Casting

PostgreSQL provides two ways to convert between types:

The :: Operator (PostgreSQL-Specific)

SELECT
    '42'::INTEGER AS text_to_int,
    42::TEXT AS int_to_text,
    '3.14'::NUMERIC AS text_to_numeric,
    salary::NUMERIC(10, 0) AS salary_rounded
FROM employees
LIMIT 3;

The CAST Function (SQL Standard)

SELECT
    CAST('42' AS INTEGER) AS text_to_int,
    CAST(42 AS TEXT) AS int_to_text,
    CAST('3.14' AS NUMERIC) AS text_to_numeric,
    CAST(salary AS NUMERIC(10, 0)) AS salary_rounded
FROM employees
LIMIT 3;

Both produce the same result. The :: operator is more concise and is the convention in PostgreSQL code.

Arithmetic and Rounding

Integer Division

Be careful with integer division -- it truncates the decimal:

SELECT 7 / 2 AS integer_division;
-- Returns 3, not 3.5!

To get a decimal result, cast one operand:

SELECT 7.0 / 2 AS decimal_division;
-- Returns 3.5

SELECT 7::NUMERIC / 2 AS also_decimal;
-- Returns 3.5

Rounding Functions

SELECT
    ROUND(3.7) AS rounded,          -- 4
    ROUND(3.14159, 2) AS two_dec,   -- 3.14
    CEIL(3.2) AS ceiling,           -- 4
    FLOOR(3.8) AS floor_val,        -- 3
    TRUNC(3.789, 1) AS truncated;   -- 3.7

Practical Examples

Working with Salary Data

SELECT
    name,
    salary,
    ROUND(salary / 12.0, 2) AS monthly_salary,
    ROUND(salary / 52.0, 2) AS weekly_salary,
    ROUND(salary / 260.0, 2) AS daily_rate
FROM employees
ORDER BY salary DESC
LIMIT 5;

Price Calculations

SELECT
    name,
    price,
    ROUND(price * 1.08, 2) AS price_with_tax,
    ROUND(price * 0.9, 2) AS discounted_price
FROM products
ORDER BY price DESC;

Try It Yourself

1. Select all employees and show their salary as both an integer (no decimals) and rounded to the nearest thousand.

SELECT
    name,
    salary,
    salary::INTEGER AS salary_int,
    ROUND(salary, -3) AS salary_nearest_thousand
FROM employees;

2. Calculate the total price and average price of products, showing results rounded to 2 decimal places.

SELECT
    SUM(price) AS total_price,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price)::INTEGER AS cheapest_rounded,
    MAX(price)::INTEGER AS priciest_rounded
FROM products;

3. Show each order with its total_price and a column showing what 15% tax would be, rounded to 2 decimal places.

SELECT
    customer_name,
    total_price,
    ROUND(total_price * 0.15, 2) AS tax_amount,
    ROUND(total_price * 1.15, 2) AS total_with_tax
FROM orders
ORDER BY total_price DESC;