PostgreSQL LENGTH(): Length of a String, char_length and octet_length

To get the PostgreSQL length of a string, call length(): it counts characters, not bytes. Here’s the shortest version, with the byte count next to it:

SELECT length('José')       AS characters,
       octet_length('José') AS bytes;

 characters | bytes 
------------+-------
          4 |     5
(1 row)

That returns 4 characters but 5 bytes, because é takes two bytes in UTF-8.

In this tutorial, I’ll show you every PostgreSQL length function on one small customers table: char_length, octet_length, char(n) padding, trimming, CHECK limits, arrays and NULLs.

PostgreSQL length functions at a glance

Function	Returns	‘José’ gives
length(text)	Number of characters	4
char_length(text) / character_length(text)	Number of characters (SQL standard name)	4
octet_length(text)	Number of bytes	5
bit_length(text)	Number of bits (8 x bytes)	40

The string functions accept char, varchar and text; see my PostgreSQL data types guide.

The sample customers table for the length examples

Every query runs on PostgreSQL 18 against six US customers, two with accented names:

CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  full_name   text NOT NULL,   -- 'José Ramírez', 'Zoë Thompson'
  username    varchar(20),     -- '  avance ', one NULL
  state       char(2) NOT NULL,
  promo_code  char(8),         -- 'SAVE10'
  tags        text[]           -- {vip,newsletter}, {}, NULL
);

To follow along, create a table in PostgreSQL like this one.

length vs char_length vs octet_length vs bit_length

Same customers table, three names, all four functions side by side:

SELECT full_name,
       length(full_name)       AS length,
       char_length(full_name)  AS char_length,
       octet_length(full_name) AS octet_length,
       bit_length(full_name)   AS bit_length
FROM customers
WHERE customer_id IN (1, 2, 4);

Output:

  full_name   | length | char_length | octet_length | bit_length 
--------------+--------+-------------+--------------+------------
 Megan Bowen  |     11 |          11 |           11 |         88
 José Ramírez |     12 |          12 |           14 |        112
 Zoë Thompson |     12 |          12 |           13 |        104
(3 rows)

length and char_length always agree. Bytes differ only where é, í or ë take two bytes each in UTF-8.

Note: on Windows, if é looks garbled in psql, run chcp 65001 first so the console reads UTF-8, as in my screenshots.

Emoji make the gap bigger, since one emoji is four bytes:

SELECT length('Thanks 👍')       AS characters,
       octet_length('Thanks 👍') AS bytes;

Output:

 characters | bytes 
------------+-------
          8 |    11
(1 row)

So if an API or a fixed-width file limits bytes, check octet_length, not length.

How do you find the length of a string column and filter with WHERE?

Pass the column to length(), and use the same expression in the WHERE clause. This finds names longer than 11 characters:

SELECT customer_id, full_name,
       length(full_name) AS name_length
FROM customers
WHERE length(full_name) > 11
ORDER BY name_length DESC;

Output:

 customer_id |       full_name        | name_length 
-------------+------------------------+-------------
           6 | Christopher Montgomery |          22
           2 | José Ramírez           |          12
           4 | Zoë Thompson           |          12
(3 rows)

Christopher Montgomery comes first with 22. Sorting by length is just ORDER BY on the alias, or on length(full_name) itself.

Length of char(n) vs varchar vs text: where trailing spaces go

The promo_code column is char(8), so ‘SAVE10’ is stored padded to eight characters:

SELECT promo_code,
       length(promo_code)             AS length,
       octet_length(promo_code)       AS bytes,
       length('SAVE10  '::varchar(8)) AS varchar_len,
       length('SAVE10  '::text)       AS text_len
FROM customers
WHERE customer_id = 1;

Output:

 promo_code | length | bytes | varchar_len | text_len 
------------+--------+-------+-------------+----------
 SAVE10     |      6 |     8 |           8 |        8
(1 row)

Important: length converts char(n) to text first, which strips the padding. octet_length takes char(n) directly, so it keeps it, while varchar and text keep every space.

That’s one reason I use varchar or text for new columns; here’s how text and varchar compare.

Trim spaces before measuring: length(trim(…))

Imported data often has stray spaces. Same table: find usernames where trimming changes the length:

SELECT customer_id,
       '[' || username || ']'  AS username,
       length(username)        AS raw_length,
       length(trim(username))  AS trimmed_length
FROM customers
WHERE length(username) <> length(trim(username));

Output:

 customer_id |  username   | raw_length | trimmed_length 
-------------+-------------+------------+----------------
           3 | [  avance ] |          9 |              6
(1 row)

trim() only removes spaces at both ends. To drop spaces inside a value too, use PostgreSQL REPLACE before you measure.

PostgreSQL length of NULL and empty strings

NULL isn’t an empty string, and the results show it (I set psql to print (null)):

\pset null '(null)'
SELECT length(NULL::text) AS null_value,
       length('')         AS empty_string,
       length(' ')        AS one_space;

SELECT full_name,
       coalesce(length(username), 0) AS username_length
FROM customers
WHERE username IS NULL;

Output:

Null display is "(null)".
 null_value | empty_string | one_space 
------------+--------------+-----------
     (null) |            0 |         1
(1 row)

  full_name  | username_length 
-------------+-----------------
 Alex Wilber |               0
(1 row)

length(NULL) is NULL, '' is 0 and a single space is 1. Wrap it in coalesce(..., 0) when a report needs a number, as with Alex Wilber’s missing username.

Enforce a maximum length with varchar(n) or a CHECK constraint

A CHECK constraint can use char_length for both a minimum and a maximum. You add it with ALTER TABLE:

ALTER TABLE customers
  ADD CONSTRAINT username_length_chk
  CHECK (char_length(trim(username)) BETWEEN 3 AND 15);

INSERT INTO customers (customer_id, full_name, username, state)
VALUES (7, 'Lee Gu', 'lg', 'IL');

Error:

ALTER TABLE
psql:length-check-constraint.sql:6: ERROR:  new row for relation "customers" violates check constraint "username_length_chk"
DETAIL:  Failing row contains (7, Lee Gu, lg, IL, null, null).

Trimming inside the check counts ‘ avance ‘ as 6. NULL usernames pass, since a CHECK only fails on false.

A varchar(20) column gives you a hard maximum without any constraint:

INSERT INTO customers (customer_id, full_name, username, state)
VALUES (8, 'Isaiah Langer', 'isaiah.langer.chicago', 'IL');

Error:

psql:length-varchar-too-long.sql:2: ERROR:  value too long for type character varying(20)

Fix it by shortening the value or widening the column; here’s how to change a column’s data type.

PostgreSQL array length: array_length vs cardinality

For arrays, length() doesn’t apply. Same customers, now the tags column:

\pset null '(null)'
SELECT full_name, tags,
       array_length(tags, 1) AS array_length,
       cardinality(tags)     AS cardinality
FROM customers
WHERE customer_id IN (2, 3, 6);

Output:

Null display is "(null)".
       full_name        |         tags          | array_length | cardinality 
------------------------+-----------------------+--------------+-------------
 José Ramírez           | {newsletter}          |            1 |           1
 Adele Vance            | {}                    |       (null) |           0
 Christopher Montgomery | {vip,beta,newsletter} |            3 |           3
(3 rows)

array_length needs the dimension and returns NULL for an empty array; cardinality returns 0. For JSON arrays, use jsonb_array_length:

SELECT jsonb_array_length('["vip", "beta"]') AS json_items;

Output:

 json_items 
------------
          2
(1 row)

That’s 2 items. Here’s how JSON and JSONB differ.

How do you get the length of a number in PostgreSQL?

length() only takes text, so cast numbers and dates first:

SELECT length(90210::text)              AS zip_digits,
       length(DATE '2026-10-27'::text)  AS date_chars;

SELECT length(90210);

Output:

 zip_digits | date_chars 
------------+------------
          5 |         10
(1 row)

psql:length-of-number.sql:4: ERROR:  function length(integer) does not exist
LINE 1: SELECT length(90210);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The cast gives 5 digits for the ZIP code and 10 characters for the date. Without it, you get function length(integer) does not exist. More on casting in PostgreSQL.

Common mistakes with PostgreSQL length

• Checking length when the limit is in bytes: use octet_length for UTF-8 data.
• Forgetting that char(n) padding disappears in length but not in octet_length.
• Measuring before trim(), so stray spaces pass a minimum-length check.
• Filtering a big table on length(col) without an expression index on that same expression.

Once you can measure strings, try these next:

• Joining strings with CONCAT
• Pattern matching with LIKE
• Cleaning text with REGEXP_REPLACE
• Building strings with FORMAT

Frequently Asked Questions