Numeric types in Postgres

1. Integer-Point Types

Integer types are used to store whole numbers (integers) without any decimal points. PostgreSQL provides several types of integers with different ranges and storage requirements.

Subtypes and Examples:

• SMALLINT: A 2-byte integer.
  • Range: -32,768 to 32,767
  • Example:
    CREATE TABLE example_smallint (
        id SERIAL PRIMARY KEY,
        small_value SMALLINT
    );
    INSERT INTO example_smallint (small_value) VALUES (32767), (-32768);
    

• INTEGER (or INT): A 4-byte integer.
  • Range: -2,147,483,648 to 2,147,483,647
  • Example:
    CREATE TABLE example_int (
        id SERIAL PRIMARY KEY,
        age INTEGER
    );
    INSERT INTO example_int (age) VALUES (25), (2147483647), (-2147483648);
    

• BIGINT: An 8-byte integer.
  • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • Example:
    CREATE TABLE example_bigint (
        id BIGSERIAL PRIMARY KEY,
        large_value BIGINT
    );
    INSERT INTO example_bigint (large_value) VALUES (9223372036854775807), (-9223372036854775808);
    

• SERIAL, BIGSERIAL, SMALLSERIAL: Auto-incrementing integer types.
  • SERIAL: 4-byte integer with auto-increment.
    CREATE TABLE example_serial (
        id SERIAL PRIMARY KEY
    );
    

  • BIGSERIAL: 8-byte integer with auto-increment.
    CREATE TABLE example_bigserial (
        id BIGSERIAL PRIMARY KEY
    );
    

Operations:

• Arithmetic Operations:
  SELECT age + 5 FROM example_int;
  SELECT large_value * 2 FROM example_bigint;
  

• Comparison Operations:
  SELECT * FROM example_int WHERE age > 30;
  SELECT * FROM example_smallint WHERE small_value = 100;
  

Performance Considerations:

• Storage Size: SMALLINT (2 bytes), INTEGER (4 bytes), BIGINT (8 bytes). Choosing the right type can save storage space.
• Range: Ensure the type you choose covers the range of values you expect to store. Using SMALLINT for large values will result in an overflow error.
• Auto-increment: SERIAL types are convenient for primary keys, but be aware of the maximum limits for each type (SERIAL for INTEGER, BIGSERIAL for BIGINT).

When to Use:

• SMALLINT: For small-range integer values to save space (e.g., age, count).
• INTEGER: For general-purpose whole numbers (e.g., IDs, quantities).
• BIGINT: For large-range integer values (e.g., financial transactions, large datasets).
• SERIAL: For auto-incrementing primary keys with INTEGER range.
• BIGSERIAL: For auto-incrementing primary keys with BIGINT range.

2. Floating-Point Types

Floating-point types are used to store real numbers, which include fractions (decimal points). PostgreSQL provides single and double precision floating-point numbers.

Subtypes and Examples:

• REAL: A 4-byte single-precision floating-point number.
  • Range: Approximately ±3.40282347E+38 (7 decimal digits precision)
  • Example:
    CREATE TABLE example_real (
        id SERIAL PRIMARY KEY,
        height REAL
    );
    INSERT INTO example_real (height) VALUES (1.75), (3.14);
    

• DOUBLE PRECISION: An 8-byte double-precision floating-point number.
  • Range: Approximately ±1.7976931348623157E+308 (15 decimal digits precision)
  • Example:
    CREATE TABLE example_double (
        id SERIAL PRIMARY KEY,
        weight DOUBLE PRECISION
    );
    INSERT INTO example_double (weight) VALUES (70.5), (150.75);
    

Operations:

• Arithmetic Operations:
  SELECT height * 2 FROM example_real;
  SELECT weight / 2 FROM example_double;
  

• Comparison Operations:
  SELECT * FROM example_double WHERE weight > 70.5;
  SELECT * FROM example_real WHERE height = 1.75;
  

Performance Considerations:

• Precision: REAL has less precision than DOUBLE PRECISION. Use DOUBLE PRECISION for calculations requiring high precision.
• Storage Size: REAL (4 bytes), DOUBLE PRECISION (8 bytes).
• Approximation: Floating-point numbers can introduce rounding errors. For exact values, consider using NUMERIC.

When to Use:

• REAL: For approximate values where precision is not critical and storage space is a concern (e.g., scientific measurements).
• DOUBLE PRECISION: For scientific calculations requiring higher precision (e.g., financial models, simulations).

3. Exact Numeric Types

Exact numeric types are used to store numbers with a fixed number of decimal places, making them suitable for financial and monetary data.

Subtypes and Examples:

• NUMERIC (or DECIMAL): Stores exact numbers with an arbitrary precision.
  • Range: Specified by NUMERIC(p, s) where p is the total number of digits and s is the number of digits to the right of the decimal point.
  • Example:
    CREATE TABLE example_numeric (
        id SERIAL PRIMARY KEY,
        price NUMERIC(10, 2)
    );
    INSERT INTO example_numeric (price) VALUES (12345.67), (99999.99);
    

Operations:

• Arithmetic Operations:
  SELECT price * 1.1 FROM example_numeric;
  SELECT price + 100 FROM example_numeric;
  

• Comparison Operations:
  SELECT * FROM example_numeric WHERE price < 100.00;
  SELECT * FROM example_numeric WHERE price = 12345.67;
  

Performance Considerations:

• Precision and Scale: NUMERIC(p, s) allows you to define the precision (p total digits) and scale (s digits after the decimal point). This makes it suitable for financial calculations where exact values are crucial.
• Storage Size: Storage size varies based on the precision and scale defined. Generally, the more precise the number, the more storage it will require.

When to Use:

• NUMERIC: For financial and monetary data requiring exact precision (e.g., currency values, financial calculations).

---

Summary

Here’s a quick summary of the PostgreSQL numeric types covered:

• Integer Types:
  • SMALLINT: Small-range integers, 2 bytes.
  • INTEGER: General-purpose integers, 4 bytes.
  • BIGINT: Large-range integers, 8 bytes.
  • SERIAL: Auto-incrementing integers, 4 bytes.
  • BIGSERIAL: Auto-incrementing large integers, 8 bytes.
• Floating-Point Types:
  • REAL: Single-precision floating-point, 4 bytes.
  • DOUBLE PRECISION: Double-precision floating-point, 8 bytes.
• Exact Numeric Types:
  • NUMERIC (or DECIMAL): Exact numbers with arbitrary precision.

By understanding and using these numeric types appropriately, you can effectively manage and store numeric data in your PostgreSQL database. This ensures that your database is both efficient and reliable.