What would be the most efficient way, within Postgres, to truncate a text field to a maximum number of bytes, in such a way that the encoding is preserved?
I.e. how to keep at most N bytes while keeping a valid UTF8 string representation.

E.g. assuming UTF8, if a field contains abc€, that's 4 characters for 5 bytes, if I want to truncate this field to at most 4 bytes, but keep a valid UTF8 string, I'd need to actually keep only the first 3 bytes.

I feel like I need the following, but instead of 'escape', I'd like to ignore the invalid bytes to only keep abc:

select encode(substring('abc€'::bytea, 0, 5), 'escape');

What's the best way to achieve this?

  • normally one does not care how many bytes a string is eating up just how many characters that is allotted in the column. the easiest way to cut string to a specific length mystring::char(5) this would return mystr. The question becomes why are you trying to do this in the database
    – zsheep
    Dec 20, 2019 at 16:23
  • I have a case where a field is given to a function that refuses values longer than 256 bytes (through an extension), hence my need to truncate to a given number of bytes, but I also want to keep a valid string representation.
    – Sylvain
    Dec 20, 2019 at 16:37
  • what Postgresql extension is doing that? Is this inhouse or common available extension
    – zsheep
    Dec 20, 2019 at 16:41
  • In UTF8 all bytes of multibyte sequences have the high-order bit set, so you can peek at the byte at the truncation offset and go backward until finding a byte with the high-order bit cleared, and then truncate just after it. Dec 20, 2019 at 18:00
  • @DanielVérité that makes sense. Wouldn't it mean if the truncation leaves a multi-byte character at the end of the string, it would necessarily be removed, even if "complete" ?
    – Sylvain
    Dec 20, 2019 at 18:46

3 Answers 3


Use this function:

   string text,
   max_bytes bigint
) RETURNS text
FROM (SELECT p.p, octet_length(p.p) AS len
      FROM generate_series(0, length($1)) AS len
         CROSS JOIN LATERAL substr($1, 1, len.len) AS p) AS q
WHERE len <= $2
LIMIT 1$$;

It works like this:

SELECT get_prefix('abc€', 4);
(1 row)
  • just finish writing one
    – zsheep
    Dec 20, 2019 at 17:13
  • thanks, I get the idea. I guess to optimize the brute force approach, for UTF8 encoding, we could start at offset "length($1)/4".
    – Sylvain
    Dec 20, 2019 at 17:38
  • Not quite, but I get what you mean. You would replace the 0 in generate_series with $2 / 4. Dec 20, 2019 at 17:42

here is another one written PL/pgSQL

Create or Replace function max_bytea_length(pchars text, bytea_length int)
LANGUAGE plpgsql

COST 100

_i int;
_length_of_chars int;
_newchars text;
_testchars text;

if octet_length(pchars::bytea) <= bytea_length then
   return pchars::bytea;
end if;
_i = least( octet_length(pchars)-4, bytea_length-4);
_length_of_chars =  char_length(pchars);
   _newchars= left(pchars, _i);
    _testchars = left(pchars, _i+1); 
  if octet_length(_testchars::bytea) > bytea_length or _i = _length_of_chars  then
     return _newchars::bytea;
  end if ;
  _i = _i+1;
end loop ;



select max_bytea_length( 'abc€', 4 )::char(10)


select max_bytea_length('i ♥ u function changed it to be faster', 56)::char(60)
  • thanks, there's a small typo: left(pchars, _i+); should be left(pchars, _i+1); Similarly to above I think the first offset could be bytea_length/4 instead of 0. Testing locally both answers, yours performs much faster (2ms vs 50ms for 5 bytes input)
    – Sylvain
    Dec 20, 2019 at 17:53
  • yup that is a typo, The reason this preforms better is Albe is generating an entire record set for the string, for each byte there is a new row, the longer the string the more rows, then there is an order by and where clause. I'll fix my typo
    – zsheep
    Dec 20, 2019 at 18:03
  • bytea_length/4 I do not understand you. there is a logic test if octet_length(pchars::bytea) <= bytea_length this escapes out of the function if the passed in string less than the bytea_length. the loop is doing string manipulation by going left to right 1 character at a time then jumping one character ahead to see if octet_length greater than bytea_length. and to make sure we do not end up in an endless loop _i is compared to the string length..
    – zsheep
    Dec 20, 2019 at 18:17
  • assuming UTF8, if you're looking for at most N bytes, you know you can take at least N/4 characters, since a character can't use more than 4 bytes.
    – Sylvain
    Dec 20, 2019 at 18:20
  • I see where you coming from now, this function is not manipulating the byteas of the string. that would be allot harder in SQL and i do not think there would be any performance improvement. The only why to improve the speed would be take my function is rewrite in C. postgresql.org/docs/12/functions-binarystring.html postgresql.org/docs/current/functions-string.html
    – zsheep
    Dec 20, 2019 at 18:25

Here's a solution for UTF-8 that is based on checking for non-leading bytes in the UTF-8 representation.

The implementation is pure SQL (non-procedural) to benefit from inlining, and uses CASE WHEN... constructs to implement the if-then-else logic.

The idea implemented by the query is: firstly, check if the octet length is already less than the limit, and if yes, return right away the original string. Otherwise, check if the byte at the right of the candidate truncation point is part of a sequence, if no, truncate, otherwise move the truncation point to the left until it does not break a sequence.

A UTF-8 byte in a valid string is in one of these 3 categories:

  • single byte character (bitstring 0xxxxxxx testable with byte & 128 = 0)
  • first byte of a multibyte sequence (bitstring 11xxxxxx testable with byte & 192 = 192)
  • 2nd or 3rd or 4th byte of a multibyte sequence (bitstring 10xxxxxx testable with byte & 192 = 128)

If the byte at the right of the putative cutoff point is of the first or second category, we can truncate safely just before that byte, excluding it. Otherwise it's of the third category, and we need to test up to 3 more bytes backwards until finding a byte that is not of the third category, or the string is too short and the result is the empty string.

The code:

create function utf8_truncate(str text, len int) returns text
as $$
select case when octet_length(str) <= len then str
else (
   with bstr(s) as (select convert_to(str, 'UTF-8'))
   when len>=1 and (get_byte(s, len) & 192) <> 128
   then convert_from(substring(s, 1, len), 'UTF-8')
     when len>=2 and (get_byte(s, len-1) & 192) <> 128
     then convert_from(substring(s, 1, len-1), 'UTF-8')
       when len>=3 and (get_byte(s, len-2) & 192) <> 128
       then convert_from(substring(s, 1, len-2), 'UTF-8')
         when len>=4 and (get_byte(s, len-3) & 192) <> 128
         then convert_from(substring(s, 1, len-3), 'UTF-8')
         else ''
 from bstr)
$$ language sql strict immutable parallel safe;

Some notes:

  • in get_byte(bytea, int), the offsets start at 0, whereas in substring(bytea, int, int) the offsets start at 1.

  • the example in the question casts text to bytea to get the byte representation, but that's not correct: if the text contains backslashes, they will be interpreted as escape sequences of the bytea escape format. convert_to() should be used instead.

  • is it ok if i take your code and stick on wiki.postgresql.org code snippets with mine,
    – zsheep
    Dec 21, 2019 at 18:35
  • @zsheep: you don't need my permission to republish but you need to abide by stackexchange license terms. See the footer at the bottom of this page if you're not familiar with them yet. Dec 21, 2019 at 19:22
  • i read over the Terms for stack overflow, as best as i can read its free to use as long as the authors are noted.. wiki.postgresql.org/wiki/Tuncate_text_by_byte
    – zsheep
    Dec 23, 2019 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.