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

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

Screenshot

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:
1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [PostgreSQL]: <your_database_name>
Port [5432]: 4149
Username [PostgreSQL]: pguser
Password FOR USER orkb: <your_password>
  • The second way to establish a connection is by way of the terminal, as follows:
1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149

Sample Data Set

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |    country_name     | country_code
----+---------------------+--------------
  1 | afghanistan         | 93
  2 | albania             | 355
  3 | algeria             | 213
  4 | american samo       | 1-684
  5 | andora              | 376
  6 | argentina           | 54
  7 | angola              | 244
  8 | anguilla            | 1-264
  9 | antartica           | 672
 10 | antigua and barbuda | 1-268
(10 rows)

Postgres SIMILAR to Example

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

1
2
3
4
5
6
7
8
9
sandbox=> SELECT * FROM country WHERE country_name SIMILAR TO 'an%';
 id |    country_name     | country_code
----+---------------------+--------------
  5 | andora              | 376
  7 | angola              | 244
  8 | anguilla            | 1-264
  9 | antartica           | 672
 10 | antigua AND barbuda | 1-268
(5 ROWS)

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 (|):

1
2
3
4
5
6
7
8
9
10
11
12
sandbox=> SELECT * FROM country WHERE country_name SIMILAR TO '%(an|al)%';
 id |    country_name     | country_code
----+---------------------+--------------
  1 | afghanistan         | 93
  2 | albania             | 355
  3 | algeria             | 213
  4 | american samo       | 1-684
  5 | andora              | 376
  7 | angola              | 244
  8 | anguilla            | 1-264
  9 | antartica           | 672
 10 | antigua AND barbuda | 1-268

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.