Introduction to PostgreSQL Select
One of the most important purposes of any database is to store the data so that it can be retrieved and fetched whenever we want according to our requirements. The retrieved records are mostly used in reporting and analysis for the user or sometimes retrieving existing results to modify the same. The SELECT clause is used to fetch the data in the PostgreSQL database. We can retrieve the results from zero, one or more tables using the select clause. In this article, we will learn how we can use the select clause to build the query statements, its syntax, and examples to better understand query building in PostgreSQL.
Syntax of PostgreSQL Select
Syntax of postgresql select are given below:
Syntax:
SELECT [ ALL | DISTINCT | DISTINCT ON (column_or_expression) ]
columns_or_expressions
FROM tables
[WHERE conditional_restrictions]
[GROUP BY column_or_expression]
[HAVING conditional_restrictions]
[ORDER BY column_or_expression [ ASC | DESC | USING operator ] [ NULLS FIRST | NULLS LAST ]]
[LIMIT [ row_count | ALL]
[OFFSET value_of_offset [ ROW | ROWS ]]
[FETCH { FIRST | NEXT } [ rows_to_be_fetched ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF table [ NOWAIT ]];
The syntax of the select clause is very complex and involves many possible combinations to provide flexibility to the user. We will learn the syntax by learning all the above-used clauses with the select clause.
- ALL: To retrieve all the records that the query will fetch after applying all the conditions, restrictions and expressions.
- DISTINCT: To retrieve only unique values of the column and expression from the retrieved results and further filter out the unique entries with respect to column or expression mentioned in the parameter of distinct.
- columns_or_expressions: This is the list of the column names or expressions that you wish to retrieve using the select query.
- FROM: This keyword helps in specifying the name of the table from which you wish to retrieve the records. Further, we can use joins of type left join, right join, natural join, etc to combine the results of two or more tables while retrieving the records.
- WHERE: This clause helps in specifying the conditions, restrictions, and expressions to filter out the results while retrieving the records in the select query.
- GROUP BY: The result set can be grouped on the basis of some column or expression using group by statement. This is most frequently used while retrieving the aggregate functions manipulated columns like sum or product of certain columns.
- HAVING: The result can be filtered out further by applying the conditions and restrictions on the retrieved columns and expressions which also include the aggregated values if used while retrieving the result.
- ORDER BY: The resultset can be arranged in the orderly format on the basis of certain columns and expressions when specified after the ORDER BY keyword. We can arrange the data in ascending or descending order by using ASC or DESC keyword.
- LIMIT: The records retrieved can be limited to a certain number of rows by using the limit keyword. For example, if the result of the query would have resulted in 55 records and after applying limit 10 statements in the select query, only the first 10 records will be retrieved.
- OFFSET: This is the number of the row from which you want to begin retrieving the records. For example, if the offset is mentioned as 5 the beginning from the 5th record the rows of the result will be retrieved.
- FETCH: This functions similarly to the limit keyword that restricts the number of the records to be retrieved to a particular number.
- FOR: The records can be restricted for access and are write-locked if FOR UPDATE is specifies and is allowed for reading operation but not update and insert operations by other transactions when FOR SHARE is specified.
Except FROM, all other clauses/keywords used in the above select clause syntax are optional in nature.
Examples of PostgreSQL Select
Following are the examples of postgresql select:
Let us create one example and insert few records in the table to learn how we can use a select clause for retrieving the records. Open your PostgreSQL command-line prompt and enter the following command to create a table named educba –
CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);
Let us insert some values in educba table using the following statement –
INSERT INTO educba VALUES (1,'java',20,'satara'),(2,'javascript',30,'mumbai'),(3,'java',20,'satara'),(4,'psql',30,'mumbai'),(5,'mysql',20,'satara'),(6,'maven',30,'mumbai'),(7,'hibernate',20,'satara'),(8,'spring',30,'mumbai'),(9,'angular',20,'satara'),(10,'html',30,'mumbai'),(11,'css',20,'satara'),(12,'reddis',30,'mumbai');
Let us create a simple select query statement to retrieve all the records from the educba table. Our query statement will be as follows:
SELECT * FROM educba;
Here, * represents all the columns to be retrieved and firing above query results in the following output –
Now we will apply the conditions using where clause and retrieve only those records that have a workforce of 20 persons. For this, we will have to mention the condition as workforce = 20 in the where clause and our query statement will be as follows –
SELECT * FROM educba WHERE workforce=20;
Now, suppose we only want to retrieve the list of name of technologies with the workforce as 20 then the query statement will be as follows –
SELECT technologies FROM educba WHERE workforce=20;
Let us see how we can group the result based on workforce count and retrieve the comma-separated string of the technologies. For this, the query statement will be as follows:
SELECT string_agg(technologies,','), workforce FROM educba GROUP BY workforce;
Suppose that instead of retrieving the column head as string_agg, we can give the alias for the same using “as” keyword as follows:
SELECT string_agg(technologies,',') as "List Of Technologies", workforce FROM educba GROUP BY workforce;
Let us order the results alphabetically based on the name of technology and limit the records to only 7 by using the limit clause. Our query statement will be as follows –
SELECT * FROM educba ORDER BY technologies ASC LIMIT 7;
Recommended Articles
This is a guide to PostgreSQL Select. Here we also discuss the introduction and syntax of postgresql select along with different examples and its code implementation. You may also have a look at the following articles to learn more –
