PostgreSQL allows us to apply various operations on the data to manipulate data within the PostgreSQL database. The results of these operations need to be returned so that we can see what the query has returned and how it is performing. The SELECT statement serves this specific purpose. The SELECT statement is used to return or fetch a record, multiple records as per our need, or all the records from the database.
The content of this article will revolve around the SELECT statement and how we can select a single, multiple, or all the records from a particular database.
PostgreSQL SELECT – How to Select Table Records?
The SELECT statement is used to get the specific data records from the database. Generally, the SELECT statement is used with the FROM clause. It can be used with various other statements and operations such as set operations, joins, sorting operators, etc. to achieve different functionalities.
The basic syntax for a SELECT statement is
SELECT records_list FROM t_name;
Syntax Description
- The SELECT statement is used to get the records as output.
- The “records_list” specifies a single, multiple, or all the records you want to fetch.
- The FROM statement takes the record from the table specified just after it.
- The “;” separates two SQL queries.
Let’s see how we can fetch all the records from a table.
Using SELECT Statement to Get All the Records From a Table
We can get all the table’s data using the SELECT keyword. This is achieved by setting out the * wildcard after the SELECT statement. The * represents all data. The basic syntax can be written as:
SELECT * FROM t_name;
Specify the name of the table in place of “t_name” from which you want to fetch all the data.
Example: Get All the Records From a Table Using SELECT Statement
Let’s consider a table named “messages” with 4 columns and have some values inserted in it. Now if we want to get all the records/data from the table “messages”, we will write the following query:
SELECT * FROM messages;
The above query will return all the records from the “messages” table. The output of the above query will be:
We can see that all the records from the table have been returned.
Using SELECT Statement to Get Data From a Single Column
We can get data from a single column using the SELECT statement. This can be done by specifying the name of the column after the SELECT statement. The basic syntax can be written as:
SELECT col_name_to_select FROM t_name;
We need to write the name of the column that is to be fetched from the specified table.
Example: Get Data From a Single Column Using SELECT Statement
Let’s consider the same table as above named “messages”. If we want to get the data records of the “msg_content” column, which contains the content of all the messages, we will write the following query:
SELECT msg_content FROM messages;
We just need to specify the name of the column from which we want to fetch the records after the SELECT statement. The above query returns:
We can see that the query returned the records from the specified column. We can also do the same to get multiple records.
Using SELECT Statement to Get Data From Multiple Columns
We can get data from multiple columns using the SELECT statement. This can be done by specifying the names of all the columns we want to get data from, after the SELECT statement. The basic syntax can be written as:
SELECT col_name_1, col_name_2, …, col_name_N FROM t_name;
We simply need to write the names of the columns that are to be fetched from the specified table.
Example: Get Data From Multiple Columns Using SELECT Statement
Let’s fetch the records “msg_content” and “msg_status” from the table “messages”.
SELECT msg_content, msg_status FROM messages;
The output of this query will return the records of these two columns, which is:
The query has fetched the records from the two specified columns.
Using SELECT Statement to Get Filtered Data From a Table
Getting the filtered data from the database is the most significant use case of the SELECT statement. We can get filtered data from a Postgres table by making use of the SELECT statement followed by the WHERE clause. The basic syntax can be written as:
SELECT col_names_list
FROM t_name
WHERE cond;
We simply need to specify the names of the columns from which data will be fetched and the condition after the WHERE clause to get the records that satisfy the specified condition.
Example: Get Filtered Data From a Table Using SELECT Statement
Let’s get the filtered data from the table named “messages”. The query for this can be written as:
SELECT * FROM messages
WHERE msg_status = 'Read';
This query will return all those outputs where the msg_status is “Read”.
We can see that only those columns are returned where the msg_status was “Read”. This is how we can get the filtered data from a Postgres table by utilizing a SELECT statement.
Using SELECT Statement to Get Data From Multiple Postgres Tables
We can fetch data from multiple Postgres tables using the SELECT statement. This can be done by specifying the names of all the tables we want to get data from, after the FROM statement. The basic syntax can be written as:
SELECT col_names_list FROM list_of_tab_name;
We simply need to specify the names of the columns and tables that are to be fetched in the output.
Example: Get Data From Multiple Tables Using SELECT Statement
Let’s get all the data from the two tables named “messages” and “test_marks”. The query for this can be written as:
SELECT * FROM test_marks, messages;
The query will return the records of both tables, but each record of the first table is mapped to all the records of the second table which means that this will return all the possible combinations of both the tables. But the basic idea is that we can select the records from multiple tables like this:
We can see that the query has returned the records from both tables.
Conclusion
The SELECT statement returns/gets specified data from the database table. The SELECT statement is used with the FROM statement to get the required output from the specified table. This statement is also used with many operators to offer several functionalities but it is usually used with the WHERE clause to return filtered data. In this post, we have understood the SELECT statement and its different use cases.
