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:
----+-----------+-----------+----------------------------+---------------------+------------
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:
- You should see something like this returned in the results:
-------------------
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:
ORDER BY firstname DESC;
- Based on the example shown above, your results should look similar to this one here:
-----------+-----------
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:
WHERE username = 'mickago@gmail.com';
- Your results should resemble something like this:
-----------+----------
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.
- The example below shows what will be returned:
----+-----------+-----------+--------------------+-------------------+----------
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 withLIMIT
. 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.
----+-----------+----------+----------------------------+------------------+----------
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 withORDER BY
andFETCH
:
ORDER BY id FETCH FIRST ROW ONLY;
- Your results should resemble something like this:
-----------+----------
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
:
- 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:
-----------
Sirciram
Mica
Richmond
Enanreh
(4 ROWS)
- Use the operator
UNION
to make just one combined result out of at least twoSELECT
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 isfullname
, for the results.
AS fullname, username FROM users;
- The results should look something like this:
--------------------+---------------------
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:
- The results should look something like this one below:
-----------
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