{"id":1819,"date":"2025-02-02T11:24:34","date_gmt":"2025-02-02T04:24:34","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1819"},"modified":"2025-02-10T15:07:33","modified_gmt":"2025-02-10T08:07:33","slug":"postgresql-lower","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-string-functions\/postgresql-lower\/","title":{"rendered":"PostgreSQL LOWER Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL LOWER<\/code> function to convert all characters of a string to lowercase.<\/p>\n\n\n\n

Introduction to the PostgreSQL LOWER function #<\/a><\/h2>\n\n\n\n

The LOWER<\/code> function takes a string and returns a new string with all characters of the input string converted to lowercase.<\/p>\n\n\n\n

Here’s the syntax of the LOWER<\/code> function:<\/p>\n\n\n

LOWER(\u00a0text\u00a0) \u2192\u00a0text<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The LOWER<\/code> function returns a new new string with all characters in the input string converted to lowercase.<\/p>\n\n\n\n
Since TEXT<\/code>, VARCHAR<\/code>, and CHAR<\/code>are binary coercible,<\/strong> PostgreSQL implicitly casts the VARCHAR<\/code> and CHAR<\/code> to TEXT<\/code>, so you don’t have to perform an explicit conversion.<\/p>\n\n\n\n
The LOWER<\/code> function returns NULL<\/code> if the input string is NULL<\/code>.<\/p>\n\n\n\n
Note that the LOWER<\/code> function does not change the original string but returns a new string.<\/p>\n\n\n\n
Basic LOWER function examples #<\/a><\/h2>\n\n\n\n
The following example uses the LOWER<\/code> function to convert all the characters in the string \"PostgreSQL\"<\/code> to lowercase:<\/p>\n\n\n
SELECT<\/span>\n  LOWER<\/span>('PostgreSQL'<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   result\n------------<\/span>\n postgresql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement use the NULL as the input string and returns NULL<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  LOWER<\/span>(NULL<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Converting table data to uppercase #<\/a><\/h2>\n\n\n\n
Let’s use the products<\/code> table from the inventory<\/code> database:<\/p>\n\n\n\n
\"PostgreSQL<\/figure>\n\n\n\n
The following query uses the LOWER<\/code> function to convert the product names from the products<\/code> table to uppercase:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  LOWER<\/span>(product_name) formatted_product_name\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        product_name        |   formatted_product_name\n----------------------------+----------------------------<\/span>\n Apple AirPods Pro 3        | apple airpods pro 3\n Apple iMac 24\"             | apple imac 24\"\n Apple iPad Pro 12.9        | apple ipad pro 12.9\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Matching data case-insensitively #<\/a><\/h2>\n\n\n\n
When matching data using the equal to operator (=<\/code>),  PostgreSQL compares string case-sensitively.<\/p>\n\n\n\n
For example, if you search for a product with the name \"dell inspiron 27\"<\/code>, PostgreSQL will return an empty set:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  product_name = 'dell inspiron 27'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To compare strings case-insensitively, you can use the LOWER<\/code> function in the WHERE<\/a><\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  LOWER<\/span>(product_name) = 'dell inspiron 27'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/p>\n\n\n
   product_name   | price\n------------------+--------<\/span>\n Dell Inspiron 27 | 999.99<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the LOWER<\/code> function to convert all characters of a string to lowercase.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
If you pass a value of a type other than TEXT<\/a><\/code>, VARCHAR<\/a><\/code>, and CHAR<\/a><\/code> to the LOWER()<\/code> function, you must explicitly cast it to the TEXT<\/code> type.<\/p>\n\n\n\n