PostgreSQL Data Types

What we're doing: Show the exact-vs-approximate difference directly by comparing 0.1 + 0.2 under numeric and double precision.

SELECT (0.1::double precision + 0.2::double precision) AS float_sum,
       (0.1::numeric + 0.2::numeric) AS numeric_sum,
       (0.1::double precision + 0.2::double precision = 0.3) AS float_equals_exact,
       (0.1::numeric + 0.2::numeric = 0.3) AS numeric_equals_exact;

1

double precision arithmetic — IEEE 754 cannot represent 0.1 or 0.2 exactly in binary.

2

numeric arithmetic — stores and computes the exact decimal value, no representation error.

float_sum          | numeric_sum | float_equals_exact | numeric_equals_exact
--------------------+-------------+---------------------+----------------------
0.30000000000000004 |        0.30 | f                   | t

Why this works: double precision stores numbers in binary floating-point, and 0.1 and 0.2 have no exact finite binary representation (the same way 1/3 has no exact finite decimal representation) — the stored values are already tiny approximations before any arithmetic even happens, so their sum is a slightly-off approximation too. numeric stores the exact decimal digits directly rather than converting to binary, so 0.1 + 0.2 in numeric arithmetic is genuinely, exactly 0.3, with no representation error at any step.

What we're doing: Store the same short value as text, varchar(10) and char(10), and see how char pads it while the others do not.

CREATE TABLE demo (t TEXT, v VARCHAR(10), c CHAR(10));
INSERT INTO demo VALUES ('hi', 'hi', 'hi');

SELECT t, v, c, length(t) AS t_len, length(v) AS v_len, length(c) AS c_len,
       octet_length(c) AS c_bytes
FROM demo;

2

'hi' is inserted identically into all three columns.

4–5

length() reports 2 for all three, since PostgreSQL's length() trims char(n)'s trailing padding — but octet_length shows the padded column actually occupies 10 bytes on disk.

t  | v  | c          | t_len | v_len | c_len | c_bytes
---+----+------------+-------+-------+-------+--------
hi | hi | hi         |     2 |     2 |     2 |     10

Why this works: char(10) physically stores 'hi' followed by 8 trailing spaces, always padding to exactly the declared length regardless of the actual content — but PostgreSQL's length() and string comparison functions are defined to disregard char(n)'s trailing padding, which is why length(c) still reports 2 even though octet_length(c) reveals the true 10-byte storage. text and varchar(10) never pad at all; 'hi' occupies exactly 2 bytes of content in both.

What we're doing: Create a table exercising several of these types at once, then query the array, JSONB and range columns.

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tags TEXT[],
    metadata JSONB,
    active_period DATERANGE
);

INSERT INTO events (tags, metadata, active_period)
VALUES (ARRAY['sale', 'summer'], '{"discount": 20}'::jsonb, daterange('2026-06-01', '2026-06-30'));

SELECT 'sale' = ANY(tags) AS has_sale_tag,
       metadata->>'discount' AS discount,
       active_period @> '2026-06-15'::date AS covers_june_15
FROM events;

8

gen_random_uuid() generates the id client-independently, no shared sequence needed.

10

= ANY(tags) checks array membership — the array-equivalent of IN against a subquery.

11

->> extracts a JSONB value as text.

12

@> is the range containment operator — is this date inside the range?

has_sale_tag | discount | covers_june_15
--------------+----------+---------------
t             | 20       | t

Why this works: Each type brings its own operator family suited to its shape: = ANY(array) tests element membership without a join, ->> navigates JSONB without parsing text on every access (JSONB is stored pre-parsed), and @> tests range containment as a single indexable operation instead of two separate comparisons (start <= date AND date <= end) that the planner would have to reason about independently.

What we're doing: Insert the same literal timestamp value as both timestamp and timestamptz, then change the session timezone and observe what changes.

CREATE TABLE demo (plain TIMESTAMP, tz TIMESTAMPTZ);

SET TIME ZONE 'UTC';
INSERT INTO demo VALUES ('2026-06-01 12:00:00', '2026-06-01 12:00:00');

SELECT plain, tz FROM demo;

SET TIME ZONE 'America/New_York';
SELECT plain, tz FROM demo;

3

Insert happens while the session timezone is UTC — timestamptz interprets the literal as 12:00 UTC and stores that instant.

8–9

Session timezone changes to America/New_York (UTC-4 in June); querying the same stored rows again shows the difference.

plain               | tz
---------------------+------------------------
2026-06-01 12:00:00 | 2026-06-01 12:00:00+00
(1 row)

plain               | tz
---------------------+------------------------
2026-06-01 12:00:00 | 2026-06-01 08:00:00-04
(1 row)

Why this works: plain (timestamp) shows 12:00:00 both times, because it never stored any timezone context — it is simply displaying the same wall-clock numbers it was given, regardless of the session's timezone setting. tz (timestamptz) shows 08:00:00-04 the second time, not because the stored value changed, but because it is displaying the exact same UTC instant (12:00 UTC) converted into the new session timezone (UTC-4) — 12:00 UTC and 08:00 EDT are the identical real-world moment.

What we're doing: Show money silently rounding a value to its locale's fractional digits, where numeric preserves the exact value entered.

SET lc_monetary = 'en_US.UTF-8';

SELECT 19.999::money AS as_money,
       19.999::numeric(10, 3) AS as_numeric;

3

money rounds to whatever fractional precision the active locale specifies for currency (2 decimal places for en_US).

4

numeric(10, 3) keeps exactly the precision the schema explicitly declared — no locale involvement.

as_money | as_numeric
---------+-----------
$20.00   |     19.999

Why this works: money always formats and rounds according to lc_monetary, which is a session/server locale setting rather than a per-column schema decision — so the same literal value produces different stored precision depending on server configuration that has nothing to do with the application's actual business rules. numeric(10, 3) is entirely explicit: the schema itself declares "3 decimal places," independent of any locale setting, which is portable across servers, environments, and PostgreSQL versions in a way money's locale dependency is not.

What we're doing: Show text-to-integer requiring an explicit cast, and a WHERE clause comparing a text literal against an integer column to see how PostgreSQL resolves it.

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders (customer_id) VALUES (42);

SELECT '42'::integer + 1;
-- 43

SELECT * FROM orders WHERE customer_id = '42';
-- works: '42' is implicitly cast to integer here, since = requires matching operand types

4

An explicit cast is required to turn text '42' into an integer before + can apply.

7

PostgreSQL resolves customer_id = '42' by casting the text literal '42' to integer to match customer_id's type — legal because integer has an assignment cast from text literals in this context, unlike arbitrary text-to-integer arithmetic.

?column?
--------
      43
(1 row)

id | customer_id
---+------------
 1 |          42
(1 row)

Why this works: PostgreSQL resolves an unknown-type string literal like '42' against the type it is being compared to, which is why customer_id = '42' works without an explicit ::integer — the literal has no fixed type yet at parse time, and the comparison operator resolution picks integer to match customer_id. This is different from casting an already-typed text value or column, which always requires an explicit cast, and it is why the analogous case with a text column instead of a literal behaves very differently (see the mistake below).

What we're doing: Compare a JSONB-based query against the equivalent logic expressed with only portable types, to see the practical difference in expressiveness.

CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO events (data) VALUES ('{"type": "click", "target": "button"}');

SELECT * FROM events WHERE data @> '{"type": "click"}';
-- one indexable, native containment query

-- portable equivalent: a TEXT column and LIKE, or a fully normalized schema
CREATE TABLE events_portable (id SERIAL PRIMARY KEY, event_type VARCHAR(50), target VARCHAR(50));
SELECT * FROM events_portable WHERE event_type = 'click';

4

@> is a native JSONB containment operator, GIN-indexable, that most other engines have no direct equivalent for.

7–8

The portable version requires deciding every JSON key's shape upfront as real columns — more rigid, but runs unmodified on nearly any relational database.

id | data
---+----------------------------------------
 1 | {"type": "click", "target": "button"}
(1 row)

Why this works: JSONB's containment query expresses "does this document include these key-value pairs" directly and can use a GIN index to answer it efficiently even against millions of rows with varying, unpredictable shapes — the portable version has to commit to a fixed schema ahead of time, which is more rigid but does not depend on any PostgreSQL-specific feature to run. Neither approach is universally correct; the JSONB version is the better engineering choice specifically when the data's shape is genuinely variable and PostgreSQL is not expected to change.