PostgreSQL LENGTH Function: Syntax, Examples, and Gotchas

LENGTH() in PostgreSQL returns the number of characters in a text string, or the number of bytes in a bytea value. It is the standard tool for character-count validation, but it is not the same as byte count under UTF-8 encoding - that is OCTET_LENGTH(). LENGTH(NULL) returns NULL; whitespace is counted; multi-byte characters count as one.

Syntax

length(text)   -> integer   -- character count
length(bytea)  -> integer   -- byte count
char_length(text)      -> integer   -- alias of length(text), SQL standard
character_length(text) -> integer   -- alias of length(text), SQL standard
octet_length(text)     -> integer   -- byte count of encoded text
bit_length(text)       -> integer   -- bit count (= 8 * octet_length)

Official reference: PostgreSQL string functions.

Parameters

LENGTH does not accept integer, boolean, or timestamp directly. Cast first with value::text if needed.

Examples

-- 1. Basic character count
SELECT length('hello world');        -- 11

-- 2. Multi-byte characters count as one
SELECT length('josé');                -- 4
SELECT octet_length('josé');          -- 5 (UTF-8: é is two bytes)
SELECT bit_length('josé');            -- 40

-- 3. NULL propagates
SELECT length(NULL::text);            -- NULL
SELECT coalesce(length(NULL::text), 0); -- 0

-- 4. Validate user input length
SELECT id, username
FROM users
WHERE length(username) BETWEEN 3 AND 32;

-- 5. Find rows where a column is empty or whitespace-only
SELECT id FROM posts WHERE length(btrim(content)) = 0;

-- 6. Byte length of a bytea column (raw bytes, not characters)
SELECT id, length(file_data) AS bytes
FROM uploads
ORDER BY bytes DESC
LIMIT 10;

-- 7. Add a CHECK constraint backed by length
ALTER TABLE products
    ADD CONSTRAINT product_code_length
    CHECK (length(code) BETWEEN 4 AND 16);

Common Issues and Gotchas

1. Characters vs bytes. length('josé') returns 4 (characters), octet_length('josé') returns 5 (UTF-8 bytes). Storage planning, network protocols, and column type sizing (e.g. varchar(n) which is character-count-bounded) need different functions.
2. LENGTH(bytea) returns bytes, not characters. Confusingly, the same function name behaves differently by argument type. For binary data, that is what you want; for text stored in bytea, decode first with convert_from(bytes, 'UTF8').
3. NULL handling. LENGTH(NULL) is NULL, not 0. Wrap with COALESCE(length(col), 0) if downstream code expects a number.
4. Trailing spaces in char(n). PostgreSQL's char(n) (blank-padded) silently pads to n characters on read, but length() reports the unpadded length. length('abc'::char(5)) returns 3.
5. varchar(n) bound is characters, not bytes. A varchar(10) column accepts 10 characters of any width. A 10-character emoji string may take 40 bytes on disk.
6. length() is not indexable for prefix search. length(name) = 5 requires an expression index (CREATE INDEX ON t (length(name))). For prefix matching, use name LIKE 'abc%' with a regular B-tree index instead.

Performance Notes

length(text) is O(n) in the string length because PostgreSQL must scan to count UTF-8 code points. For ASCII-only data, the scan is fast. For very wide text columns where you frequently filter by length, build an expression index:

CREATE INDEX idx_posts_title_len ON posts (length(title));

length(bytea) is O(1) - PostgreSQL stores the byte length in the value header.

In aggregation queries (AVG(length(col)), MAX(length(col))), the cost is dominated by the table scan, not the function. The planner does not push length() filters down through TOAST detoasting, so filtering on length still requires reading the full out-of-line value.

Frequently Asked Questions

Q: What is the difference between LENGTH and OCTET_LENGTH in PostgreSQL?
A: LENGTH(text) returns the number of characters. OCTET_LENGTH(text) returns the number of bytes in the current server encoding. For UTF-8 with non-ASCII characters, the two differ. Use LENGTH for character counts, OCTET_LENGTH for storage and protocol sizing.

Q: Is LENGTH the same as CHAR_LENGTH in PostgreSQL?
A: Yes. CHAR_LENGTH and CHARACTER_LENGTH are SQL-standard aliases for LENGTH(text). All three return the same character count. PostgreSQL keeps the aliases for SQL conformance.

Q: Why does LENGTH(NULL) return NULL instead of 0?
A: PostgreSQL follows SQL NULL semantics: any function applied to NULL returns NULL unless the function explicitly defines NULL-input behavior. To get 0 instead, wrap with COALESCE(length(col), 0).

Q: How do I count characters excluding leading and trailing whitespace?
A: Combine with BTRIM: length(btrim(col)). For one-sided trim, use ltrim or rtrim. This is the standard pattern for "empty after trimming" checks.

Q: How do I find the byte size of a column for capacity planning?
A: For text, use SUM(octet_length(col)). For bytea, use SUM(length(col)). For total row size including TOAST and headers, use pg_column_size(col), which counts the on-disk representation including compression.

Q: Can I use LENGTH on numeric or date types?
A: Not directly. Cast to text first: length((123)::text) returns 3. PostgreSQL does not have an implicit numeric-to-text conversion for length().

Related Reading

• PostgreSQL CONCAT Function: combine strings whose lengths you may check.
• PostgreSQL Cast Operator: how ::text works when measuring non-text values.
• PostgreSQL Character Not in Repertoire: encoding errors related to multi-byte characters.
• PostgreSQL CASE Expression: combine with length() for conditional logic.
• PostgreSQL Check Constraint Violated: enforce length bounds with CHECK constraints.
• PostgreSQL Array Length Function: array equivalent for measuring element counts.