In PostgreSQL we can use the left() function to get the specified number of leftmost characters in a given string.

We have the option of specifying the number of characters to return from the left or the number of characters to omit from the right. We do this by specifying a positive integer (to return n number of leftmost characters) or a negative integer (to return everything except n rightmost characters).

Example

Here’s a quick example to demonstrate:

SELECT left('Catatonia', 3);

Result:

Cat

In this example I used a positive integer (3) to return the three leftmost characters.

Using a Negative Count

As mentioned, we can use a negative integer to return everything except the last n characters from the string:

SELECT left('Catatonia', -3);

Result:

Catato

So in this case we returned everything except the last three characters.

Trimming White Space

If the original string contains white space on the right or left this can affect the result. We can use the trim() function to remove white space from the string:

SELECT 
    left(' Catatonia ', 3) AS "Untrimmed",
    left(trim(' Catatonia '), 3) AS "Trimmed";

Result:

 Untrimmed | Trimmed 
-----------+---------
  Ca       | Cat

And here it is with a negative value:

SELECT 
    left(' Catatonia ', -3) AS "Untrimmed",
    left(trim(' Catatonia '), -3) AS "Trimmed";

Result:

 Untrimmed | Trimmed 
-----------+---------
  Cataton  | Catato

We can also use btrim(), rtrim() and ltrim() if required.

Passing Null Values

If any argument is null, then null is returned:

\pset null 'null'
SELECT 
    left('Catatonia', null),
    left(null, 3),
    left(null, null);

Result:

 left | left | left 
------+------+------
 null | null | null