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:
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 theFROM
clause and then the name of the tablecountry
is specified.Next, the
WHERE
clause is combined with theSIMILAR TO
operator to further refine the results. This will cause the operation to return records having acountry_name
that begins with the lettersan
.
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