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:
INTEGER
(orINT
): A 4-byte integer.- Range: -2,147,483,648 to 2,147,483,647
- Example:
BIGINT
: An 8-byte integer.- Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
- Example:
SERIAL
,BIGSERIAL
,SMALLSERIAL
: Auto-incrementing integer types.SERIAL
: 4-byte integer with auto-increment.BIGSERIAL
: 8-byte integer with auto-increment.
Operations:
- Arithmetic Operations:
- Comparison Operations:
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
forINTEGER
,BIGSERIAL
forBIGINT
).
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 withINTEGER
range.BIGSERIAL
: For auto-incrementing primary keys withBIGINT
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:
DOUBLE PRECISION
: An 8-byte double-precision floating-point number.- Range: Approximately ±1.7976931348623157E+308 (15 decimal digits precision)
- Example:
Operations:
- Arithmetic Operations:
- Comparison Operations:
Performance Considerations:
- Precision:
REAL
has less precision thanDOUBLE PRECISION
. UseDOUBLE 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
(orDECIMAL
): Stores exact numbers with an arbitrary precision.- Range: Specified by
NUMERIC(p, s)
wherep
is the total number of digits ands
is the number of digits to the right of the decimal point. - Example:
- Range: Specified by
Operations:
- Arithmetic Operations:
- Comparison Operations:
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
(orDECIMAL
): 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.
Was this page helpful?