Why is char(n) a trap in Postgres?

char(n) with its padding semantics

Note: this works and blocks nothing, but a performance regression is likely.

What happens

char(n) space-pads every value to n and strips the padding in surprising places, comparisons and lengths behave differently from every other string type, with no storage or speed benefit over text in Postgres.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE TABLE users (country_code char(2));

The safe pattern

Use text (with a CHECK constraint when the length is a real business rule) or varchar. In Postgres char(n) has no storage or performance advantage, only the padding surprises.

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  country_code text NOT NULL CHECK (char_length(country_code) = 2)
);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

char

ALTER TABLE users ADD COLUMN country_code char(2);

Stays silent (1)

text

ALTER TABLE users ADD COLUMN country_code text;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV040 BV042 →