PostgreSQL Data Types — Choosing the Right One

Prerequisites: a running PostgreSQL and psql — see Install PostgreSQL. This post pairs with Create Database & Tables — the schema we practice on here is the same little web shop used across the whole PostgreSQL series.

1Why Types Matter (The Type IS Your First Validation)

Before any application code runs — before Pydantic, before Zod, before your API even exists — PostgreSQL checks every value against the column's type. An INTEGER column will never hold "hello". A NUMERIC(10,2) price will never silently grow a 15th decimal. The type is a contract that the database enforces on every writer: your app, a teammate's script, a 3 AM manual fix in psql. Ang importante dito: the type is validation that cannot be bypassed.

And a wrong type haunts you. The classic horror story: someone stores money in FLOAT because "it's a number with decimals, right?". Months later, refund totals are off by a centavo, finance opens a ticket, and you learn the hard way that floats are approximate. See it yourself — this is one line of SQL:

SELECT 0.1::float + 0.2::float AS float_sum;

      float_sum
---------------------
 0.30000000000000004
(1 row)

That is not a PostgreSQL bug — it's how binary floating point works in every language. Now the same math with NUMERIC, PostgreSQL's exact decimal type:

SELECT 0.1::numeric + 0.2::numeric AS numeric_sum;

 numeric_sum
-------------
         0.3
(1 row)

Let's set up a playground so every example in this post is runnable. This is the compact version of the web-shop schema from Create Database & Tables:

CREATE TABLE customers (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  order_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT NOT NULL DEFAULT 'pending',
  ordered_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO customers (full_name, email, city) VALUES
  ('Maria Santos',   '[email protected]', 'Manila'),
  ('Juan dela Cruz', '[email protected]',  'Cebu'),
  ('Ana Reyes',      '[email protected]',   NULL);

INSERT INTO products (name, category, price, stock) VALUES
  ('Mechanical Keyboard', 'peripherals', 3499.00, 12),
  ('USB-C Hub',           'accessories', 1299.00, 30),
  ('27-inch Monitor',     'displays',    9990.00, 8);

INSERT INTO orders (customer_id, status) VALUES
  (1, 'pending'),
  (2, 'paid');

2Numbers: SMALLINT, INTEGER, BIGINT, NUMERIC, FLOAT

PostgreSQL gives you three integer sizes and two families of decimals. First, the integers — the difference is just how many bytes they use and how far they count:

Pick a size that fits — but know what happens when it doesn't. Integers don't wrap around or silently truncate in PostgreSQL; they fail loudly, which is exactly what you want:

SELECT 32767::smallint + 1::smallint AS boom;

ERROR:  smallint out of range

That error is your friend — it's a wrong number that never reached your data. If a counter might outgrow INTEGER (2.1 billion sounds like a lot until you log every click), start with BIGINT; changing later on a huge table means a slow full-table rewrite.

NUMERIC(precision, scale) stores exact decimal digits. Precision is total digits, scale is digits after the decimal point. Our price NUMERIC(10,2) holds up to 99,999,999.99 — 8 digits before the point, 2 after. Values with extra decimals get rounded to the scale, not rejected:

SELECT 19.999::numeric(10,2) AS rounded_to_scale;

 rounded_to_scale
------------------
            20.00
(1 row)

But blow past the precision and PostgreSQL refuses — again, loudly:

SELECT 1234567890.12::numeric(10,2) AS too_big;

ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.

Here's the money bug in aggregate form. Sum ten payments of ₱0.10 — a totally normal thing to do in an orders report:

SELECT SUM(0.10::float8)  AS float_total,
       SUM(0.10::numeric) AS exact_total
FROM generate_series(1, 10);

    float_total     | exact_total
--------------------+-------------
 0.9999999999999999 |        1.00
(1 row)

Ten dimes should be exactly one peso. FLOAT loses a quintillionth — invisible on one row, real money across a million rows, and enough to make WHERE total = 1.00 mysteriously match nothing.

So when is FLOAT / DOUBLE PRECISION genuinely fine? When the data is a measurement that was never exact to begin with: sensor readings, scientific values, ML feature vectors, physics simulations. Floats are smaller and faster for math-heavy workloads, and a temperature of 31.400000000000002°C hurts nobody. Exact data → NUMERIC. Approximate data → FLOAT. That one sentence settles 95% of number-type decisions.

3Text: TEXT vs VARCHAR(n) vs CHAR(n)

Here's the truth that surprises people coming from other databases: in PostgreSQL, TEXT and VARCHAR(n) are stored identically and perform identically. There is no speed bonus for declaring a length. The only difference is that VARCHAR(n) adds a length check. So the house rule across this whole series: use TEXT unless a maximum length is a real business rule.

When a length limit is the point, VARCHAR(n) enforces it at the database level. Say our warehouse scanner can only print 8-character SKUs:

ALTER TABLE products ADD COLUMN sku VARCHAR(8);

UPDATE products SET sku = 'KB-87-BLK' WHERE product_id = 1;

ALTER TABLE
ERROR:  value too long for type character varying(8)

'KB-87-BLK' is 9 characters — rejected before it could corrupt a label. Trim it and the update goes through:

UPDATE products SET sku = 'KB87-BLK' WHERE product_id = 1;
UPDATE products SET sku = 'HUB-USBC' WHERE product_id = 2;
UPDATE products SET sku = 'MON-27IN' WHERE product_id = 3;

SELECT product_id, name, sku FROM products ORDER BY product_id;

 product_id |        name         |   sku
------------+---------------------+----------
          1 | Mechanical Keyboard | KB87-BLK
          2 | USB-C Hub           | HUB-USBC
          3 | 27-inch Monitor     | MON-27IN
(3 rows)

And CHAR(n)? It pads every value with spaces to exactly n characters — a relic of fixed-width mainframe records. The padding behaves in genuinely confusing ways:

SELECT 'PH'::char(5)                AS code,
       octet_length('PH'::char(5)) AS stored_bytes,
       'PH'::char(5) || '!'        AS concatenated;

 code  | stored_bytes | concatenated
-------+--------------+--------------
 PH    |            5 | PH!
(1 row)

4BOOLEAN: Small Type, Strong Opinions

BOOLEAN stores true or false in a single byte. PostgreSQL is generous about what it accepts as input — all of these are valid true spellings:

SELECT true           AS kw,
       't'::boolean   AS letter,
       'yes'::boolean AS word,
       '1'::boolean   AS digit,
       'off'::boolean AS this_one_is_false;

 kw | letter | word | digit | this_one_is_false
----+--------+------+-------+-------------------
 t  | t      | t    | t     | f
(1 row)

Notice the output: psql always displays booleans as t and f, no matter which literal you typed in. Let's give products a feature flag the way we'd actually ship it:

ALTER TABLE products
  ADD COLUMN is_featured BOOLEAN NOT NULL DEFAULT false;

UPDATE products SET is_featured = true WHERE product_id = 1;

SELECT name, is_featured FROM products ORDER BY product_id;

        name         | is_featured
---------------------+-------------
 Mechanical Keyboard | t
 USB-C Hub           | f
 27-inch Monitor     | f
(3 rows)

5Dates & Times: TIMESTAMPTZ Is the Big One

This is the section that saves you a production incident. PostgreSQL has four everyday date/time types:

See the difference in one experiment. Insert the same wall-clock value into both types, then move timezones:

CREATE TABLE tz_demo (
  naive TIMESTAMP,
  aware TIMESTAMPTZ
);

SET timezone = 'UTC';
INSERT INTO tz_demo VALUES ('2026-07-18 20:00', '2026-07-18 20:00');

-- now pretend our session is a user in the Philippines
SET timezone = 'Asia/Manila';
SELECT naive, aware FROM tz_demo;

        naive        |         aware
---------------------+------------------------
 2026-07-18 20:00:00 | 2026-07-19 04:00:00+08
(1 row)

The aware column knows 20:00 UTC is 04:00 +08 the next morning in Manila. The naive column just parrots back digits — it has no idea what they mean, so it can't adapt. Every "our timestamps shifted by 8 hours after we moved servers" bug traces back to a naive TIMESTAMP.

SELECT CURRENT_DATE AS today,
       now()        AS this_exact_instant;

   today    |      this_exact_instant
------------+-------------------------------
 2026-07-18 | 2026-07-18 17:42:10.532104+08
(1 row)

(now() shows +08 because our session timezone is still Asia/Manila from the demo above.)

AT TIME ZONE answers "what did the clock on the wall say in city X at this instant?" — perfect for reports:

SELECT order_id,
       ordered_at,
       ordered_at AT TIME ZONE 'Asia/Manila' AS manila_wall_clock
FROM orders
ORDER BY order_id;

 order_id |          ordered_at           |     manila_wall_clock
----------+-------------------------------+----------------------------
        1 | 2026-07-18 17:05:44.120933+08 | 2026-07-18 17:05:44.120933
        2 | 2026-07-18 17:05:44.120933+08 | 2026-07-18 17:05:44.120933
(2 rows)

Note the result of AT TIME ZONE has no +08 suffix — it's a plain local wall-clock reading, which is exactly what a "time" column in a report should be.

SELECT now() + INTERVAL '7 days'          AS next_week,
       CURRENT_DATE + INTERVAL '30 days'  AS payment_due,
       now() - INTERVAL '1 hour 30 minutes' AS ninety_min_ago;

           next_week           |     payment_due     |        ninety_min_ago
-------------------------------+---------------------+-------------------------------
 2026-07-25 17:42:10.532104+08 | 2026-08-17 00:00:00 | 2026-07-18 16:12:10.532104+08
(1 row)

INTERVAL is also a column type in its own right — a rental duration, a subscription period. And one teaser for the road: date_trunc collapses timestamps to a boundary, the backbone of every "orders per month" report you'll ever write:

SELECT date_trunc('month', now()) AS month_bucket;

      month_bucket
------------------------
 2026-07-01 00:00:00+08
(1 row)

6UUID: IDs You Can Show the World

A UUID is a 128-bit identifier that looks like a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Since PostgreSQL 13, generating one needs no extension:

SELECT gen_random_uuid() AS fresh_uuid;

              fresh_uuid
--------------------------------------
 8f14c9a2-51d3-4e6b-9c07-2ab54f1e83d9
(1 row)

The classic pattern in real systems: keep a compact IDENTITY integer as the internal primary key, and add a UUID as the public identifier you expose in URLs and APIs:

ALTER TABLE customers
  ADD COLUMN public_id UUID NOT NULL DEFAULT gen_random_uuid();

SELECT customer_id, full_name, public_id FROM customers ORDER BY customer_id;

 customer_id |   full_name    |              public_id
-------------+----------------+--------------------------------------
           1 | Maria Santos   | 3c9e6f2a-8b41-4d5c-a7e9-15f2d8c04b6e
           2 | Juan dela Cruz | b7d21c48-06aa-4f3e-8d92-c5e7a1904f13
           3 | Ana Reyes      | 61f8ab05-92c4-47d1-b3a8-7e0c95d2ef4a
(3 rows)

Why not just UUIDs everywhere, or integers everywhere? Trade-offs:

7Arrays: A List Inside a Column

PostgreSQL lets any type become a list: TEXT[], INTEGER[], even TIMESTAMPTZ[]. The everyday use case is tags:

ALTER TABLE products
  ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';

-- two ways to write an array value:
UPDATE products
SET tags = ARRAY['mechanical', 'rgb', 'hot-swap']   -- constructor syntax
WHERE product_id = 1;

UPDATE products
SET tags = '{"usb-c", "7-port", "travel"}'          -- literal syntax
WHERE product_id = 2;

Both syntaxes produce the same thing. Querying "which products have this tag?" uses ANY — read it as "the value 'rgb' equals any element of tags":

SELECT name, tags
FROM products
WHERE 'rgb' = ANY (tags);

        name         |           tags
---------------------+---------------------------
 Mechanical Keyboard | {mechanical,rgb,hot-swap}
(1 row)

And when you need one row per element — say, to count tag popularity — unnest explodes the array (full treatment in a later post):

SELECT unnest(tags) AS tag
FROM products
WHERE product_id = 1;

    tag
------------
 mechanical
 rgb
 hot-swap
(3 rows)

8JSONB: Flexible Documents, Real Queries

Sometimes different rows legitimately need different fields. A keyboard has a switch type; a monitor has a panel type and refresh rate. Rather than 40 mostly-NULL columns, give the variable part of the row a JSONB column:

ALTER TABLE products
  ADD COLUMN attributes JSONB NOT NULL DEFAULT '{}';

UPDATE products
SET attributes = '{"switch": "red", "keys": 87, "backlight": true}'
WHERE product_id = 1;

UPDATE products
SET attributes = '{"panel": "IPS", "refresh_hz": 144, "backlight": true}'
WHERE product_id = 3;

Two operators pull values out, and the difference matters: -> returns JSONB (still JSON — you can chain deeper), while ->> returns text (ready to compare, display, or cast):

SELECT name,
       attributes ->> 'switch' AS switch_as_text,
       attributes ->  'keys'   AS keys_as_jsonb
FROM products
WHERE product_id = 1;

        name         | switch_as_text | keys_as_jsonb
---------------------+----------------+---------------
 Mechanical Keyboard | red            | 87
(1 row)

The containment operator @> asks "does this JSONB contain this shape?" — the idiomatic way to filter on document fields:

SELECT name, attributes
FROM products
WHERE attributes @> '{"backlight": true}';

        name         |                       attributes
---------------------+---------------------------------------------------------
 Mechanical Keyboard | {"keys": 87, "switch": "red", "backlight": true}
 27-inch Monitor     | {"panel": "IPS", "backlight": true, "refresh_hz": 144}
(2 rows)

Our products table has grown quite a wardrobe of types. Check the full picture:

\d products

                                Table "public.products"
   Column    |         Type         | Collation | Nullable |           Default
-------------+----------------------+-----------+----------+------------------------------
 product_id  | integer              |           | not null | generated always as identity
 name        | text                 |           | not null |
 category    | text                 |           | not null |
 price       | numeric(10,2)        |           | not null |
 stock       | integer              |           | not null | 0
 sku         | character varying(8) |           |          |
 is_featured | boolean              |           | not null | false
 tags        | text[]               |           | not null | '{}'::text[]
 attributes  | jsonb                |           | not null | '{}'::jsonb
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)

9Constrained Values: CHECK vs ENUM

Our orders.status is TEXT — which means nothing stops a buggy script from writing 'paidd'. Two tools lock a column to a fixed set of values. First, the CHECK constraint:

ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'));

-- now try to sneak in a bad value:
INSERT INTO orders (customer_id, status) VALUES (1, 'teleported');

ALTER TABLE
ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
DETAIL:  Failing row contains (3, 1, teleported, 2026-07-18 17:58:03.44182+08).

An ENUM creates a brand-new type whose only legal values are the ones you list. It reads beautifully in \d output and stores compactly:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

-- a new table could then declare:
--   status order_status NOT NULL DEFAULT 'pending'

-- adding a value later is easy...
ALTER TYPE order_status ADD VALUE 'refunded';

CREATE TYPE
ALTER TYPE

10Casting & Conversion: ::, CAST, to_char, to_date

Types are strict; casting is how you convert on purpose. PostgreSQL has two spellings — :: (the Postgres shorthand you've seen all post) and standard-SQL CAST():

SELECT '42'::integer          AS shorthand,
       CAST('42' AS integer)  AS standard_sql;

 shorthand | standard_sql
-----------+--------------
        42 |           42
(1 row)

A cast that can't work fails immediately with a clear message — validation again:

SELECT '3,499'::integer AS price;

ERROR:  invalid input syntax for type integer: "3,499"

(Yes — the thousands separator breaks it. Strip formatting before casting.) PostgreSQL also casts implicitly when it's safe and unambiguous: insert the string '2026-07-18' into a DATE column and it converts without a :: in sight. But implicit rules hide one classic trap — integer division:

SELECT 10 / 4            AS int_division,
       10 / 4.0          AS with_decimal,
       10::numeric / 4   AS explicit_cast;

 int_division |    with_decimal    |   explicit_cast
--------------+--------------------+--------------------
            2 | 2.5000000000000000 | 2.5000000000000000
(1 row)

10 / 4 is integer ÷ integer, so PostgreSQL stays in integer land and truncates to 2. One operand cast to NUMERIC promotes the whole expression. Every "average rating shows 3 instead of 3.7" bug is this.

Finally, the formatting pair: to_char renders dates/numbers as text for humans, and to_date / to_timestamp parse text using an explicit pattern — the safe way to ingest ambiguous formats like 18/07/2026:

SELECT to_char(now(), 'Mon DD, YYYY HH24:MI')   AS pretty,
       to_date('18/07/2026', 'DD/MM/YYYY')     AS parsed;

      pretty       |   parsed
-------------------+------------
 Jul 18, 2026 17:59 | 2026-07-18
(1 row)

11NULL: Not Zero, Not Empty, Not False

NULL works with every type, so it deserves a section in a types guide. NULL means "unknown" — not 0, not '', not false. And here's the mind-bender: NULL is not even equal to itself.

SELECT NULL = NULL   AS equals_itself,
       NULL IS NULL  AS is_null;

 equals_itself | is_null
---------------+---------
               | t
(1 row)

NULL = NULL returns… NULL (displayed as blank). Logic: "is one unknown equal to another unknown?" — unknown! That's why comparing against NULL always uses IS NULL / IS NOT NULL. And NULL infects everything it touches:

SELECT 100 + NULL       AS math_result,
       'hello ' || NULL AS concat_result;

 math_result | concat_result
-------------+---------------
             |
(1 row)

Both expressions collapse to NULL — this is called NULL propagation. It even changes what aggregates count. Remember Ana Reyes has no city:

SELECT count(*)    AS all_rows,
       count(city) AS rows_with_city
FROM customers;

 all_rows | rows_with_city
----------+----------------
        3 |              2
(1 row)

12Common Mistakes Recap (Learn Them Here, Not in Prod)

The four type mistakes we see most, condensed. You've met the first three — the fourth deserves a demo.

CREATE TABLE bad_dates (
  event       TEXT,
  happened_on TEXT          -- 😬 should be DATE
);

INSERT INTO bad_dates VALUES
  ('Product launch', '12/01/2025'),
  ('Big sale',       '03/15/2025'),
  ('Annual audit',   '01/02/2026');

SELECT event, happened_on
FROM bad_dates
ORDER BY happened_on;       -- "chronological", supposedly

     event      | happened_on
----------------+-------------
 Annual audit   | 01/02/2026
 Big sale       | 03/15/2025
 Product launch | 12/01/2025
(3 rows)

The 2026 audit sorts first because the string '01/…' is alphabetically smallest. No error, no warning — just silently wrong ordering in every report. With a real DATE column, ORDER BY is chronological, comparisons work, and malformed input like '13/45/2025' is rejected at the door.

13The Decision Guide: You're Storing X → Use Y

Bookmark this table. It compresses everything above into one lookup:

14Practice Exercises + What's Next

Time to make it stick. Imagine we're adding a co-working space booking system to our web shop's database. Work through these against your playground:

Choose a type for each column of a bookings table: an internal ID, a public ID for URLs, the guest's name, a 6-character room code, when the booking starts, how long it lasts, the price per hour, a paid/unpaid flag, a list of requested amenities, a free-form metadata payload from the booking widget, a status that's one of 'pending' / 'confirmed' / 'cancelled', and when the row was created. Write the full CREATE TABLE before peeking below.

Write one SELECT that shows the float error for summing twenty bookings of ₱0.10, next to the exact NUMERIC total.

Insert a booking starting '2026-08-01 09:00+08' and display it as a Manila wall-clock time using AT TIME ZONE.

Add a CHECK constraint for the three statuses, then try inserting 'maybe' and confirm you get the constraint error.

Find all bookings that requested a projector.

Expected answer for exercise 1 (yours may differ in names, not in types):

CREATE TABLE bookings (
  booking_id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id      UUID NOT NULL DEFAULT gen_random_uuid(),
  guest_name     TEXT NOT NULL,
  room_code      TEXT NOT NULL CHECK (char_length(room_code) = 6),
  starts_at      TIMESTAMPTZ NOT NULL,
  duration       INTERVAL NOT NULL,
  price_per_hour NUMERIC(10,2) NOT NULL CHECK (price_per_hour >= 0),
  is_paid        BOOLEAN NOT NULL DEFAULT false,
  amenities      TEXT[] NOT NULL DEFAULT '{}',
  metadata       JSONB NOT NULL DEFAULT '{}',
  status         TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'confirmed', 'cancelled')),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

Expected answers for exercises 2, 3, and 5:

-- Exercise 2: the money rule
SELECT SUM(0.10::float8)  AS float_total,
       SUM(0.10::numeric) AS exact_total
FROM generate_series(1, 20);

-- Exercise 3: timezone check
INSERT INTO bookings (guest_name, room_code, starts_at, duration, price_per_hour)
VALUES ('Carlo Garcia', 'ROOM-A', '2026-08-01 09:00+08', INTERVAL '2 hours', 350.00);

SELECT guest_name,
       starts_at AT TIME ZONE 'Asia/Manila' AS manila_start
FROM bookings;

-- Exercise 5: amenities array
SELECT guest_name, room_code
FROM bookings
WHERE 'projector' = ANY (amenities);

    float_total     | exact_total
--------------------+-------------
 2.0000000000000004 |        2.00
(1 row)

  guest_name  |    manila_start
--------------+---------------------
 Carlo Garcia | 2026-08-01 09:00:00
(1 row)

(Exercise 4 should reward you with ERROR: new row for relation "bookings" violates check constraint — if it doesn't, the constraint isn't attached. Exercise 5 returns (0 rows) until you insert a booking with amenities = ARRAY['projector', 'whiteboard'] — try it.)

Recap: the column type is the validation layer nobody can bypass. NUMERIC for money, TEXT for strings, TIMESTAMPTZ for moments in time, BOOLEAN NOT NULL for flags, UUID for public IDs, arrays and JSONB for the genuinely flexible parts — and a CHECK constraint wherever a value set is fixed. Choose deliberately once, and the database quietly enforces your decision for years.