Postgres Select Statement

Introduction on the Postgres select statement

The SELECT statement is used to query table data in an efficient way. One of the benefits of applying SELECT is that you can combine it with a variety of clauses. Having that flexibility enables those in the technical field to customize their queries to suit their needs. For example, users can use a clause to show a particular column within a table. They can also sort with ORDER BY, perform a row filter with the WHERE clause, or return a row subset with FETCH or LIMIT. Learn more about it and so much more with this overview of the Postgres SELECT statement.

Prerequisites to using psql to make PostgreSQL queries

• Make active the PostgreSQL on your OS. If you haven’t installed it yet, do so now.

• Check the version of PostgreSQL on your OS by using the command postgres -V at the PostgreSQL command line.

• At a command prompt, enter the utility pg_ct1 to get the process identifer (PID) information about the status of PostgreSQL like this:

• MacOS users, try the Homebrew method for installing PostgreSQL like this:

Accessing PostgreSQL using the ‘psql’ command line interface

• Enter the command psql to connect to a database in PostgreSQL on the server on your localhost:

• Alternatively, use syntax psql -U and -h flags to connect. You’ll input the host, username, and name of the database.

NOTE: The flags represent different things. The -d flag username is represented by the -U flag, and the -h is for the IP address or host. If you don’t use both parameters, whichever one you use, PostgreSQL will consider it as the name of the database.

Use the Postgres ‘SELECT’ command to retrieve database information

Now, you’ll learn the steps to retrieve database information by using the Postgres SELECT statement.

NOTE: Within psql, you can make a database connection with the \c command as long as you name the database to which you want to connect. For example, \c database_name.

Use the SQL ‘SELECT’ statement to get the current Postgres database

• If you’re unsure of the database name, enter the command current_database() to get it.

Use the ‘SELECT’ statement to get pg_user information

• Here’s an example of how to use the Postgres SELECT statement to obtain pg_user data.

Use the Postgres ‘SELECT’ keyword to query data in a table

Display data within a table using the Postgres SELECT statement.

• Try to query using this simple example:

• You should see something similar to this:

• Try querying the username, which is a specific column, in your database table:

• You should see something like this returned in the results:

Use the Postgres ‘SELECT’ keyword with ‘ORDER BY’

• You can also use the Postgres SELECT statement to sort the rows by first and last name or by any other column combination with the clause ORDER BY like this:

• Based on the example shown above, your results should look similar to this one here:

• Now try to perform a row filtering using the SELECT command with the clause WHERE like this:

• Your results should resemble something like this:

Use the Postgres ‘SELECT’ statement with the ‘LIMIT’ and ‘FETCH’ clauses

• Use the LIMIT clause with the SELECT statement to display a table row subset.

• The example below shows what will be returned:

• Specify the start of the rows you want to be returned by using OFFSET along with LIMIT. This example shows the offset by 2, so the rows that will be returned will begin with the third row. The limit is two rows as well.

• The results should look similar to this one here:

NOTE: The first row is the admin row; therefore, it’s not included in the user LIMIT count.

• You can FETCH a particular row as well. Here’s an example of the SELECT with ORDER BY and FETCH:

• Your results should resemble something like this:

Use the Postgres ‘SELECT’ statement to join table data

• Join tables with the Postgres SELECT statement and the clause INNER JOIN:

• There is also the clause LEFT JOIN:

• Another way to join tables is by using the clause FULL OUTER JOIN:

• Here’s an example of the clause CROSS JOIN:

• Use SELECT with the clause NATURAL JOIN:

In your coding, you’ll find PostgreSQL operators useful with SELECT statements too.

• Delete rows in a table that are duplicated with the operator DISTINCT:

• You should see a response that closely resembles this one:

• Use the operator UNION to make just one combined result out of at least two SELECT statements:

• Use the operator INTERSECT to show rows that match the results of two separate PostgreSQL SELECT statements:

• For comparing purposes, use the operator EXCEPT to return rows that apply to one of the SELECT statements:

Using the expression in ‘SELECT’ query

• Here’s an example of the SELECT statement with an expression in the query.

NOTE: The query example illustrates an expression to combine the first name and last name in each row. Next, it uses AS Alias to assign a temporary column name, which is fullname, for the results.

• The results should look something like this:

• An operator that is mathematical expression is shown here:

• The results should look something like this one below:

Conclusions on how to use the Postgres select statement

The Postgres SELECT statement gives developers, DBAs, and other technical professionals many options for creating custom queries. Use SELECT along with specific clauses to limit rows, filter them, join tables, and so much more. SELECT works with PostgreSQL operators too. This is helpful for shortening long columns in the results display. Take advantage of what SELECT to simplify your complex queries and boost productivity today.