Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 90 ยท Data Types

Text Types and Collations

Choose between text, varchar(n) and char(n) in PostgreSQL, understand collations and ICU, and handle case-insensitive matching with lower(), citext and custom collations.

Almost every table has text columns: names, emails, titles, descriptions. PostgreSQL gives you three character types that look different but behave almost identically, and a collation system that decides how strings are compared and sorted. Most beginner mistakes here come from carrying habits over from other databases, such as picking varchar(255) out of reflex or expecting 'Apple' = 'apple' to be true.

This lesson explains what each type really does, how collations affect sorting and equality, and the common ways to do case-insensitive matching. The string functions in the live examples run in the site's SQLite engine, so they are real. Anything that only PostgreSQL supports is shown as a read-only block, and each block says so.

Three character types

text Any length, up to about 1 GB.
varchar(n) Same as text, plus a length check.
char(n) Pads with spaces. Rarely a good idea.

Under the hood all three share one storage format.

1 / 2

text, varchar(n) and char(n)

The PostgreSQL types are:

Type Meaning
text Variable length, no declared limit
varchar(n) or character varying(n) Variable length, at most n characters
varchar Same as text (no limit)
char(n) or character(n) Fixed length n, padded with spaces

text is a PostgreSQL type rather than part of the SQL standard, but it is available everywhere in practice. The key facts:

  • There is no performance difference between text and varchar(n). They are stored the same way. Choosing varchar(255) does not make anything faster or smaller.
  • The limit counts characters, not bytes. varchar(10) holds ten characters, even if each takes several bytes in UTF-8.
  • char(n) is usually the wrong choice. Shorter values are padded with spaces, trailing spaces are ignored in comparisons, and the padding wastes space. It exists for compatibility with the standard.
  • A single value can be up to about 1 GB. Large values are compressed and moved out of line automatically (a mechanism called TOAST).

A varchar(n) limit is only worth declaring when the limit is a real business rule, such as a two-letter country code or a 280-character post. If the limit is arbitrary, use text. A length rule can also be written as a constraint, which is easier to change later:

SQL
-- Read-only: PostgreSQL syntax
CREATE TABLE posts (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL CHECK (char_length(title) BETWEEN 1 AND 200),
  body  text NOT NULL
);

Changing a CHECK constraint later is cheap. Increasing a varchar(n) limit is also a quick metadata change in modern versions, but shrinking one forces a table scan to verify the existing rows.

The char(n) surprise

With char(5), the value 'ab' is stored as 'ab '. Comparisons ignore the padding, but casting to text removes it and some functions treat it differently:

SQL
-- Read-only: PostgreSQL
SELECT 'ab'::char(5) = 'ab   '::char(5);   -- true
SELECT length('ab'::char(5));              -- 2  (trailing spaces are not counted)
SELECT octet_length('ab'::char(5));        -- 5  (but they are stored)

Those mixed answers are the reason to prefer text or varchar.

Length limits are not enforced by SQLite

The live engine here is SQLite, which treats VARCHAR(3) as a plain text column and ignores the number. This is a useful contrast: in PostgreSQL the same insert below fails with value too long for type character varying(3).

SQL SQLite ignores varchar(n)

Press Run (or Ctrl + Enter) to see the result here.

You get two rows: the first holds an eight-character short_code that a real PostgreSQL would have rejected, and the second holds abc. The over-long checked_code row was skipped by the CHECK. Keep this in mind: a CHECK constraint behaves the same in both databases, but a declared varchar(n) does not.

String functions you will use daily

PostgreSQL has a rich set of string functions. Many exist in SQLite under the same or a similar name, which lets you try them here. See String Functions for the portable basics.

Job PostgreSQL SQLite equivalent
Length in characters char_length(s) length(s)
Upper and lower case upper(s), lower(s) same
Concatenate a || b, concat(a, b) a || b
Substring substring(s FROM 2 FOR 3) or substr(s, 2, 3) substr(s, 2, 3)
Find position position('x' IN s) or strpos(s, 'x') instr(s, 'x')
Replace replace(s, 'a', 'b') same
Trim trim(s), ltrim, rtrim same
Left pad lpad(s, 5, '0') printf('%05d', n)
Split and join split_part(s, ',', 2), string_agg() group_concat()
Title case initcap(s) not available

PostgreSQL also has left(s, n), right(s, n), repeat(s, n), reverse(s) and regexp_replace(), which you will meet in Pattern Matching and Regular Expressions.

SQL String functions that work in both

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL, concatenating with || returns NULL if either side is NULL, just as in SQLite. The concat() function treats NULL as an empty string instead, and concat_ws(sep, ...) skips NULL values and joins the rest with a separator.

Collations: how strings compare

A collation is a set of rules for ordering and comparing strings. It answers questions such as: does 'a' sort before 'B'? Where does 'รค' go? Are 'a' and 'A' equal? PostgreSQL does not implement these rules itself. It asks a library:

  • libc collations come from the operating system (en_US.UTF-8). Results can change when the OS upgrades its C library.
  • ICU collations come from the International Components for Unicode library. They are consistent across platforms and have names like und-x-icu or en-US-x-icu.
  • C and POSIX simply compare bytes. They are fast and fully predictable: uppercase letters sort before lowercase ones.
  • PostgreSQL 17 added a built-in provider with a C.UTF-8 collation that works the same on every platform.

You can set a default collation for a database, override it for a column, or override it for a single expression:

SQL
-- Read-only: PostgreSQL
CREATE DATABASE shop LOCALE_PROVIDER icu ICU_LOCALE 'en-US'
  TEMPLATE template0 ENCODING 'UTF8';

CREATE TABLE people (
  name text COLLATE "und-x-icu"
);

-- Compare bytes for this query only:
SELECT name FROM people ORDER BY name COLLATE "C";

With a typical en_US collation, ORDER BY gives apple, Apple, banana, Banana. With COLLATE "C" it gives Apple, Banana, apple, banana, because uppercase letters have lower byte values. SQLite's default BINARY collation behaves like C, which makes it a handy way to see the difference:

SQL Collation changes sorting and equality

Press Run (or Ctrl + Enter) to see the result here.

The last column shows a limit of SQLite's NOCASE: it only knows ASCII letters, so รฉ and ร‰ stay different. A proper Unicode-aware collation, such as ICU in PostgreSQL, does better.

An index stores keys in collation order. If the operating system upgrades its C library and the sort rules change, existing indexes on libc-collated text columns can become silently wrong. PostgreSQL records the collation version and warns you; rebuild affected indexes with REINDEX when it does. ICU and the C collation reduce this risk.

Case-insensitive matching

By default PostgreSQL string comparison is case-sensitive: '[email protected]' = '[email protected]' is false. You have several options, and each has a cost.

1. Normalize on the way in. Store emails in lower case (convert in the application or with a trigger or CHECK (email = lower(email))) so a plain = works and a plain unique index enforces uniqueness.

2. Compare with lower(), and index the expression. This is the most common approach and works on any installation:

SQL
-- Read-only: PostgreSQL
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email));

SELECT * FROM users WHERE lower(email) = lower('[email protected]');

The query must use the exact same expression as the index, or the index will not be used. Partial and Expression Indexes explains this in detail.

3. Use ILIKE for pattern searches. name ILIKE 'ada%' ignores case but is a pattern match, not an equality test. Without a special index (see GIN and GiST Indexes) it scans the table.

4. Use the citext extension. The citext type compares and sorts case-insensitively without any extra function calls:

SQL
-- Read-only: needs the citext extension
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE accounts (email citext UNIQUE NOT NULL);
-- WHERE email = '[email protected]' now matches '[email protected]'

It is convenient, but it folds case with the database's lower-casing rules and adds an extension dependency. For new projects the next option is often better.

5. Use a nondeterministic ICU collation. This is the standards-based approach, available since PostgreSQL 12:

SQL
-- Read-only: PostgreSQL 12+
CREATE COLLATION ci_ai (
  provider = icu,
  locale = 'und-u-ks-level2',
  deterministic = false
);

CREATE TABLE accounts (email text COLLATE ci_ai UNIQUE NOT NULL);

A nondeterministic collation can treat different byte sequences as equal, so it also ignores case (and, at another strength level, accents). The trade-offs: these collations have restrictions with pattern matching in older versions (LIKE only gained support in PostgreSQL 18) and some indexing features, so test your queries.

The live SQLite engine lets you try the lower() approach on a small table, with an index on the expression:

SQL lower() and an expression index

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL, which statement about text and varchar(n) is correct?

    text and varchar(n) are stored identically. The only difference is that varchar(n) rejects values longer than n characters. Choosing a limit does not improve speed.
  2. 2. Why is char(n) rarely recommended?

    char(n) pads shorter values with spaces and ignores trailing spaces in some comparisons, which leads to confusing results. text or varchar avoid that.
  3. 3. A query uses WHERE lower(email) = '[email protected]'. What makes it fast on a large table?

    An index on the plain column cannot answer a condition on lower(email). You need an expression index with the same expression.
  4. 4. What does a collation define?

    A collation is the set of rules for ordering and comparing strings, for example whether lower and upper case are equal and where accented letters sort.
  5. 5. Which approach lets you compare text case-insensitively with a plain = and no extension?

    A nondeterministic ICU collation such as und-u-ks-level2 treats strings that differ only in case as equal. COLLATE "C" compares raw bytes and is case-sensitive.

Summary

  • text, varchar and varchar(n) share one storage format; there is no speed gain from limits, so use text unless a length is a real rule.
  • Avoid char(n): it pads with spaces and behaves oddly.
  • A collation (libc, ICU, C or the built-in provider) decides sorting and equality; it can be set per database, column or expression.
  • COLLATE "C" compares bytes: fast and predictable, but uppercase sorts before lowercase.
  • For case-insensitive matching use normalized storage, an expression index on lower(), citext, or a nondeterministic ICU collation.
  • SQLite ignores varchar(n), while PostgreSQL enforces it; a CHECK constraint behaves the same in both.

Next: Booleans and Enums.