Now we arrive at the char(n) type, the most misunderstood and, frankly, most often misused character type in the SQL universe. Its defining characteristic is that it is fixed-length. When you define a column as char(10), every single value in that column is exactly 10 characters long. Always. No exceptions. The database engine enforces this with a zeal usually reserved for bouncers at an exclusive club.

If you insert a string shorter than the defined length, say ‘cat’ into a char(10) column, PostgreSQL doesn’t just store those three characters. Oh no. It pads the string with spaces until it reaches the specified length. It becomes ‘cat       ’ (with seven trailing spaces). When you retrieve that value, by default, the trailing spaces are stripped off, making it seem like the padding never happened. This is the source of much confusion and many a quiet bug.

The Padding Illusion: Storage vs. Retrieval

This behavior is the core of the char(n) experience. Let’s break it down with code.

CREATE TABLE char_example (
    id serial PRIMARY KEY,
    fixed_name char(10)
);

INSERT INTO char_example (fixed_name) VALUES
('dog'),          -- 3 chars, will be padded to 10
('elephant'),     -- 8 chars, will be padded to 10
('hippopotamus'); -- 12 chars? ERROR! Too long.

-- Now let's see what we *actually* have in there.
SELECT fixed_name, length(fixed_name) FROM char_example;

 fixed_name | length
------------+--------
 dog        |      3
 elephant   |      8

Wait, what? The length function returns 3 and 8, not 10! This is the illusion. The padding spaces are there in storage, but the standard string functions like length and the way the psql console displays results typically trim trailing spaces before showing them to you. To see the raw, padded truth, we can use a trick.

SELECT fixed_name, length(fixed_name), octet_length(fixed_name) FROM char_example;

 fixed_name | length | octet_length
------------+--------+--------------
 dog        |      3 |           10
 elephant   |      8 |           10

Ah-ha! The octet_length function, which returns the number of bytes used for storage, shows the true fixed nature of the column: both values consume 10 bytes. The padding is real; it’s just often hidden from view.

When to Even Use This Thing?

Given its odd behavior, you’re right to ask, “When would I ever want this?” The use cases are narrow but valid:

  1. True Fixed-Length Codes: This is the textbook example. Think of country codes (exactly 2 letters, like ‘US’, ‘CA’, ‘FR’), ISO currency codes (exactly 3 letters, like ‘USD’, ‘EUR’), or other standardized codes where the length is an inviolable rule. For these, char(2) or char(3) genuinely makes sense. You’re telling the database, “This value must be this length,” and it will enforce it (via padding or error).

  2. Heavy, Write-Optimized Workloads (Theoretical): On some very old database systems, fixed-length rows could be faster because the engine knew exactly where to seek on disk. This performance benefit is largely negligible on modern hardware with modern PostgreSQL. If you think you need char(n) for performance, you’re almost certainly wrong and should benchmark against varchar(n) first.

The Pitfalls: Where char(n) Bites You

This is the important part. Using char(n) carelessly leads to headaches.

INSERT INTO char_example (fixed_name) VALUES ('dog');
INSERT INTO char_example (fixed_name) VALUES ('dog '); -- Note the trailing space

SELECT * FROM char_example WHERE fixed_name = 'dog';
-- This will return BOTH rows. The trailing space in the second value is padding, which is ignored.

SELECT * FROM char_example WHERE fixed_name = 'dog '; -- Explicit space
-- This will also return BOTH rows. The explicit space is treated the same as padding.

-- To see the difference, you must use a binary-comparison operator or cast to bytea.

This “helpful” blank-trimming behavior can make comparisons behave in ways that feel counterintuitive. If you need to store values where trailing spaces are semantically important (e.g., a value that is literally ‘dog ’ vs ‘dog’), char(n) is a terrible choice. Use varchar(n) or text.

The Verdict: Just Use text or varchar(n)

Here’s my direct advice: 99% of the time, you should use text (for anything of arbitrary length) or varchar(n) (if you need an explicit constraint on maximum length). They behave predictably. They don’t secretly pad your data with spaces. They don’t create illusions.

Reserve char(n) for those very specific, rare cases where you are storing codes that are defined by their fixed length. For everything else, the flexibility and clarity of text and varchar(n) will save you from wondering why your string isn’t what you thought it was. It’s a specialized tool, not your everyday hammer.