PostgreSQL LENGTH Function

The LENGTH() function in PostgreSQL returns the number of characters in a string. It's essential for validating input, analyzing text data, and implementing character limits in applications.

Syntax

LENGTH(string)

Official Documentation

Example usage

-- Basic usage
SELECT LENGTH('Hello World') AS string_length;
-- Result: 11

-- Check column lengths
SELECT username, LENGTH(username) AS username_length
FROM users
WHERE LENGTH(username) > 20;

-- Find longest values
SELECT product_name, LENGTH(product_name) AS name_length
FROM products
ORDER BY LENGTH(product_name) DESC
LIMIT 10;

-- Validate input length
SELECT *
FROM posts
WHERE LENGTH(title) BETWEEN 10 AND 100
  AND LENGTH(content) >= 50;

-- Count multi-byte characters correctly
SELECT LENGTH('こんにちは') AS char_count;
-- Result: 5 (counts characters, not bytes)

Common issues

1. Encoding differences: LENGTH counts characters, while OCTET_LENGTH counts bytes. Multi-byte characters (UTF-8) will have different results.
2. NULL handling: LENGTH(NULL) returns NULL, not 0. Use COALESCE if needed.
3. Whitespace: LENGTH counts all characters including spaces, tabs, and newlines.

Best practices

1. Use LENGTH for character count validation in user input.
2. For byte size calculations, use OCTET_LENGTH instead, especially for storage estimates.
3. Use CHAR_LENGTH as an alias for LENGTH for SQL standard compliance.
4. Combine with TRIM() when you want to exclude leading/trailing whitespace from the count.

Frequently Asked Questions

Q: What's the difference between LENGTH and OCTET_LENGTH?
A: LENGTH returns the number of characters in a string, while OCTET_LENGTH returns the number of bytes. For multi-byte character encodings like UTF-8, these can differ significantly.

Q: Does LENGTH count spaces and special characters?
A: Yes, LENGTH counts all characters including spaces, tabs, newlines, and special characters. To exclude leading/trailing spaces, use LENGTH(TRIM(column)).

Q: What does LENGTH return for NULL values?
A: LENGTH(NULL) returns NULL. If you need 0 instead, use COALESCE(LENGTH(column), 0).

Q: Is LENGTH the same as CHAR_LENGTH?
A: Yes, CHAR_LENGTH and CHARACTER_LENGTH are synonyms for LENGTH in PostgreSQL. They all return the same result.

Q: How do I find records where a column is empty?
A: Use WHERE LENGTH(TRIM(column)) = 0 to find empty or whitespace-only values, or WHERE column = '' for truly empty strings.