PostgreSQL SELECT Statement: Introduction, Explanation & Examples

In this tutorial, we will learn about the SELECT statement in PostgreSQL to fetch the data from the table. The SELECT statement is straightforward and doesn’t have complex terminologies. So without further ado, let’s get started!

Also Read: How to Create a Database in PostgreSQL

Introduction to PostgreSQL SELECT Statement

Fetching the data from the database tables and displaying it on the user interface is the most common task for any application. In this case, we must use the SELECT statement to fetch the data.

The simple SELECT statement fetches all the data from the table. However, you can use the SELECT statement with different clauses to filter the records and fetch only the required data based on conditions.

There are a bunch of clauses that can be used with the SELECT statement such as- WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, FETCH, joins, UNION, INTERSECT, etc.

Each of these clauses can have a separate tutorial, therefore, this tutorial will focus only on the SELECT statement.

All keywords in SQL are case-insensitive. Therefore, you can use uppercase, lowercase or any preferred case to specify a keyword. However, the standard practice is to use the uppercase format for the keywords in SQL.

Example of PostgreSQL SELECT Statement

There are many examples of using SELECT statements based on the requirements, let us go through them one by one.

1. Fetch All Data Using Asterisk (*)

If you want to fetch the record detail for all available columns, use the asterisk (*) symbol as shown below:

SELECT * FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, you just need to mention the asterisk symbol and that’s it. It will fetch all column values from the table.

Moreover, there is a shortcut in PostgreSQL if you want to fetch all the data without any further clauses after the FROM clause. Here it is:

TABLE table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As said before, you can not use any clause with this statement. Therefore, use it only when you want to check the table data on the console screen.

Note that, using the * in the SELECT query is supposed as a bad practice. Let’s say you have a large number of columns present in the table and the number of records is huge. In that case, using * will fetch all the records along with the unnecessary columns, increasing the load over the database server.

This causes a massive drop in database performance. Therefore, it also affects the application performance.

It is always recommended to specify the column names while fetching the data from the table. However, if the table size is relatively small and the number of columns is smaller, you can use the asterisk (*) symbol to fetch all data.

2. Fetch Only Required Column Values

To fetch the records from the table for only specific columns, use the following syntax:

SELECT col1, col2, col3 ... 
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, you have to mention the column names from your table separated by a comma. You can even specify a single-column name also.

You can use any number of valid clauses with this syntax.

3. Fetch using Alias for Column Names

You can use the aliases for the columns to display on a console screen. Look at the syntax below to use the aliases:

SELECT col1 as Name, col2 as Age
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the above syntax, we are using the AS clause to give the column alias name. However, note that even if we specify the alias ‘Name’, the console will display it as a ‘name’. The first capital letter will be turned into a small letter.

If you want to display the alias in case-sensitive format, mention the alias name in the double quotes as shown below:

SELECT col1 as "Name", col2 as "Age"
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now the column names will be displayed exactly as mentioned.

4. Using SELECT Without Table

The FROM clause is optional, therefore, it is not mandatory to use the table name with the SELECT statement. You can display any value or expression using the SELECT statement.

Example 1:

SELECT NOW();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the NOW is a built-in function that returns the current system date and time. Using the SELECT statement, we can display the current date-time.

Example 2:

SELECT 1+2*3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we are using a mathematical expression with the SELECT statement. PostgreSQL will solve it for us and display the result on the screen.

5. Using SELECT Statement With Expression

You can use the expressions with the column names as shown below:

SELECT firstName || ' ' || lastName FROM employees;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the query will concatenate the firstName and lastName and returns the result as a single-column value.

You can perform other operations also. For example:

SELECT name, salary+1000 FROM employees;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the query will add the amount of 1000 to each salary record and returns the new result.

Conclusion

The SELECT statement seems too easy and some may think there is nothing to learn more about it. However, there are so many things you can learn about the SELECT statement with the help of this tutorial. I hope you learned some new things. If you did, don’t forget to share it with your friends!

Reference

PostgreSQL official documentation on SELECT Statement