TEXT / VARCHAR / CHAR

Level 2 — Core Data Types & Constraints The three primary string data types in PostgreSQL, differing in length constraint enforcement and trailing space padding behavior.

1. Prerequisites

• Data Types (Overview) — Understanding database column typing.

2. Term Category

Data Type (Character Sequence Types): Text data types (TEXT, VARCHAR(n), CHAR(n)) store UTF-8 character string sequences within table columns.

3. Explanation

Environment Context

• PostgreSQL Core (Stored internally using the same storage format. Strings longer than 2KB are automatically compressed and moved out of main table memory via PostgreSQL TOAST storage).

(1) Design Motivation — "Why did we design this?"

Relational databases must support text of varying lengths—from short country codes ("US") to medium usernames ("john_doe") to massive blog articles.

In old database engines (like early SQL Server or MySQL), choosing the wrong string type severely hurt performance:

• CHAR was fast but wasted space.
• VARCHAR was slower but saved space.

In modern PostgreSQL, this performance difference is gone. Under the hood, TEXT and VARCHAR use the exact same storage engine and are equally fast.

The three types are used to enforce business constraints:

(2) Reality Metaphor

Imagine packing suitcases:

• TEXT and VARCHAR are like soft, expandable canvas bags. If you pack a single t-shirt, the bag compresses down to the size of the t-shirt. It only occupies as much space as the content requires.
• CHAR(n) is a hard plastic suitcase. If you put a single t-shirt inside a 10-slot suitcase, it stays full-sized. To prevent items from rattling, the suitcase is filled with styrofoam spacers (trailing spaces). It always occupies the maximum space.

(3) Code Examples

Creating a Table with Text Types

CREATE TABLE user_profiles (
  country_code CHAR(2),          -- Always exactly 2 characters (e.g. 'US', 'CA')
  username VARCHAR(50),          -- Limit username length
  bio TEXT                       -- Unlimited text description
);

The Space Padding Difference

Let's see what happens when we insert a short string into a fixed CHAR column:

INSERT INTO user_profiles (country_code, username, bio) 
VALUES ('US', 'alice', 'Hello world');

-- If you query length, country_code is returned padded:
-- A string inserted into CHAR(10) gets trailing spaces added to fill the slot.

Truncation Error Example

If you exceed a VARCHAR limit, Postgres blocks the write:

-- This crashes because 'this_is_an_extremely_long_username_that_exceeds_fifty' is 53 characters!
INSERT INTO user_profiles (country_code, username, bio) 
VALUES ('US', 'this_is_an_extremely_long_username_that_exceeds_fifty', 'Short bio');
-- ERROR: value too long for type character varying(50)

4. Common Mistakes & Pitfalls

Mistake 1: Believing VARCHAR(50) is faster to query than TEXT in PostgreSQL

The mistake: Artificially capping text columns at VARCHAR(255) because you think it speeds up database indexes or consumes less RAM than TEXT.

Why it's wrong: In Postgres, TEXT and VARCHAR are stored using the exact same binary storage engine. Choosing VARCHAR(255) is NOT faster than TEXT. The only difference is that Postgres checks the character count of VARCHAR inputs on every insert, which adds a tiny CPU validation step.

Fix: Default to TEXT for general string fields. Only use VARCHAR(n) if you want the database to actively enforce a validation constraint (e.g. preventing a username from being 10,000 characters).

Mistake 2: Using Fixed-Length CHAR(N) Type expecting Performance Advantages

The mistake: Defining code CHAR(10) expecting performance improvements over VARCHAR or TEXT.

Why it's wrong: CHAR(N) pads trailing whitespace to fill $N$ characters (e.g. 'abc '), consuming unnecessary byte space and requiring RTRIM() on queries.

Incorrect:

code CHAR(10) -- Pads trailing spaces: 'abc       '

Fix:

code VARCHAR(10) or code TEXT -- No space padding

Mistake 3: Assuming VARCHAR(255) is Faster Than TEXT in PostgreSQL

The mistake: Restricting text columns to VARCHAR(255) out of performance habits from other SQL engines.

Why it's wrong: In PostgreSQL, TEXT, VARCHAR, and VARCHAR(N) share identical underlying storage engines and performance metrics. VARCHAR(255) adds arbitrary length validation checks.

Incorrect:

bio VARCHAR(255) -- Arbitrary length cap

Fix:

bio TEXT -- Unconstrained text storage

5. Practice Exercises

Exercise 1: Evaluating TEXT vs VARCHAR(n) Performance

Scenario: Create a posts table choosing between TEXT and VARCHAR(255).

Requirements:

1. Contrast TEXT vs VARCHAR(n) storage and performance in PostgreSQL.

CREATE TABLE posts (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

Exercise 2: Case-Insensitive String Filtering with LOWER()

Scenario: Query table users for email address "ALICE@EXAMPLE.COM" using case-insensitive matching.

Requirements:

1. Use WHERE LOWER(email) = LOWER('ALICE@EXAMPLE.COM') or ILIKE.

SELECT id, username, email 
FROM users 
WHERE LOWER(email) = LOWER('ALICE@EXAMPLE.COM');

Exercise 3: String Trimming and Substring Extraction

Scenario: Sanitize text input by removing whitespace (TRIM()) and extracting the first 50 characters (LEFT()).

Requirements:

1. Use TRIM() and LEFT().

SELECT 
  id, 
  TRIM(title) AS clean_title,
  LEFT(TRIM(body), 50) || '...' AS excerpt 
FROM articles;

6. Related Terms

• Data Types (Overview) — The parent typing framework.
• INTEGER / BIGINT / SMALLINT — Numeric integer types.

7. Key Takeaways

• PostgreSQL string types include TEXT, VARCHAR(n), and CHAR(n).
• TEXT and VARCHAR use the same underlying storage engine and perform identically.
• VARCHAR(n) enforces a maximum character limit check, rejecting longer strings.
• CHAR(n) pads values with trailing spaces, which can cause query string comparison bugs.
• Default to TEXT for free-form strings, and use VARCHAR(n) only for active validation.