3.1 Relational Data Types & Table Design

3.1 Relational Data Types & Table Design

[!NOTE] Exam Blueprint Focus: The EDB PostgreSQL Associate exam heavily tests your practical knowledge of PostgreSQL data types, storage trade-offs, internal representations, and modern schema design best practices. Expect questions contrasting exact numeric types with floating-point representations, the storage equivalence of text and varchar, the precise UTC conversion mechanics of timestamptz, the structural indexing differences between json and jsonb, and the behavioral differences between legacy SERIAL pseudo-types and SQL-standard IDENTITY columns.

Designing robust, high-performance database schemas in PostgreSQL requires selecting the most appropriate native data types. PostgreSQL offers a rich set of built-in data types, ranging from fundamental relational primitives to complex structured and semi-structured types. Choosing the right data type ensures domain integrity, minimizes storage footprints on disk and in shared buffers, and enables query optimizer indexing paths.

---

Numeric Data Types: Integers, Exact Precision & Floating-Point

PostgreSQL categorizes numeric data into three distinct architectural classes: fixed-width integers, arbitrary-precision exact decimals, and variable-precision IEEE floating-point numbers.

1. Fixed-Width Integer Types

Integers store whole numbers without fractional components. PostgreSQL implements three signed two's-complement integer types:

• smallint: 2 bytes (16 bits), storing signed values from -32,768 to +32,767. Ideal for small enumerated codes, days of the week, or small geographic units.
• integer (or int): 4 bytes (32 bits), storing signed values from -2,147,483,648 to +2,147,483,647. This is the standard, general-purpose choice for counters and foreign keys.
• bigint: 8 bytes (64 bits), storing signed values from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. Mandatory for large surrogate keys, high-frequency transaction logs, and tables expected to exceed two billion rows.

CREATE TABLE sensor_readings (
    sensor_id    smallint,
    reading_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_cycle integer
);

2. Arbitrary-Precision Exact Numbers: numeric and decimal

The numeric type (synonymous with decimal) stores numbers with exact precision up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point:

• Syntax: numeric(precision, scale), where precision is the total count of significant decimal digits across the entire number, and scale is the count of digits in the fractional component to the right of the decimal point.
• Exactness: Arithmetic on numeric values is implemented in software rather than CPU hardware registers. It produces zero rounding errors, making it mandatory for monetary, banking, accounting, and legal computations.
• Storage Overhead: Unlike fixed-width integers, numeric is a variable-length type. PostgreSQL stores numeric as a sequence of base-10,000 digits (2 bytes per digit group), adding a 3-to-8 byte header per value. Consequently, calculations on numeric are significantly slower than integer or floating-point operations.

-- Stores values up to 99999999.99 with exact cent precision
CREATE TABLE invoices (
    invoice_id   bigint PRIMARY KEY,
    total_amount numeric(10, 2) NOT NULL
);

3. Floating-Point Numbers: real and double precision

Floating-point numbers conform to the IEEE 754 standard for binary floating-point arithmetic:

• real: 4 bytes (single precision), providing at least 6 decimal digits of precision with a range of approximately $1E-37$ to $1E+37$.
• double precision (or float8): 8 bytes (double precision), providing at least 15 decimal digits of precision with a range of approximately $1E-307$ to $1E+308$.
• Inexact Arithmetic: Floating-point values cannot represent all decimal fractions exactly (e.g., $0.1$ has an infinite repeating expansion in binary). Arithmetic operations accumulate inexact rounding artifacts. Use floating-point types exclusively for scientific data, sensor telemetry, and geometric coordinates where calculation speed outweighs penny-perfect decimal accuracy.

---

Character Data Types: char(n), varchar(n), and text

PostgreSQL supports three core character types: character(n) (or char(n)), character varying(n) (or varchar(n)), and text.

Storage Architecture and TOAST

All three character types share the exact same underlying PostgreSQL storage engine structure known as varlena (variable-length array). A varlena structure consists of a 1-byte or 4-byte header describing the length of the string, followed by the raw byte data encoded in the database's character set (such as UTF-8):

• If a text value exceeds approximately 2 KB (one-fourth of an 8 KB page), PostgreSQL automatically compresses the value and, if necessary, moves it out-of-line into a separate table storage area called TOAST (The Oversized-Attribute Storage Technique). The maximum field size for any character value is 1 GB.

Behavioral Differences

1. char(n): Fixed-length character string. If the stored string contains fewer than $n$ characters, PostgreSQL automatically pads the remainder with trailing space characters. When retrieving or comparing char(n) strings, these trailing spaces are ignored semantically, but they remain physically stored on disk.
2. varchar(n): Variable-length character string with an explicit maximum length cap of $n$ characters. No trailing spaces are padded.
3. text (or unbounded varchar): Variable-length character string without any artificial length limit.

[!IMPORTANT] The PostgreSQL Performance Myth: In many relational database systems (such as legacy MySQL or Microsoft SQL Server), char(n) offers performance advantages over variable-length strings because fixed offsets can be calculated statically. In PostgreSQL, this is completely false. Because all three types use the exact same varlena storage mechanism, there is zero performance difference between text and varchar(n).

Furthermore, char(n) is actually the slowest of the three types because PostgreSQL must expend CPU cycles calculating, padding, and stripping trailing whitespace during storage and comparison operations. In modern PostgreSQL schema design, prefer text or unbounded varchar combined with CHECK constraints when length restrictions are necessary.

---

Temporal Data Types & Timezone Normalization

Handling dates, times, and durations accurately requires understanding how PostgreSQL handles time zones.

Date and Time Primitives

• date: 4 bytes, storing calendar dates from 4713 BC to 5874897 AD with 1-day resolution.
• time [without time zone]: 8 bytes, storing the time of day from 00:00:00.000000 to 24:00:00.000000 with microsecond resolution (up to 6 digits).
• interval: 16 bytes, storing a span of time (e.g., '1 year 2 months 3 days 4 hours 30 seconds'). Intervals can be added to or subtracted from dates and timestamps.

timestamp vs. timestamptz

PostgreSQL provides two primary timestamp variants:

1. timestamp without time zone (shorthand timestamp): 8 bytes, storing date and time (year, month, day, hour, minute, second, microsecond). It does not know or care about time zones. If you insert '2026-09-06 14:30:00', it stores and outputs exactly that value, regardless of the client session's timezone setting.
2. timestamp with time zone (shorthand timestamptz): 8 bytes, storing date and time normalized to UTC.

How timestamptz Works Internally

A frequent source of confusion on certification exams is how timestamptz stores data:

• PostgreSQL DOES NOT store the client's timezone offset in the column!
• When a client transmits a timestamp string with an offset (e.g., '2026-09-06 14:30:00-04'), PostgreSQL immediately converts the timestamp to Coordinated Universal Time (UTC) and stores it as an 8-byte integer representing microseconds since January 1, 2000, UTC.
• When a client queries the column, PostgreSQL reads the UTC value and converts it into the timezone specified by the client's active session parameter TimeZone (e.g., SET TimeZone = 'America/New_York'; or SET TimeZone = 'Europe/London';).

-- Session 1: UTC client
SET TimeZone = 'UTC';
CREATE TABLE event_log (event_time timestamptz);
INSERT INTO event_log VALUES ('2026-09-06 12:00:00+00');

-- Session 2: New York client (-04:00 in daylight saving)
SET TimeZone = 'America/New_York';
SELECT event_time FROM event_log;
-- Output: '2026-09-06 08:00:00-04'

---

Boolean, UUID & Structured Types

The boolean Type

PostgreSQL implements a true 1-byte boolean type supporting three-valued logic (TRUE, FALSE, and NULL / unknown). Accepted input literals include:

• True literals: 't', 'true', 'y', 'yes', '1', TRUE
• False literals: 'f', 'false', 'n', 'no', '0', FALSE

Universally Unique Identifier (uuid)

The uuid type occupies 16 bytes (128 bits), conforming to RFC 4122. It provides much greater collision resistance and distribution than 4-byte integers when generating keys in distributed systems. Since PostgreSQL 13, the cryptographically secure random UUID generator function gen_random_uuid() is built into the core engine without requiring external extensions:

CREATE TABLE distributed_accounts (
    account_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email      text NOT NULL UNIQUE
);

Semistructured Documents: json vs. jsonb

PostgreSQL features world-class native JSON support through two data types:

1. json (Textual JSON): Stores an exact verbatim copy of the input text, including extraneous whitespace, indentation, and key ordering. It also preserves duplicate object keys. Every time a query reads a field from a json column, PostgreSQL must completely re-parse the text string.
2. jsonb (Decomposed Binary JSON): Parses the JSON text upon input and stores it in a decomposed binary format. Extraneous whitespace is stripped, object keys are sorted and deduplicated (if duplicate keys are provided, only the last value is retained), and numbers are converted to binary numeric representations.

Indexing JSONB with GIN

While jsonb has a slightly higher ingestion cost due to binary conversion overhead, it is vastly superior for querying. Crucially, jsonb supports Generalized Inverted Indexes (GIN), enabling sub-millisecond document lookups:

CREATE TABLE customer_profiles (
    user_id  bigint PRIMARY KEY,
    metadata jsonb NOT NULL
);

-- Create GIN index on entire JSONB document
CREATE INDEX idx_profiles_metadata ON customer_profiles USING GIN (metadata);

-- Fast query using JSON containment operator (@>)
SELECT * FROM customer_profiles WHERE metadata @> '{"tier": "enterprise", "active": true}';

-- Path-specific operators:
-- '->' extracts JSON object field (returns jsonb)
-- '->>' extracts JSON object field as raw text
SELECT metadata->'tier' AS json_val, metadata->>'tier' AS text_val FROM customer_profiles;

Native Arrays

PostgreSQL allows columns to be defined as multidimensional arrays of any valid base data type, such as integer[] or text[]:

CREATE TABLE articles (
    article_id bigint PRIMARY KEY,
    tags       text[] DEFAULT '{}'
);

-- Array constructor and literals
INSERT INTO articles VALUES (1, ARRAY['postgresql', 'dba', 'sql']);
INSERT INTO articles VALUES (2, '{"cloud", "performance"}');

-- Querying array elements (PostgreSQL uses 1-based indexing by default!)
SELECT tags[1] FROM articles WHERE article_id = 1; -- Returns 'postgresql'

-- Array search operators
SELECT * FROM articles WHERE 'dba' = ANY(tags);
SELECT unnest(tags) FROM articles WHERE article_id = 1; -- Expands array to relational rows

---

Primary Key Generation: SERIAL vs. Modern IDENTITY

Surrogate primary keys require monotonic sequence generation. Historically, PostgreSQL applications relied on SERIAL pseudo-types. Modern applications use the SQL:2003 standard IDENTITY column syntax.

+-----------------------------------------------------------------------------+
|                   SERIAL Pseudo-Type Architecture (Legacy)                  |
|   CREATE TABLE tbl (id SERIAL PRIMARY KEY);                                 |
|     ├── Creates independent sequence: tbl_id_seq                            |
|     ├── Sets column default: nextval('tbl_id_seq'::regclass)                |
|     └── Sets sequence ownership: tbl_id_seq OWNED BY tbl.id                 |
|   * Flaw: Permits silent manual overrides; sequence easily falls out of sync!|
+-----------------------------------------------------------------------------+
                                      VS
+-----------------------------------------------------------------------------+
|               GENERATED AS IDENTITY Architecture (SQL Standard)             |
|   CREATE TABLE tbl (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);    |
|     ├── Integrates sequence directly into table schema metadata (pg_class)  |
|     ├── Enforces strict system generation; blocks manual user inserts       |
|     └── Permits manual inserts ONLY with OVERRIDING SYSTEM VALUE            |
+-----------------------------------------------------------------------------+

1. Legacy SERIAL Pseudo-Types

The SERIAL family (smallserial [2 bytes], serial [4 bytes], bigserial [8 bytes]) is not a true data type. It is syntactic sugar that causes PostgreSQL to execute three separate actions under the hood:

1. Creates an independent sequence object named <tablename>_<colname>_seq.
2. Sets the column data type to integer (or smallint/bigint).
3. Assigns the column default expression to nextval('<tablename>_<colname>_seq'::regclass).
4. Marks the sequence as OWNED BY the table column so that dropping the table or column drops the sequence.

Weaknesses of SERIAL:

• Users can bypass the sequence by explicitly specifying a value in an INSERT statement (INSERT INTO tbl (id) VALUES (42)). PostgreSQL accepts this without warning.
• Subsequent sequence calls (nextval()) will eventually generate 42, resulting in a duplicate key collision violation (ERROR: duplicate key value violates unique constraint).

2. SQL-Standard IDENTITY Columns (PostgreSQL 10+)

Modern PostgreSQL supports the SQL standard IDENTITY specification, which binds sequence management directly into column metadata:

• GENERATED ALWAYS AS IDENTITY: PostgreSQL guarantees that the sequence generates the value. If an application attempts to execute INSERT INTO tbl (id, ...) VALUES (10, ...), PostgreSQL raises an immediate error: ERROR: cannot insert a non-DEFAULT value into column "id" To override this protection, the user must explicitly declare OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT AS IDENTITY: PostgreSQL generates sequence values when the column is omitted or defaulted, but gracefully accepts user-supplied values if explicitly provided.

-- Modern best practice: standard identity column
CREATE TABLE order_records (
    order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL,
    placed_at    timestamptz NOT NULL DEFAULT clock_timestamp()
);

-- Attempting to manually insert an ID fails under GENERATED ALWAYS:
-- INSERT INTO order_records (order_id, customer_id) VALUES (999, 101); -- ERROR!

-- Successful manual override using explicit clause:
INSERT INTO order_records (order_id, customer_id) 
OVERRIDING SYSTEM VALUE 
VALUES (999, 101);