PostgreSQL Regular Expressions

Summary: in this tutorial, you’ll learn how to use the PostgreSQL regular expressions to match text strings.

Introduction to POSIX regular expressions #

A regular expression, regex, is a sequence of characters that defines a search pattern.

For example, the \d+ regular expression matches one or more digits. They can be any digits, not specific to particular digits. The string "iPhone 15" will match the regular expression \d.

PostgreSQL supports POSIX regular expressions, a standardized set of regular expressions defined by POSIX (Portable Operating System Interface).

The Perl Compatible Regular Expressions (PCRE) are another standard besides the POSIX regular expressions. PostgreSQL does not natively support PCRE regular expressions.

Let’s understand how POSIX regular expressions work.

Character Classes #

• \d matches any digit. It is equivalent to [0-9]
• \s matches any whitespace character.
• \w matches any word character, including letters, digits, and underscores. It is equivalent to [a-zA-Z0-9_].
• The uppercase letter negates the meaning of the character classes:
• \D matches any non-digit character, equivalent to [^0-9].
• \S matches any non-whitespace character.
• \W matches any non-word character.

Anchors #

Anchors match a position instead of characters:

• ^ matches the beginning of a string.
• $ matches the end of a string.

Quantifiers #

Quantifiers match the number of instances of a character set:

• * matches zero or more.
• + matches one or more.
• ? matches zero or one.
• {n} matches exactly n times.
• {n,} matches at least n times.
• {n,m} matches a range from n to m times, where n < m.

Sets & Ranges #

Use [] to create a set that matches any character in the set. For example, [abc] matches a, b, or c.
Use a hyphen - within the square brackets [] to create a range. For example, [a-z] is a range of characters from a to z. [0-9] is a range of digits from 0 to 9.
Use ^ inside [] to exclude a set or range. For example, the set [^0-9] matches any character except a digit.

Alternation #

Use | to represent alternation, which is like the OR operator. For example, the regular expression a|b matches a or b.

PostgreSQL Regular Expression Operators #

PostgreSQL allows you to use POSIX regular expressions with the following operators:

• ~ matches a pattern.
• ~* case-insensitively matches a pattern.
• !~ does not match a pattern.
• !~* case-insensitively does not match a pattern.

PostgreSQL Regular Expression Examples #

Let’s explore some examples of using regular expressions with the products table:

Matching Character Classes #

The following query uses the match operator (~) to find products whose names contain three digits:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~ '\d{3}';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name
-------------------------
 Samsung QN900C Neo QLED
 Sony HT-A7000 Soundbar
 HP Spectre x360Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the match operator (~) to find products whose names contain the letter S followed by two digits:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~ 'S\d{2}';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name
--------------------
 Samsung Galaxy S24Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Matching Positions #

The following statement uses the match operator (~) to find products with names starting with the letter H:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~ '^H';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  product_name
-----------------
 Huawei Mate 60
 HP Spectre x360Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the match operator (~) to find products with names ending with two digits:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~ '\d{2}$';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name
--------------------
 Samsung Galaxy S24
 Apple iPhone 15
 Huawei Mate 60
 Xiaomi Mi 14
 Dell XPS 15
 HP Spectre x360
 Dell Inspiron 27Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Matching iPhone or Galaxy #

The following query uses the match operator (~) to find products with names that contain “iPhone” or “Galaxy”:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~ 'iPhone|Galaxy';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name
---------------------------
 Samsung Galaxy S24
 Apple iPhone 15
 Samsung Galaxy Z Fold 5
 Apple iPhone 15 Pro Max
 Samsung Galaxy Tab S9
 Samsung Galaxy Buds Pro 2
 Samsung Galaxy Watch 6Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To match “iPhone” and “Galaxy” case-insensitively, you can use the ~* operator:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~* 'iphone|galaxy';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name
---------------------------
 Samsung Galaxy S24
 Apple iPhone 15
 Samsung Galaxy Z Fold 5
 Apple iPhone 15 Pro Max
 Samsung Galaxy Tab S9
 Samsung Galaxy Buds Pro 2
 Samsung Galaxy Watch 6Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finding Product Names that Do Not Match a Pattern #

The following query uses the operator (!~) to find products with names that do not contain digits:

SELECT
  product_name
FROM
  products
WHERE
  product_name !~ '\d+';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name
--------------------
 Bose SoundLink MaxCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• PostgreSQL supports POSIX regular expressions.
• Use a regular expression operator to match a string against a pattern.

Quiz #