Here are four options for returning rows that contain uppercase letters in PostgreSQL.

Sample Data

Suppose we have a table with the following data:

SELECT c1 FROM t1;

Result:

       c1       
----------------
 CAFÉ
 Café
 café
 1café
 eCafé
 James Bond 007
 JB 007
 007
 null
 
 É
 É 123
 é
 é 123
 ø
 Ø

We can use the following methods to return the rows that contain uppercase letters.

Option 1: Compare to a POSIX Character Class

We can use the ~ operator to perform a case sensitive match of a regular expression pattern. The pattern can be as simple or complex as we like.

A simple option is to compare our column to the [:upper:] POSIX character class:

SELECT c1 FROM t1
WHERE c1 ~ '[[:upper:]]';

Result:

       c1       
----------------
 CAFÉ
 Café
 eCafé
 James Bond 007
 JB 007
 É
 É 123
 Ø

Option 2: Compare to the LOWER() String

Another option is to use the LOWER() function to compare the original value to its lowercase equivalent:

SELECT c1 FROM t1
WHERE LOWER(c1) <> c1;

Result:

       c1       
----------------
 CAFÉ
 Café
 eCafé
 James Bond 007
 JB 007
 É
 É 123
 Ø

By using the not equal to (<>) operator (you can alternatively use != instead of <> if you prefer), we only return those rows that are different to their lowercase equivalents. The reason we do this is because, if a value is the same as its lowercase equivalent, then it was already lowercase to begin with (and we don’t want to return it).

By default, PostgreSQL performs a case-sensitive match, and so we don’t need to specify a case-sensitive collation to get the result we desire.

Option 3: Compare to the Actual Characters

Another option is to use the ~ operator with a regular expression pattern that explicitly includes each uppercase character we want to match:

SELECT c1 FROM t1
WHERE c1 ~ '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';

Result:

       c1       
----------------
 CAFÉ
 Café
 eCafé
 James Bond 007
 JB 007

In this case, less rows are returned than in the previous examples. This is because I didn’t specify characters like É and Ø, which were returned in those examples. Our result does contain É but that row was only returned because it also contains other uppercase characters that do match.

Therefore, this option is more limited than the previous one, but it does provide you with more control over the characters you want to match.

Option 4: Compare to a Range of Characters

We can alternatively specify the range of characters we want to match:

SELECT c1 FROM t1
WHERE c1 ~ '[A-Z]';

Result:

       c1       
----------------
 CAFÉ
 Café
 eCafé
 James Bond 007
 JB 007