Character types (char, varchar, text) in PostgreSQL

Introduction

Character types store text data in a database. They help keep words, names, or any text information organized and easy to find.

Storing fixed-length codes like country codes (e.g., 'USA', 'CAN').

Saving names or descriptions where length can vary, like product names.

Keeping long text like comments or articles.

When you want to limit the size of text to save space or ensure consistency.

Syntax

PostgreSQL

CHAR(n)
VARCHAR(n)
TEXT

CHAR(n) stores fixed-length text, padded with spaces if shorter.

VARCHAR(n) stores variable-length text up to n characters.

TEXT stores variable-length text with no specific limit.

Examples

This creates a table with a fixed 3-character code, a name up to 50 characters, and a long description.

PostgreSQL

CREATE TABLE example (
  code CHAR(3),
  name VARCHAR(50),
  description TEXT
);

Inserts a row with a 3-character code, a name, and a longer text description.

PostgreSQL

INSERT INTO example (code, name, description) VALUES ('USA', 'United States', 'A country in North America.');

Finds rows where the name starts with 'United'.

PostgreSQL

SELECT code, name FROM example WHERE name LIKE 'United%';

Sample Program

This example creates a table for countries with a fixed 3-letter code, a name up to 50 characters, and notes with no length limit. It inserts three countries and then selects all data ordered by the country code.

PostgreSQL

CREATE TABLE countries (
  country_code CHAR(3),
  country_name VARCHAR(50),
  notes TEXT
);

INSERT INTO countries (country_code, country_name, notes) VALUES
('USA', 'United States', 'Located in North America'),
('CAN', 'Canada', 'Known for maple syrup'),
('MEX', 'Mexico', 'Famous for its cuisine');

SELECT country_code, country_name, notes FROM countries ORDER BY country_code;

Important Notes

CHAR pads text with spaces to fill the fixed length, so 'USA' stored as CHAR(5) becomes 'USA '.

VARCHAR is more flexible and usually preferred unless you need fixed length.

TEXT can store very long text but does not enforce length limits.

Summary

CHAR(n) stores fixed-length text, padding with spaces if needed.

VARCHAR(n) stores variable-length text up to n characters.

TEXT stores variable-length text with no size limit.