Data Types in PostgreSQL

Numeric, Text, Date and More

PostgreSQL has one of the richest type systems of any relational database.

Numeric Types

CREATE TABLE products (
  id        SERIAL PRIMARY KEY,
  price     NUMERIC(10,2),
  weight    REAL,
  quantity  INTEGER
);

SERIAL is a convenient auto-incrementing integer (internally a sequence). NUMERIC(p,s) stores exact decimal values, perfect for money.

Text and Character Types

• VARCHAR(n): text limited to n characters.
• TEXT: unlimited length text.
• CHAR(n): fixed length, padded with spaces.

JSON and JSONB

CREATE TABLE events (
  id      SERIAL PRIMARY KEY,
  payload JSONB
);
INSERT INTO events (payload) VALUES
  (\'{"user": "priya", "action": "login"}\'::jsonb);

SELECT payload ->> \'user\' AS username FROM events;

jsonb stores JSON in a binary format, supports indexes and fast query operators. This is a killer feature of PostgreSQL.

Arrays and Enums

CREATE TABLE posts (
  id   SERIAL PRIMARY KEY,
  tags TEXT[]
);

CREATE TYPE order_status AS ENUM (\'new\', \'paid\', \'shipped\');

Key Points

• SERIAL auto-increments IDs via sequences.
• NUMERIC is exact for currency; use it instead of FLOAT for money.
• jsonb brings document power into a relational database.
• Arrays and custom ENUM types are first-class features.