How to Use the Postgres Similar To Operator on an ObjectRocket instance

Introduction

The Postgres SIMILAR TO operator works similar to the LIKE operator, but the SIMILAR TO function uses the SQL standard’s definition of a regular expression to interpret the pattern. However, unlike the ordinary regular expression action, where the pattern may match any part of the string, the SIMILAR TO operator succeeds only when the pattern matches the entire string. The Postgres SIMILAR TO operator will only return a true response if its pattern matches the specified string, otherwise a false response is returned. This tutorial will explain how to use the Postgres SIMILAR TO operator on an ObjectRocket instance.

Prerequisites

The following software programs must be properly installed and configured in order to use the Postgres SIMILAR TO operator on an ObjectRocket instance.

• PostgreSQL 11 must be properly installed and running on the local system.

• An instance of Postgres must be set up in an ObjectRocket account in the Mission Control panel, shown here:

What Is PostgreSQL SIMILAR to Operator

The SIMILAR TO operator works similarly to the LIKE operator. The Postgres SIMILAR TO operator is a matching utility that returns a true response when a match is found against a given string. If a positive match is not found, then the operator will return a false result.

How to Connect to PostgreSQL Instance

Before being able to perform the SIMILAR TO operation, a connection to the PostgreSQL instance must be set up on ObjectRocket though either the PSQL utility or terminal.

Connect via the PSQL Utility

• The first connection option, shown here, is via the psql shell that comes included with the PostgreSQL installation:

• The second way to establish a connection is by way of the terminal, as follows:

Sample Data Set

Following is a sample data set that will be used for demonstration purposes in this tutorial:

Postgres SIMILAR to Example

The following example shows how to retrieve all country_name that begin with the letters an:

Following is a breakdown of the above statement:

• First, the SELECT statement is followed by the FROM clause and then the name of the table country is specified.

• Next, the WHERE clause is combined with the SIMILAR TO operator to further refine the results. This will cause the operation to return records having a country_name that begins with the letters an.

Here is another example using an operator (|):

Notice the operator, or (|), was used in the above statement to retrieve a country_name that begins with either the letters al o an.

Conclusion

This tutorial explained how to use the Postgres SIMILAR TO operator on an ObjectRocket instance. The tutorial explained what the PostgreSQL SIMILAR TO operator is and two ways of connecting to a PostgreSQL instance, one through the terminal and one via the PSQL utility. The article provided a sample data set, showed two Postgres SIMILAR TO working examples and then gave a breakdown of the syntax used in the SIMILAR TO statement. Remember that while the SIMILAR TO operator works similarly to the LIKE operator, the SIMILAR TO function will succeed only when the pattern matches the entire string.