PostgreSQL UPPER() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL UPPER() function to convert a string to uppercase.

Introduction to the PostgreSQL UPPER() function

The UPPER() function converts a string to uppercase based on the rules of the database’s locale.

Here’s the syntax of the UPPER() function:

UPPER(text)Code language: SQL (Structured Query Language) (sql)

In this syntax, text is the input string that you want to convert to uppercase. Its type can be CHAR, VARCHAR, or TEXT.

The UPPER() function returns a new string with all letters converted to uppercase.

The UPPER() function returns NULL if the text is NULL.

Note that to convert a string to lowercase, you use the LOWER() function.

PostgreSQL UPPER() function examples

Let’s take some examples of using the UPPER() function.

1) Basic PostgreSQL UPPER() function example

The following example uses the UPPER() function to convert the string PostgreSQL to uppercase:

SELECT UPPER('PostgreSQL');Code language: SQL (Structured Query Language) (sql)

Output:

   upper
------------
 POSTGRESQL
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using PostgreSQL UPPER() function with table data

We’ll use the customer table from the sample database:

PostgreSQL UPPER() Function - Sample Table

The following example uses the UPPER() function to convert the first names of customers to uppercase:

SELECT 
  UPPER(first_name) 
FROM 
  customer 
ORDER BY 
  first_name;Code language: SQL (Structured Query Language) (sql)

Output:

    upper
-------------
 AARON
 ADAM
 ADRIAN
 AGNES
 ALAN
...Code language: SQL (Structured Query Language) (sql)

3) Using PostgreSQL UPPER() function in the WHERE clause

The following example uses the UPPER() function in the WHERE clause to find customers by last names, comparing them with the input string in uppercase:

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  UPPER(last_name) = 'BARNETT';Code language: SQL (Structured Query Language) (sql)

Output:

 first_name | last_name
------------+-----------
 Carole     | Barnett
(1 row)Code language: SQL (Structured Query Language) (sql)

Summary

Use the UPPER() function to return a new string with all the characters of the input string converted to uppercase.

Was this tutorial helpful ?