SUMMARY: This article discusses the use of clauses in PostgreSQL queries to filter and organize data in results. It introduces different clauses and provides examples of their usage.
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. BETWEEN operator
6. LIKE condition
7. FETCH clause
Clauses are helpful and often necessary components of a search query to help filter and organize data. Let’s take a look at some of the most common clauses and how you can make best use of them when querying data in PostgreSQL.
FROM clause
The FROM clause is used to specify the table or tables that data is retrieved from..
Example
This example shows that all the columns can be retrieved using the FROM clause. Using the asterisk symbol (*) with SELECT will list all the columns in their defined order:
postgres=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(14 rows)
To restrict the data to be retrieved from a table to specific columns, specify them after the SELECT keyword:
postgres=# select empno,ename from emp;
empno | ename
-------+--------
7369 | SMITH
7499 | ALLEN
7521 | WARD
7566 | JONES
7654 | MARTIN
7698 | BLAKE
7782 | CLARK
7788 | SCOTT
7839 | KING
7844 | TURNER
7876 | ADAMS
7900 | JAMES
7902 | FORD
7934 | MILLER
(14 rows)
WHERE clause
The WHERE clause is used to restrict the data to rows that match specific conditions.
Example
This example lists only the data of the employees who are working in the department number 10.
postgres=# select empno, ename, deptno from emp where deptno=10;
empno | ename | deptno
-------+--------+--------
7782 | CLARK | 10
7839 | KING | 10
7934 | MILLER | 10
(3 rows)
Character strings and dates can also be used in a WHERE clause.
Example
postgres=# select empno,ename,deptno from emp where ename='KING';
empno | ename | deptno
-------+-------+--------
7839 | KING | 10
(1 row)
GROUP BY clause
The rows in a table can be condensed into a smaller number groups that share the same value by using the GROUP BY clause.
Example
This example displays departments with the average salary for their employees:
select deptno, avg(sal) from emp group by deptno;
deptno | avg
--------+-----------------------
20 | 2175.0000000000000000
30 | 1566.6666666666666667
10 | 2916.6666666666666667
(3 rows)
NOTE: All columns in the list that are not in grouped functions must be included in the GROUP BY clause.
HAVING clause
Results of a GROUP BY clause can be restricted to those satisfying certain conditions using the HAVING clause. The WHERE clause cannot be used to restrict groups.
Example
Attempting to use WHERE and GROUP BY together will give an error:
postgres=# select deptno, AVG(sal) from emp where AVG(sal) > 300 group by deptno;
ERROR: aggregate functions are not allowed in WHERE
LINE 1: select deptno, AVG(sal) from emp where AVG(sal) > 300 group ...
^
postgres=# select deptno, AVG(sal) from emp group by deptno having AVG(sal) > 300;
deptno | avg
--------+-----------------------
10 | 2916.6666666666666667
20 | 2175.0000000000000000
30 | 1566.6666666666666667
(3 rows)
BETWEEN operator
The BETWEEN operator can be used to find out the range between two values.
Example
Here BETWEEN is used to look for salaries with a value greater than or equal to 500 and less than or equal to 1000:
postgres=# select ename,sal from emp where sal between 500 and 1000;
ename | sal
-------+--------
SMITH | 800.00
JAMES | 950.00
(2 rows)
Example
This example sorts and displays the results between two character values:
postgres=# select empno,ename from emp order by ename;
empno | ename
-------+--------
7876 | ADAMS
7499 | ALLEN
7698 | BLAKE
7782 | CLARK
7902 | FORD
7900 | JAMES
7566 | JONES
7839 | KING
7654 | MARTIN
7934 | MILLER
7788 | SCOTT
7369 | SMITH
7844 | TURNER
7521 | WARD
(14 rows)
postgres=# select ename,sal from emp where ename between 'ALLEN' and 'JONES';
ename | sal
-------+---------
ALLEN | 1600.00
JONES | 2975.00
BLAKE | 2850.00
CLARK | 2450.00
FORD | 3000.00
JAMES | 950.00
(6 rows)
LIKE condition
The LIKE condition is used to match a character pattern in wild-card searches.
• % denotes zero or many characters.
• _ denotes one character.
Example
This example lists the employees with names starting with letter S. The ‘%’ below denotes zero or many characters:
postgres=# select ename from emp where ename like 'S%';
ename
-------
SCOTT
SMITH
(2 rows)
Example
This example lists the employee names whose second letter is the letter L. The ‘_’ below denotes one character only:
postgres=# select ename from emp where ename like '_L%';
ename
-------
ALLEN
BLAKE
CLARK
(3 rows)
FETCH clause
The FETCH clause is used to limit the number of retrieved portions of rows generated by a query.
NOTE: You should use the FETCH clause with the ORDER BY clause to get a consistent result, as the order of rows stored in a table is unpredictable.
postgres=# select ename,sal from emp order by sal ;
ename | sal
--------+---------
SMITH | 800.00
JAMES | 950.00
ADAMS | 1100.00
MARTIN | 1250.00
WARD | 1250.00
MILLER | 1300.00
TURNER | 1500.00
ALLEN | 1600.00
CLARK | 2450.00
BLAKE | 2850.00
JONES | 2975.00
FORD | 3000.00
SCOTT | 3000.00
KING | 5000.00
(14 rows)
Example
This example sorts the results based on the salary column and displays only the first row/record from the query:
postgres=# select ename,sal from emp order by sal
FETCH FIRST ROW ONLY;
ename | sal
-------+--------
SMITH | 800.00
(1 row)
OR
postgres=# select ename,sal from emp order by sal '
FETCH FIRST 1 ROW ONLY;
ename | sal
-------+--------
SMITH | 800.00
(1 row)
Example
This example sorts the results based on the salary column and displays the first three rows only from the query:
postgres=# select ename,sal from emp order by sal
FETCH FIRST 3 ROW ONLY;
ename | sal
-------+---------
SMITH | 800.00
JAMES | 950.00
ADAMS | 1100.00
(3 rows)
You can either use the above syntax or the below syntax.
postgres=# select ename,sal from emp order by sal
FETCH NEXT 3 ROW ONLY;
ename | sal
-------+---------
SMITH | 800.00
JAMES | 950.00
ADAMS | 1100.00
(3 rows)
Example
This example sorts the results based on the salary column and displays the second five rows from the query by adding the OFFSET clause before FETCH:
postgres=# select ename,sal from emp order by sal
OFFSET 5 rows
FETCH FIRST 5 ROW ONLY;
ename | sal
--------+---------
MILLER | 1300.00
TURNER | 1500.00
ALLEN | 1600.00
CLARK | 2450.00
BLAKE | 2850.00
(5 rows)
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...