PostgreSQL UPPER Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL UPPER function to convert all characters in a string to uppercase.

Introduction to the PostgreSQL UPPER function #

The UPPER function takes a string and returns a new string with all characters converted to uppercase.

Here’s the syntax of the UPPER function:

UPPER( text ) → textCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The UPPER function takes a parameter string with type text and returns a value with the type text with all characters converted to uppercase.

If you pass a value of a type other than TEXT, VARCHAR, and CHAR, you must explicitly cast it to the text type.

Since TEXT, VARCHAR, and CHARare binary coercible, PostgreSQL implicitly casts them to TEXT, so you don’t have to perform an explicit conversion.

The UPPER function returns NULL if the input string is NULL.

Note that the UPPER function does not modify the original string but returns a new string.

Basic UPPER function examples #

The following example uses the UPPER function to convert all the characters in the string "pgtutorial.com" to uppercase:

SELECT
  UPPER('pgtutorial.com') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
----------------
 PGTUTORIAL.COMCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement returns NULL because the input string is NULL:

SELECT
  UPPER(NULL) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Converting table data to uppercase #

Let’s use the products table from the inventory database:

The following query uses the UPPER function to convert the product names from the products table to uppercase:

SELECT
  UPPER(product_name) formatted_product_name
FROM
  products
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   formatted_product_name
----------------------------
 APPLE AIRPODS PRO 3
 APPLE IMAC 24"
 APPLE IPAD PRO 12.9
 APPLE IPHONE 15
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Matching data case-insensitively #

When matching data using the equal to operator, PostgreSQL matches it case-insensitively.

For example, if you search for a product with the name "APPLE IPHONE 15", PostgreSQL will return an empty set:

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name = 'APPLE IPHONE 15';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

To match data case-insensitively, you can use the UPPER function in the WHERE clause as follows:

SELECT
  product_name,
  price
FROM
  products
WHERE
  UPPER(product_name) = 'APPLE IPHONE 15';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

  product_name   |  price
-----------------+---------
 Apple iPhone 15 | 1099.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the UPPER function to convert all characters of a string to uppercase.