Text Types

Nearly every database stores text -- names, emails, descriptions, addresses. PostgreSQL offers several text types, and understanding their differences helps you make good schema decisions. This lesson covers the types themselves and essential string functions.

The Three Text Types

TEXT: Unlimited Length

TEXT

TEXT stores strings of any length with no enforced maximum. It is the simplest and most commonly used text type in PostgreSQL:

• No length limit
• No performance penalty compared to VARCHAR
• The go-to choice when you do not need a length constraint

VARCHAR(n): Variable Length with Limit

VARCHAR(100)    -- Maximum 100 characters
VARCHAR(255)    -- Maximum 255 characters

VARCHAR(n) (also called CHARACTER VARYING(n)) stores strings up to n characters long. Attempting to store a longer string raises an error:

-- If a column is VARCHAR(5):
-- 'hello' works fine (exactly 5 characters)
-- 'hi'    works fine (shorter is OK)
-- 'hello world' raises an ERROR (too long)

VARCHAR without a length specification (just VARCHAR) behaves identically to TEXT.

CHAR(n): Fixed Length

CHAR(10)    -- Always stores exactly 10 characters

CHAR(n) (also called CHARACTER(n)) stores fixed-length strings. If the value is shorter than n, it is padded with trailing spaces:

-- If a column is CHAR(10):
-- 'hello' is stored as 'hello     ' (5 spaces added)
-- Comparisons ignore trailing spaces

In practice, CHAR(n) is rarely used. It wastes space on shorter strings and offers no performance advantage in PostgreSQL. It exists mainly for compatibility with the SQL standard and legacy systems.

Comparison Summary

PostgreSQL-Specific Insight

In PostgreSQL, TEXT, VARCHAR, and VARCHAR(n) all use the same internal storage mechanism (varlena). The only difference is that VARCHAR(n) adds a length check. There is no performance difference between TEXT and VARCHAR(n) -- choose based on whether you need the constraint, not for performance reasons.

Essential String Functions

PostgreSQL provides a rich set of functions for working with text.

Case Conversion

SELECT
    UPPER('hello world') AS uppercased,     -- 'HELLO WORLD'
    LOWER('HELLO WORLD') AS lowercased,     -- 'hello world'
    INITCAP('hello world') AS title_case;   -- 'Hello World'

Applied to real data:

SELECT
    name,
    UPPER(name) AS name_upper,
    LOWER(email) AS email_lower
FROM employees
LIMIT 5;

Length

SELECT
    LENGTH('PostgreSQL') AS char_count,            -- 10
    CHAR_LENGTH('PostgreSQL') AS also_char_count,  -- 10
    OCTET_LENGTH('PostgreSQL') AS byte_count;      -- 10

LENGTH and CHAR_LENGTH return the number of characters. OCTET_LENGTH returns the number of bytes (which differs for multi-byte characters like emojis or accented letters).

Concatenation

There are two ways to join strings together:

-- Using the || operator
SELECT 'Hello' || ' ' || 'World' AS greeting;
-- Returns: 'Hello World'

-- Using the CONCAT function
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- Returns: 'Hello World'

The difference: || returns NULL if any operand is NULL, while CONCAT treats NULL as an empty string:

SELECT
    'Hello' || NULL AS with_operator,      -- NULL
    CONCAT('Hello', NULL) AS with_concat;  -- 'Hello'

Applied to data:

SELECT
    name || ' (' || department || ')' AS employee_info
FROM employees
LIMIT 5;

Substring Extraction

-- SUBSTRING(string FROM start FOR length)
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS result;
-- Returns: 'Post'

-- LEFT and RIGHT
SELECT
    LEFT('PostgreSQL', 4) AS from_left,     -- 'Post'
    RIGHT('PostgreSQL', 3) AS from_right;   -- 'SQL'

Trimming Whitespace

SELECT
    TRIM('  hello  ') AS trimmed,          -- 'hello'
    LTRIM('  hello  ') AS left_trimmed,    -- 'hello  '
    RTRIM('  hello  ') AS right_trimmed;   -- '  hello'

You can also trim specific characters:

SELECT TRIM(BOTH '.' FROM '...hello...') AS trimmed;
-- Returns: 'hello'

Finding and Replacing

-- POSITION: find a substring
SELECT POSITION('SQL' IN 'PostgreSQL') AS found_at;
-- Returns: 8

-- REPLACE: substitute text
SELECT REPLACE('Hello World', 'World', 'PostgreSQL') AS replaced;
-- Returns: 'Hello PostgreSQL'

Applied to data:

SELECT
    name,
    email,
    REPLACE(email, '@', ' [at] ') AS obfuscated_email
FROM employees
LIMIT 5;

Padding

-- LPAD: pad from the left
SELECT LPAD('42', 5, '0') AS padded;
-- Returns: '00042'

-- RPAD: pad from the right
SELECT RPAD('hi', 10, '.') AS padded;
-- Returns: 'hi........'

REPEAT and REVERSE

SELECT
    REPEAT('ha', 3) AS laughing,     -- 'hahaha'
    REVERSE('hello') AS backwards;   -- 'olleh'

String Functions with Real Data

Extracting Email Domains

SELECT
    name,
    email,
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM employees;

Creating Display Names

SELECT
    INITCAP(name) AS display_name,
    UPPER(LEFT(name, 1)) || '. ' || department AS short_form
FROM employees
LIMIT 5;

Finding String Lengths

SELECT
    name,
    LENGTH(name) AS name_length,
    LENGTH(email) AS email_length
FROM customers
ORDER BY name_length DESC
LIMIT 10;

Pattern Matching Preview

PostgreSQL supports pattern matching with LIKE and ILIKE:

-- Names starting with 'A'
SELECT name FROM employees WHERE name LIKE 'A%';

-- Names containing 'son' (case-insensitive)
SELECT name FROM employees WHERE name ILIKE '%son%';

-- Email from a specific domain
SELECT name, email FROM employees WHERE email LIKE '%@gmail.com';

These will be covered in greater depth in a later chapter.

Try It Yourself

1. Show each product name in uppercase, along with its name length.

SELECT
    name,
    UPPER(name) AS name_upper,
    LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC;

2. Concatenate each employee's name and department into a single string like "Alice (Engineering)".

SELECT
    name || ' (' || department || ')' AS employee_info
FROM employees;

3. Extract the first 3 characters of each customer's name and show it alongside their full name and city.

SELECT
    name,
    LEFT(name, 3) AS name_prefix,
    city
FROM customers
ORDER BY name;

4. Find all customers whose email contains "gmail" (case-insensitive).

SELECT name, email
FROM customers
WHERE LOWER(email) LIKE '%gmail%';