Common Mistakes When Choosing Types in PostgreSQL

← Back to the section

The database schema is the foundation. Pick the wrong types and problems pile up unnoticed: data loses its meaning after a server move, money drifts by a few cents, indexes stop working, and adding a value to an enumeration becomes a multi-release task.

Below are the mistakes that show up most often.

varchar(255) out of habit

In MySQL and Oracle the string length affects storage, so developers who came from those databases bring varchar(255) into PostgreSQL.

Here it buys nothing. text and varchar(n) are stored identically — the length in varchar(n) is just a CHECK, not an optimization.

The right way: text for strings with no business limit on length. If there is a limit, write it explicitly: varchar(20) for a country code, not a magic 255.

timestamp without a time zone for business time

timestamp stores a "bare" time with no zone attached. A year later nobody remembers which zone the server ran in when the row was written, and an order placed at 23:30 on Friday becomes a Saturday order after the service moves to another host.

The right way: timestamptz. The name misleads: the zone itself is not stored. Inside there is only an instant in UTC, and on read the database shows it in the session's zone. The instant is fixed, and moving the server spoils nothing. If the original zone matters — at what local time the customer ordered — keep it in a separate column.

varchar(36) for UUID

A UUID as a string takes 36 characters with hyphens plus a length byte, there is no format check, and comparison goes letter by letter. 'ABCD-...' and 'abcd-...' sit side by side, and to the database they are two different values:

live example

SELECT count(*) FILTER (WHERE id = 'ord-01') AS lower_hit,
       count(*) FILTER (WHERE id = 'ORD-01') AS upper_hit
FROM orders

The same identifier in another case is not found. With the uuid type that cannot happen: '0F9A…'::uuid and '0f9a…'::uuid are one value.

The right way: uuid in PostgreSQL is 16 bytes, keeps no hyphens internally, and validates on write.

float / real / double for money

Floating-point numbers cannot represent most decimal fractions exactly. On one operation that goes unnoticed; over a long chain of calculations the error accumulates and surfaces at reconciliation with the bank. A short check:

live example

SELECT 0.1::float8 + 0.2::float8               AS float_sum,
       0.1::numeric(19,4) + 0.2::numeric(19,4) AS numeric_sum,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_eq

On the left — 0.30000000000000004, on the right — exactly 0.3000, and comparing with 0.3 in float returns false.

The right way: numeric(precision, scale) — exact decimal arithmetic: numeric(19, 4) for an amount in rubles.

serial / bigserial in a new schema

serial and bigserial are syntactic sugar from early versions of PostgreSQL. They create a sequence and attach it to the column in a non-obvious way: privileges on it are granted separately from the table, and nothing stops the application from inserting its own id past the sequence — which then hands out numbers already taken.

The right way: since PostgreSQL 10, GENERATED ALWAYS AS IDENTITY. The behavior is the same, but the link to the column is explicit and survives a dump.

-- deprecated variant
id bigserial PRIMARY KEY

-- modern variant
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

smallint 0/1 or char(1) Y/N instead of boolean

This shows up in schemas migrated from databases without boolean. PostgreSQL has it — one byte, convenient in SQL expressions, readable in queries.

The right way: boolean. WHERE is_active = true reads without decoding.

PG ENUM for a frequently changing list

An enumeration via CREATE TYPE ... AS ENUM works fine while the list is stable. But a value cannot be removed from a PG ENUM — only added (renaming is allowed: ALTER TYPE ... RENAME VALUE), and attributes such as a display name or a sort order cannot be added.

The right way: an enumeration that can grow, be renamed or carry attributes belongs in a reference table with an FK. A bit more code, and the flexibility pays off.

JSONB as a "flexible schema" for core fields

JSONB is convenient, so it gets used where plain columns belong: metadata->>'email' instead of just email. Type safety is gone, indexes get harder, required fields are not enforced.

The right way: a field that is filtered or sorted on is a column. JSONB is for polymorphic data (different attributes on different rows), optional fields and rarely read JSON documents.

An array where a table should be

Storing order line items in jsonb[] or int[] looks convenient: everything in one row. But that's a table flipped on its side — no FK on array elements, no attributes on an element, updating one is awkward.

The right way: objects with identity (line items, participants, attachments) go into their own table with an FK. An array fits only scalars without identity: tags, locale codes, lists of strings.

valid_from / valid_to instead of a range type

Two separate columns look obvious, but it's a trap: without complex triggers overlapping intervals cannot be forbidden in the database, and two concurrent inserts slip past a check in the code anyway.

The right way: PostgreSQL has range types — tstzrange, daterange, int4range and others. With EXCLUDE USING gist they guarantee non-overlap in the database:

ALTER TABLE price_periods
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (product_id WITH =, valid_period WITH &&);

One detail: CREATE EXTENSION btree_gist comes first — without it the database cannot check plain equality on product_id inside a spatial index and refuses to create the constraint.

The money type

money is tied to the session locale — the same value reads differently depending on the settings, and there is no currency code in it.

The right way: numeric(p, s) for the amount plus a separate currency char(3) column (ISO 4217 code, RUB, USD).

A type without a time zone on the application side

Even when the schema says timestamptz, the driver may send a value with no zone attached — and PostgreSQL reads it in the session's zone. On a UTC server and on a developer's laptop with a local zone the same line of code lands different values. The same substitution without a database, across three zones:

live example

import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;

public class ZoneDemo {
    public static void main(String[] args) {
        LocalDateTime naive = LocalDateTime.parse("2026-01-31T23:30:00");
        for (String zone : new String[] {"UTC", "Europe/Moscow", "Asia/Novosibirsk"}) {
            System.out.println(zone + " -> " + naive.atZone(ZoneId.of(zone)).toInstant());
        }
        OffsetDateTime exact = OffsetDateTime.parse("2026-01-31T23:30:00+03:00");
        System.out.println("with zone -> " + exact.toInstant());
    }
}

The same string gives three different instants: 23:30, 20:30 and 16:30 UTC. A value that carries a zone is the same everywhere.

The right way: use a type with a time zone on the application side:

• Java: Instant or OffsetDateTime (not LocalDateTime)
• Go: time.Time (always carries a zone)
• Node.js: pg sends a Date object with its offset, no extra settings for writes
• Python: datetime with tzinfo (not a "naive" datetime)

Direct calls to system time and the UUID generator in code

When production code calls time.Now(), Instant.now() or uuid.New() directly, tests turn non-deterministic: the time and the identifier cannot be pinned down without platform-level mocks.

The right way: wrap them in a service layer — ClockService, UuidGenerator or an equivalent — and substitute a deterministic implementation in tests.

UUID v4 for a primary key

UUID v4 is fully random. On insert PostgreSQL has to find the right B-tree page, which may already have been evicted from the cache; on large tables that becomes constant random I/O and poor page packing.

The right way: UUID v7 is monotonic — it starts with a timestamp, so inserts go sequentially, pages pack densely and the cache works. PostgreSQL 18 added a built-in uuidv7(); on earlier versions v7 comes from the application — gen_random_uuid() makes only v4.

In short

• The type follows the value's meaning, not habit: text over varchar(255), uuid over varchar(36), boolean over smallint and char(1).
• timestamptz for any business time — and a type with a zone on the application side.
• Money is numeric(p, s): float accumulates error, money carries the session locale instead of a currency.
• New schemas take GENERATED ALWAYS AS IDENTITY, not serial: no hand-picked id slips past the sequence.
• A PG ENUM value can never be removed — a growing enumeration lives in a reference table with an FK.
• JSONB is for polymorphic and rare data; a field you filter by is a column, an object with identity a table, not an array.
• An interval is a range type with EXCLUDE USING gist and btree_gist, not a valid_from / valid_to pair.
• A primary key is UUID v7, not v4; time and the id generator go through a layer tests can swap.

What to read next

• Time and time zones in PostgreSQL — what timestamptz does on write and read.
• Numbers and precision in PostgreSQL — which numeric to take for money.
• UUID and identifiers in PostgreSQL — v4 against v7 and the price of a random key.
• Zero-downtime migrations — how to change a column type on a live table.