Numeric datatype

Why Numeric Data Types Matter

"Numeric data types define how your numbers are stored in the database. It impacts accuracy, disk usage, and query performance. For example, using real for monetary amounts can cause rounding errors, while numeric incurs more storage and CPU cost."

Integer Types and Storage

PostgreSQL integer types and their storage footprint:

Use Cases and Storage Example:

CREATE TABLE test_integers (
  id SERIAL PRIMARY KEY,
  age SMALLINT,
  population INTEGER,
  national_debt BIGINT
);

INSERT INTO test_integers (age, population, national_debt)
VALUES (25, 1200000, 900000000000);

-- Check storage size of each column value:
SELECT
  pg_column_size(age) AS age_bytes,
  pg_column_size(population) AS pop_bytes,
  pg_column_size(national_debt) AS debt_bytes
FROM test_integers;

Insight: Saving 2 bytes instead of 8 bytes per row adds up when you store millions of records.

Decimal Types and Precision

Use Cases: Financials, scientific measurements requiring exact values.

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  item TEXT,
  price NUMERIC(10, 2)  -- Up to 99,999,999.99
);

INSERT INTO payments (item, price)
VALUES
  ('Laptop', 75999.95),
  ('Mouse', 899.50),
  ('Software License', 14999.99);

-- Implicit vs. explicit cast:
SELECT
  price + 1.05 AS implicit_sum,
  price + CAST(1.05 AS NUMERIC(4,2)) AS explicit_sum
FROM payments;

Floating-Point Types and Trade-Offs

Use Cases: Sensor data, GIS distances where slight imprecision is acceptable.

CREATE TABLE sensors (
  id SERIAL,
  temperature REAL,
  pressure DOUBLE PRECISION
);

INSERT INTO sensors (temperature, pressure)
VALUES (34.6, 1013.25);

-- Benchmark simple SUM on float vs. numeric:
EXPLAIN ANALYZE
SELECT SUM(temperature) FROM sensors;

EXPLAIN ANALYZE
SELECT SUM(CAST(temperature AS NUMERIC(8,2))) FROM sensors;

Tip: Use EXPLAIN ANALYZE to compare performance.

Domain Types for Safety

Domains wrap a base type with constraints. For example, a percentage between 0 and 100:

CREATE DOMAIN percentage AS NUMERIC(5,2)
  CHECK (VALUE BETWEEN 0 AND 100);

CREATE TABLE survey_results (
  id SERIAL,
  satisfaction_percentage percentage
);

INSERT INTO survey_results (satisfaction_percentage)
VALUES (85.50);

Benefit: Enforces business rules at the type level.

Altering Column Types

Changing a column's type in production requires care to avoid data loss.

-- Change integer to bigint
ALTER TABLE test_integers
  ALTER COLUMN population TYPE BIGINT;

-- Convert float to numeric
ALTER TABLE sensors
  ALTER COLUMN temperature TYPE NUMERIC(6,2)
  USING ROUND(temperature::NUMERIC,2);

Advice: Use the USING clause for safe conversion.

Indexing Numeric Columns

• B-tree indexes work well for exact matches and range queries.
• GiST or SP-GiST indexes can help with nearest-neighbor searches on double precision (e.g., KNN).

CREATE INDEX idx_population ON test_integers (population);
CREATE INDEX idx_temperature ON sensors USING gist (temperature);

Comparing Precision vs. Speed

Recommendation: Use numeric when correctness is paramount; use double precision for analytics where speed matters.

Edge-Case Pitfalls

-- Overflow example for bigint
SELECT (9223372036854775807::bigint + 1);
-- ERROR: arithmetic overflow