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:

pg_ctl -D /usr/local/var/postgres status
  • MacOS users, try the Homebrew method for installing PostgreSQL like this:
brew info postgres

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:
psql postgres
  • Alternatively, use syntax psql -U and -h flags to connect. You’ll input the host, username, and name of the database.
psql -U some_username -h 127.0.0.1 -d some_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.
SELECT current_database();

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.
SELECT * FROM pg_user;

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:
SELECT * FROM users;
  • You should see something similar to this:
id | firstname | lastname | address | username | password
----+-----------+-----------+----------------------------+---------------------+------------
1 | Mica | Kagome | 123 Purok 3 Avenue | mickago@gmail.com | mic1234
2 | Richmond | Francisco | Diko Alam Street | rkmond@ymail.com | password
3 | Enanreh | Lagidac | North Ville 16 | admin@admin.com | admin
4 | Sirciram | Sibmat | Blk10 Lot 60 Matipuno Str. | sibmat@gmail.com | pass123
5 | Mica | Sanders | 567 Alam Ko Na Str | sanders@hotmail.com | mypassword
(5 ROWS)
  • Try querying the username, which is a specific column, in your database table:
SELECT username FROM users;
  • You should see something like this returned in the results:
username
-------------------
mickago@gmail.com
rkmond@ymail.com
admin@admin.com
sibmat@gmail.com
sanders@hotmail.com
(5 ROWS)

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:
SELECT firstname, lastname FROM users
ORDER BY firstname DESC;
  • Based on the example shown above, your results should look similar to this one here:
firstname | lastname
-----------+-----------
Sirciram | Sibmat
Richmond | Francisco
Mica | Kagome
Enanreh | Lagidac
Mica | Sanders
(5 ROWS)
  • Now try to perform a row filtering using the SELECT command with the clause WHERE like this:
SELECT firstname, lastname FROM users
WHERE username = 'mickago@gmail.com';
  • Your results should resemble something like this:
firstname | lastname
-----------+----------
Mica | Kagome
(1 ROW)

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.
SELECT * FROM users LIMIT 2;
  • The example below shows what will be returned:
id | firstname | lastname | address | username | password
----+-----------+-----------+--------------------+-------------------+----------
1 | Mica | Kagome | 123 Purok 3 Avenue | mickago@gmail.com | mic1234
2 | Richmond | Francisco | Diko Alam Street | rkmond@ymail.com | password
(2 ROWS)
  • 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.
SELECT * FROM users LIMIT 2 OFFSET 2;
  • 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.

id | firstname | lastname | address | username | password
----+-----------+----------+----------------------------+------------------+----------
3 | Enanreh | Lagidac | North Ville 16 | admin@admin.com | admin
4 | Sirciram | Sibmat | Blk10 Lot 60 Matipuno Str. | sibmat@gmail.com | pass123
5 | Mica | Sanders | 567 Alam Ko Na Str | sanders@hotmail.com | mypassword
(5 ROWS)
  • You can FETCH a particular row as well. Here’s an example of the SELECT with ORDER BY and FETCH:
SELECT firstname, lastname FROM users
ORDER BY id FETCH FIRST ROW ONLY;
  • Your results should resemble something like this:
firstname | lastname
-----------+----------
Mica | Kagome
(1 ROW)

Use the Postgres ‘SELECT’ statement to join table data

  • Join tables with the Postgres SELECT statement and the clause INNER JOIN:
SELECT * FROM table1 INNER JOIN table2 ON conditions
  • There is also the clause LEFT JOIN:
SELECT * FROM table1 LEFT JOIN table2 ON conditions
  • Another way to join tables is by using the clause FULL OUTER JOIN:
SELECT * FROM table1 FULL OUTER JOIN table2 ON conditions
  • Here’s an example of the clause CROSS JOIN:
SELECT * FROM table1 CROSS JOIN table2 ON conditions
  • Use SELECT with the clause NATURAL JOIN:
SELECT * FROM table1 NATURAL JOIN table2 ON conditions

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:
SELECT DISTINCT firstname FROM users;
  • You should see a response that closely resembles this one:
firstname
-----------
Sirciram
Mica
Richmond
Enanreh
(4 ROWS)
  • Use the operator UNION to make just one combined result out of at least two SELECT statements:
SELECT FROM table1 UNION SELECT * FROM table2;
  • Use the operator INTERSECT to show rows that match the results of two separate PostgreSQL SELECT statements:
SELECT * FROM table1 INTERSECT SELECT * FROM table2;
  • For comparing purposes, use the operator EXCEPT to return rows that apply to one of the SELECT statements:
SELECT * FROM table1 EXCEPT SELECT * FROM table2

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.

SELECT firstname || ' ' || lastname
AS fullname, username FROM users;
  • The results should look something like this:
fullname | username
--------------------+---------------------
Mica Kagome | mickago@gmail.com
Richmond Francisco | rkmond@ymail.com
Enanreh Lagidac | admin@admin.com
Sirciram Sibmat | sibmat@gmail.com
Mica Sanders | sanders@hotmail.com
(5 ROWS)
  • An operator that is mathematical expression is shown here:
SELECT 10 + 10 AS TOTAL_SUM;
  • The results should look something like this one below:
total_sum
-----------
20
(1 ROW)

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.

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.