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
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.
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
SELECT email FROM users;
Doing this should result in a list of email addresses similar to mine below.
email ---------------- firstname.lastname@example.org email@example.com (2 rows)
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
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
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 | firstname.lastname@example.org 2 | 22 | John | Smith | email@example.com (2 rows)
This should look familiar because we used it briefly in the last post.
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 “firstname.lastname@example.org” you could do so with the following query.
SELECT * FROM users WHERE email = 'email@example.com';
Go head, try to run it on your own. You should only get one result back due to our email field having a
WHERE clause can also be used with any conditional clause, but the most common ones you will typically see and use are
=, which are all pretty self explanatory.
You can also use the keywords
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;
SQL database variations
It is important to note here that not all SQL databases operate the same under the hood. For example, not all SQL databases are case-sensitive, but Postgres IS case sensitive. As a result, there is a difference between
last_name > 'douglas' and
last_name > 'Douglas'.
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.
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.
Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.
You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.
Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)
Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.
Jon's latest progress update: Writing Course Notes
More in this series
This post is part of the series, Using PostgreSQL with Go.
Spread the word
Did you find this page helpful? Let others know about it!
Sharing helps me continue to create both free and premium Go resources.
Want to discuss the article?
©2018 Jonathan Calhoun. All rights reserved.