What is the difference between CHAR, VARCHAR, and TEXT?

Q: What is the difference between CHAR, VARCHAR, and TEXT?

**Target Audience:** Beginners choosing types for their first tables, and candidates who need the engine-specific facts behind "it depends". **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15 (UTF-8 database), both run for this page. MySQL and SQL Server facts are cited from their manuals. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Imagine three ways to give people name badges. - **Fixed boxes.** Every badge has exactly 5 squares, one letter per square. "Ab" gets three empty squares after it. Every badge is the same size, and short names waste space. - **A strip with a limit.** You write the name

The problem

Question presented to candidate: "When you design a table, how do you choose between CHAR, VARCHAR and TEXT for a string column? What does each one actually do with the value, and does the choice affect performance?"

What a strong answer should cover:

CHAR(n) is fixed length and blank-padded; VARCHAR(n) is variable length with a maximum; TEXT is variable length without a declared maximum
Padding semantics: trailing spaces in CHAR are insignificant in comparisons and dropped when converted to text, which produces surprises with LIKE and concatenation
Length limits are counted in characters in PostgreSQL and MySQL but in bytes in SQL Server; too-long values raise an error, while an explicit cast truncates
Engine facts: in PostgreSQL there is no performance difference and TEXT or VARCHAR is recommended; SQLite ignores declared lengths entirely (type affinity); MySQL TEXT needs a prefix length to be indexed; SQL Server TEXT is deprecated in favour of VARCHAR(MAX)
A sensible rule: use TEXT/VARCHAR by default, a length or CHECK only when it is a real business rule, CHAR almost never

Code / implementation expected: Optional — show what each type stores for 'ab' and how comparisons with trailing spaces behave.

Schema used by the examples:

data-types

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners choosing types for their first tables, and candidates who need the engine-specific facts behind "it depends". Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15 (UTF-8 database), both run for this page. MySQL and SQL Server facts are cited from their manuals.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Imagine three ways to give people name badges.

Fixed boxes. Every badge has exactly 5 squares, one letter per square. "Ab" gets three empty squares after it. Every

The solution is hidden so you can try first

12 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: declared lengths are not enforced, and every one of these types is stored as text

SQLSQLite: CHAR(5) is not padded, and trailing spaces count in comparisons

PostgreSQL: CHAR pads to 5, ignores trailing spaces in comparisons and drops them when converted to text

SELECT id,
       '[' || c || ']' AS c_concat, length(c) AS c_len, octet_length(c) AS c_bytes, c = 'ab' AS c_eq_ab,
       '[' || v || ']' AS v_concat, v = 'ab' AS v_eq_ab
FROM samples
ORDER BY id;

PostgreSQL: a cast truncates silently, padding equality differs by type, and LIKE sees the padding

SELECT 'abcdef'::varchar(3) AS cast_truncates,
       'ab'::char(5) = 'ab   '::char(5) AS char_pad_eq,
       'ab'::varchar(5) = 'ab  '::varchar(5) AS varchar_pad_eq,
       'ab'::char(5) LIKE 'ab' AS char_like_ab,
       char_length('héllo') AS chars, octet_length('héllo') AS utf8_bytes;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.