Querying for records stored in a PostgreSQL table using SQL

In the last article in my PostgreSQL series we talked about how to create data using the INSERT statement. The next step in our progression is going to involve looking at different ways to query the data we have stored so that we can effectively use any data we store in our database.

SQL is an incredibly powerful language that allows for the creation of some very complex and specific queries. For example, you could create a query to find every user who has spent over $100 in your store by joining data from the users and transactions table and summing the amount of every user’s transactions. While this type of query is useful in the real world, it would be next to impossible to truly understand these queries without first learning about the basics of querying with SQL.

In this article we will be doing just that; We will be looking at the SELECT statement and using it along with several different clauses that allow us to both filter and order the data that we retrieve from a single SQL table. Once you have a solid understanding of how SELECT statement work a single table you will be prepared to further your SQL education and start learning about more advanced statements.

Introducing the SELECT statement

All of our SQl queries are going to start with the SELECT statement. This is our way of telling an SQL database both what data we want to retrieve and where we want to retrieve it from.

For example, if we wanted to query a user from the users table we created in the last article we could do so by first connecting to our database with the postgres role.

psql -U postgres -d calhounio_demo

And then running the following SQL to select the email address of every user in our users table.

SELECT email FROM users;

Doing this should result in a list of email addresses similar to mine below.

     email
----------------
 jon@calhoun.io
 john@smith.com
(2 rows)

The SELECT statement is broken into roughly two parts in this example. First we specify which columns we want to retrieve from our database. That is the email portion of the line SELECT email FROM users;.

The second is the table that we want to retrieve data from. This is the FROM users; portion of our query. We are stating that we want the email field from records in the users table.

Using the asterisk (*) with SELECT

While it is useful to be able to specify which columns you want to retrieve data for, there will likely also be times where rather than specifying every field you want, you would instead just like to get all of the data available in a table.

This is done by using the asterisk in place of the the field names.

SELECT * FROM users;

Which will yield results similar to those below.

 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  6 |  30 | Jonathan   | Calhoun   | jon@calhoun.io
  2 |  22 | John       | Smith     | john@smith.com
(2 rows)

This should look familiar because we used it briefly in the last post.

Filtering with the WHERE clause

The next thing we need to look at is how to filter data. In SQL this is done with the WHERE clause. For example, if you wanted to find the user with the email address “jon@calhoun.io” you could do so with the following query.

SELECT *
FROM users
WHERE email = 'jon@calhoun.io';

Go head, try to run it on your own. You should only get one result back due to our email field having a UNIQUE requirement.

The WHERE clause can also be used with any conditional clause, but the most common ones you will typically see and use are >, <, and =, which are all pretty self explanatory.

Combining conditionals with AND and OR

You can also use the keywords AND and OR to combine multiple conditional clauses together. For example, you might want to find all users who have a last name that comes after “Douglas” alphabetically, and hare over 16 years old. To do this you could use the AND statement to join your two conditional clauses together, and you would end up with the following SQL.

SELECT *
FROM users
WHERE last_name > 'Douglas'
AND age > 16;

The AND statement requires that both conditions are met for a record to be returned, and the OR statement only requires one of the conditions to be met for a record to be returned.

Up Next…

While we didn’t get into the details of complex queries, this should give you a basic understanding of how to create simple queries on a single table in your database. With this knowledge you would be able to start creating a real web application; Even if your SQL queries aren’t the most efficient, you would be able to make a working prototype to extend.

Next we will discuss Updating and Deleting records stored in a PostgreSQL table using SQL, and after that point we will be ready to start using SQL with Go, but remember that there is a lot more to SQL than what we covered, and you might need to expand your understanding of SQL depending on the complexities of your application.