Discover PostgreSQL table data with SELECT statement.

Proceeding from Part 1 and Part 2 of this series, today’s post will involve reading stored data in a PostgreSQL table using the SELECT statement along with filtering criteria in the WHERE clause for specific record searches. Modifying table data with the UPDATE command will also be visited to round out this discussion.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), PostgreSQL 9.6.2 and pgAdmin for these exercises.

The SELECT statement is likely one of the more frequently used identifiers in SQL. Specifically, for reading data. SELECT is used to retrieve rows from a table or view. Akin to most commands covered in this series, an attempt to list every available option for the SELECT statement in one post would offer little justice to it. Online documentation is available should your needs or curiosity be piqued. This blog post will explore commonly used choices for basic viewing and reading of stored table data.

Below is a basic form of the SELECT statement syntax. As I progress through the post, all elements will be discussed in turn.

SELECT all_columns_or_specific_columns  -- separate multiple columns with commas
FROM table or multiple_tables
WHERE some_condition(s)_is_met_or_true;  -- optional

---

See it all…

One way of viewing all the contents of a table is by using the * character with SELECT. To demonstrate, I will revisit the galaxy_user table present throughout the series.

Producing the below output:

When used in this manner, the * character means return ‘all’ columns from the table.

---

Is that a good idea?

A point worth noting. Exercise caution when querying a table with the * character. Especially in those situations, when you are unfamiliar with the table and the amount of data stored. In this example table, 6 rows returned is not so bad. What if there were 60,000 rows? Or more? Would that have been a good idea to load all that information blindly?

A safer usage for the * character is to obtain a count of how many rows are present in the table. Then decide if loading all the rows is feasible or not. Or even needed.

How can you use the * character without loading all the rows?

The syntax below provides an example.

COUNT is a SQL Aggregate function. If you are interested in SQL Aggregate functions, visit this post where I present a practical use case for inventory and asset tracking needs, leveraging a number of these exceptionally useful and powerful functions.

Executing the above query returns:

Utilizing the * character and the COUNT aggregate function together produces a count of the total input rows found in the table. The galaxy_user table has 6 rows.

A couple of takeaways for the SELECT statement:

• Used to retrieve rows of data from tables or views.
• When a WHERE clause is included in the SELECT statement, any rows that do not meet the filter condition, are not returned in the query results.

---

I need results for just one column of data.

Imagine you only need the galaxy_coins column values. You can specify just that column, to be returned, in the SELECT statement.

That query would look like this:

SELECT galaxy_coins
FROM galaxy_user;

Separate multiple columns you want to be returned in the query results, by commas:

SELECT user_name,email -- all columns you want returned separated by commas
FROM galaxy_user;

The user_name and email columns are both returned in the query results since those two columns were specified in the SELECT statement.

---

I need to locate a certain record.

Oftentimes, you will be searching for one specific record. Even then, just one column or field within that record may satisfy your needs.

Using the optional WHERE clause with the SELECT statement enables you to specify just what row(s) to return, based on filter condition(s) that evaluate to a boolean expression result. Perhaps you need the email address value for the record with the user_name ‘remy292’.

The user_id column can be utilized, as the filter condition in the WHERE clause.

How can you use that in the query?

SELECT *
FROM galaxy_user
WHERE user_id = 2;

Specifying the filter condition user_id = 2, this query will only return data (if there is a match) for the row that contains the value 2 in the user_id column. Since no other row contains the value 2 in the user_id column, except the record for user ‘remy292’, the boolean expression result evaluates to true and this record is returned.

Alternately, the user_name column value can be used in the filter condition as well. That query would look like this:

SELECT * 
FROM galaxy_user
WHERE user_name = 'remy292';

Likewise, since no other row has a user_name column value of ‘remy292’, that record is returned for the query result.

---

Pay attention when including any string character values (or date values) in the WHERE clause filter condition. Leaving out the single quotes surrounding the user_name value results in an error:

SELECT * 
FROM galaxy_user
WHERE user_name = remy292;
ERROR:  column "remy292" does not exist
LINE 3: WHERE user_name = remy292;
                          ^

---

These types of checks for equality are used quite often, however, you can check for values just as easily that is not equal to a certain column value. Suppose you needed rows returned for the records where the user_id column value is not equal to 2. Those records can be returned using the not equals comparison operator in the WHERE clause filter condition.

Either != or <> tests a not equals comparison filter. Although I tend to use !=, the choice is ultimately yours. Both operators hold the same meaning.
Here is an example of that query (no results are shown)

SELECT *
FROM galaxy_user
WHERE user_id != 2;

---

In addition to equality checks and operators, greater than and less than comparison operators are useful in checking column values for specific search results.

Which users have a galaxy_coins balance of less than 25.00?

This query will tell you:

Here I am selecting the user_name and galaxy_coins columns for any rows where the galaxy_coins column value is less than 25.00, using the less than comparison operator (‘< '). Returning:

A few WHERE clause takeaways:

• Is optional.
• The condition checked by the WHERE clause evaluates to a boolean result (true or false).
• This is a great explanation, quoted directly from the online documentation detailing what row(s) are returned: “A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.”

---

Yes, but I need to change some values in my table.

User ‘remy292’ has just requested to change the password associated with their account.

How can you change the pass_word column value to the new value that user ‘remy292’ wants?

By using the UPDATE command in conjunction with the SET command and the WHERE clause. The syntax to handle that request would be structured like so:

UPDATE table_name
SET column_to_update = value_you_want_to_set
WHERE some_conditional;

How can I construct the exact syntax to complete this update?

UPDATE galaxy_user -- specifying the change(s) will happen in the galaxy_user table
SET pass_word = 'downtree22' -- setting the pass_word column value here to 'downtree22'
WHERE user_id = 2; -- this boolean expression means only perform this where the user_id column value is 2

When executing this update statement, an informative output is displayed in the Linux terminal:

UPDATE 1

Signifying 1 row was successfully updated.

And I can verify the changes by querying the table for that user:

Displaying this record was updated with the new pass_word column value:

I will dissect the query line by line:

• UPDATE galaxy_user (This is the table to update).
• SET pass_word = ‘downtree22’ (The value for column pass_word is being changed to ‘downtree22.’ Careful here.)
• WHERE user_id = 2; (The row containing 2 as the value of the user_id column. This line is most critical.)

What would have been the outcome if ‘WHERE user_id = 2;’ had not been part of the UPDATE command?

All pass_word column values would have been altered and SET to the value ‘downtree22’.

Yikes! Surely not the intention but nevertheless, the outcome is the same.

There may be times when you do want to carry out an update such as this across multiple rows, and in those instances, UPDATE and SET can be used. Just a friendly reminder. Be careful when updating data in your table(s)! With or without the WHERE clause present in the query, you can potentially affect multiple rows.

A few UPDATE takeaways:

• Will change the specified column(s) value for the row(s) that satisfy the condition set forth in the WHERE clause.
• UPDATE  returns a tag

  UPDATE count

  Where count is the number of updated rows for a successful completion.

• The desired columns to be modified must be named in the SET clause.

---

Closing thoughts.

With SELECT, UPDATE and SET you are able to monitor, modify, and manage table data as needs and circumstances dictate. Through reading and searching different records within tables, you should have a solid idea of how to execute these ever-changing requirements of an active database and table. Explore these commands to gain deeper insight into their uses.

Still, to come in the final post of this series, I will demonstrate deleting data from a PostgreSQL table. Make sure and subscribe so you don’t miss out on more great PostgreSQL content!

---

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ button in the sidebar!

---

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.