When it comes to working with text data in PostgreSQL, we’ve got three main players: CHAR, VARCHAR, and TEXT. Each one has its own quirks, upsides, and gotchas. Let’s break it down step by step.
CHAR(n)
CHAR, or character, is a string with a fixed length of n. If your data string is shorter than that, it’ll get padded with spaces automatically.
Example:
| id | code - CHAR(5) |
|---|---|
| 1 | ''ABC'' |
This type is handy when all your strings need to be the same length (like codes, barcodes, or fixed-length IDs).
But if you’re working with variable-length text, those extra spaces just waste space in your database.
VARCHAR(n)
VARCHAR, or variable character, is meant for storing strings of variable length, with a max limit of n characters.
Example:
| id | username - VARCHAR(10) |
|---|---|
| 1 | ''Alice'' |
This type uses space efficiently, since it only stores the actual text. The downside is you have to set a length limit. If your string goes over that, the database will throw an error.
TEXT
TEXT is a string data type with no length limit. This type is often used when you’re not sure how big your text might get.
Example:
| id | content - TEXT |
|---|---|
| 1 | ''This is a long piece of text. No limits!'' |
Pros: you can store text of any length, no worries about limits.
Cons: having no limits can lead to inefficient database usage if your text data starts to "bloat".
Comparing Text Data Types
When you’re working with text, it’s important to know which data type fits your case. Here are the main differences between CHAR, VARCHAR, and TEXT:
| Data Type | Length | Performance | When to use? |
|---|---|---|---|
CHAR(n) |
Fixed | Usually the slowest of the three (extra space padding and length checks) | For fixed-length codes (like ISO) |
VARCHAR(n) |
Max length n |
Performance-wise indistinguishable from TEXT |
For variable-length strings with a known max |
TEXT |
Unlimited | Performance-wise indistinguishable from VARCHAR; recommended by PostgreSQL by default |
For long texts where size is hard to predict |
Practical Usage Examples
Alright, let’s check out how to use these data types in real-life scenarios.
Example 1: Using CHAR for fixed-length codes
Imagine you’re working with a database where you need to store three-letter country codes per the ISO 3166-1 alpha-3 standard (USA, GBR, RUS). Each code is exactly 3 characters.
| city_id | city_name - VARCHAR(50) | iso_code - CHAR(3) |
|---|---|---|
| 1 | New York | NYC |
| 2 | Los Angeles | LAX |
| 3 | Chicago | CHI |
Here, CHAR(3) is perfect, since every city’s ISO code is exactly three characters.
Example 2: Using VARCHAR for usernames
Usernames are a great use case for VARCHAR. Usually, a username has variable length, but you can assume it won’t go over 50 characters.
| user_id | username - VARCHAR(50) | email - VARCHAR(50) |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.net |
VARCHAR saves space here, since the actual string length can be less than 50 characters.
Example 3: Using TEXT for storing descriptions
Imagine you’ve got a blog, and each post needs to store a big text description. Here, TEXT is the best choice.
| post_id | title - VARCHAR(100) | content - TEXT |
|---|---|---|
| 1 | Post 1 | This is a very long blog post content that goes on and on... |
If you never know in advance how long the text will be, TEXT is just right.
4. Extra Gotchas and Caveats
When working with text data types, there are a few things to keep in mind to avoid classic mistakes.
Problem: CHAR adds spaces
If you try to compare strings in a CHAR field without trimming the extra spaces, you might get unexpected results.
SELECT * FROM cities WHERE iso_code = 'NYC';
-- Nothing will be returned if you don’t trim the spaces
How to fix: Use the TRIM() function to remove spaces.
SELECT * FROM cities WHERE TRIM(iso_code) = 'NYC';
Problem: Length limits in VARCHAR can cause errors
If you try to insert a string into a VARCHAR field that’s too long, the database will throw an error.
INSERT INTO users (username, email) VALUES ('A_username_that_is_too_long_for_field', 'test@example.com');
-- Error
How to fix: Make sure the length limit (n) matches your real needs. Or just use TEXT to avoid limits.
Problem: TEXT can bloat your database
TEXT stores unlimited data, which can make your tables grow too much and make indexing harder.
How to avoid: If you plan to actively index a TEXT column, consider using a limited VARCHAR instead.
GO TO FULL VERSION