How to Use PostgreSQL Complex Queries Part 2

Introduction

When you’re working with data in PostgreSQL, you’ll find that queries can range quite a bit in complexity. While you may sometimes get the answers you need with a simple and straightforward query, other situations call for complex queries that could include subqueries and joins. In this article, we’ll continue our discussion of PostgreSQL complex queries and provide more examples of their use.

Prerequisites

If you’d like to follow along with the examples in this article, a couple of key prerequisites need to be in place:

  • First, you’ll need to have PostgreSQL installed on your computer. This is necessary in order to execute the PostgreSQL complex queries presented throughout the tutorial

  • It’s also helpful to have some basic knowledge of PostgreSQL to get the most out of the instructions in this tutorial.

Complex Queries Examples

Let’s look at some examples of complex queries you may need to use when working with tables in PostgreSQL:

In our example, we’ll use a table named employees. This table contains an id along with information on each employee’s name, department and salary:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name | age | department_name | salary
----+------------+-----------+-----+-----------------+--------
7 | Ella May | Stevens | 31 | Accounting | 5000
9 | Ayyan | Bourne | 30 | Accounting | 5000
1 | Lacie | Serrano | 31 | Accounting | 5000
2 | Marius | Bryne | 27 | Engineering | 7300
3 | Anita | Matthams | 28 | Engineering | 7300
10 | Manha | Ferry | 28 | Engineering | 7300
4 | Maryam | Cohen | 36 | Marketing | 5300
8 | Ruari | Ball | 26 | Marketing | 5300
5 | Ronald | Mcmillan | 32 | IT | 6000
6 | Payton | Glover | 35 | IT | 6000
(10 ROWS)

Selecting For An Specific Values

To select records where a column’s value matches a specific pattern, we can use the LIKE operator in conjunction with certain wildcard characters.

For example, let’s imagine we’d like to get a list of all employees who have the letter ‘a’ as the second character of their first name. In the query shown below, two wildcards are used in conjunction with the LIKE operator. The underscore represents any single character, and the percent sign can represent zero or more characters:

1
2
3
4
5
6
7
8
9
SELECT * FROM employees WHERE first_name LIKE '_a%';
id | first_name | last_name | age | department_name | salary
----+------------+-----------+-----+-----------------+--------
1 | Lacie | Serrano | 31 | Accounting | 5000
2 | Marius | Bryne | 27 | Engineering | 7300
10 | Manha | Ferry | 28 | Engineering | 7300
4 | Maryam | Cohen | 36 | Marketing | 5300
6 | Payton | Glover | 35 | IT | 6000
(5 ROWS)

We can see that the returned list included all employees that have ‘a’ as the second character of their first name.

Counting A Column

Let’s take this query to the next level and make it a bit more complex.

In our next example, we’ll display the number of employees that have ‘a’ as the second character of their first name. When you just want to return the number of records that match the query conditions instead of the records themselves, you can use the COUNT() function to accomplish the task. Our query will look like the following:

1
2
3
4
5
SELECT COUNT(*) FROM employees WHERE first_name LIKE '_a%';
COUNT
-------
5
(1 ROW)

We can see that five records matched the query conditions.

Creating Table From Another Table

If we’d like to create a table that has the same column structure as an existing table, we can use a query like the one shown below:

1
CREATE TABLE EMPLOYEES2 AS (SELECT * FROM employees WHERE 1=2);

Now that we created this new table, let’s retrieve a list of all available tables using the \dt command:

1
2
3
4
5
List OF relations
Schema | Name | TYPE | Owner
--------+-------------+-------+----------
public | employees | TABLE | postgres
public | employees2 | TABLE | postgres

We can see that our newly-created table shows up in the list.

Conclusion

It’s clear that PostgreSQL queries can range from simple and straightforward to complex and lengthy. In this two-part series, we’ve taken an in-depth look at PostgreSQL complex queries. We’ve provided several examples of these complex queries that you can use as a guide. With these instructions and examples, you’ll be able to construct different types of PostgreSQL queries to retrieve the information and insights you need.

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.