Numeric and String Types

PostgreSQL provides a comprehensive set of built-in data types. For integers, SMALLINT (2 bytes), INTEGER (4 bytes), and BIGINT (8 bytes) cover different size ranges, while SERIAL and BIGSERIAL are auto-incrementing integer types. For decimal precision, NUMERIC(precision, scale) stores exact values without floating-point rounding errors and is the correct choice for monetary values. REAL and DOUBLE PRECISION are IEEE 754 floating-point types suitable for scientific computations where small rounding errors are acceptable. For text, VARCHAR(n) stores strings up to n characters, TEXT stores strings of any length, and CHAR(n) pads shorter strings to exactly n characters. PostgreSQL also supports ENUM types, which restrict a column to a predefined set of values stored efficiently as integers internally.

Example

-- Numeric types
CREATE TABLE numeric_examples (
  small_num   SMALLINT,               -- -32768 to 32767
  regular_num INTEGER,                -- ~2.1 billion
  large_num   BIGINT,                 -- ~9.2 * 10^18
  auto_id     SERIAL,                 -- auto-increment INTEGER
  big_auto    BIGSERIAL,              -- auto-increment BIGINT

  -- Exact decimal (best for money)
  price       NUMERIC(10, 2),         -- up to 99999999.99
  tax_rate    NUMERIC(5, 4),          -- e.g. 0.0875

  -- Floating point (approximate)
  ratio       DOUBLE PRECISION,
  score       REAL
);

-- String types
CREATE TABLE string_examples (
  code        CHAR(3),                -- fixed length, padded with spaces
  username    VARCHAR(50),            -- variable, max 50 chars
  bio         TEXT,                   -- unlimited length

  -- Enum type
  status      TEXT CHECK (status IN ('active', 'inactive', 'banned'))
);

-- Named ENUM type
CREATE TYPE mood AS ENUM ('happy', 'neutral', 'sad');
ALTER TABLE users ADD COLUMN current_mood mood DEFAULT 'neutral';

-- Format numbers
SELECT TO_CHAR(1234567.89, 'FM$999,999,999.00');

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned