In the previous post, we learned about projection in which we restricted the postgreSQL to display only certain columns.
Now we learn about restricting the rows using “PostgreSQL where” clause.
when retrieving data from the database, you may need to do the following.
1. restrict the rows of data that are displayed.
2. specify the order in which the rows are displayed.
Table of Contents
PostgreSQL where clause:
You can restrict the rows that are returned from the query by using the PostgreSQL where clause. A where clause contains a condition that must be met and it directly follows the from clause. If the condition is true, the row meeting the condition is returned.
Syntax:
SELECT {column, column, …}
FROM {table}
WHERE {condition};
The SQL where clause can compare values in columns, literal, arithmetic expressions or functions. It consists of three elements
1. column name
2. comparison condition
3. column name, constant or list of values.
PostgreSQL where clause example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
testdb=> select * from emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+---------------------------+---------+---------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00+05:30 | 2850.00 | 20.00 | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00+05:30 | 2450.00 | 10.00 | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00+05:30 | 950.00 | 11.00 | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 (14 rows) |
Here, In the above table, if we want to restrict the emp list whose deptno is 20, we write the query as
|
1 2 3 4 5 6 7 8 9 10 11 12 |
testdb=> select * from emp testdb-> where deptno=20; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+---------+------+---------------------------+---------+--------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | 20 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 (5 rows) testdb=> |
We can also do projection by selecting column list.
e.g.,
|
1 2 3 4 5 6 7 8 9 10 11 12 |
testdb=> testdb=> select empno, ename, sal testdb-> from emp testdb-> where deptno=20; empno | ename | sal -------+-------+--------- 7369 | SMITH | 800.00 7566 | JONES | 2975.00 7788 | SCOTT | 3000.00 7876 | ADAMS | 1100.00 7902 | FORD | 3000.00 (5 rows) |
PostgreSQL where like:
Use the LIKE operator to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers.
The following searches can be used.
1. % denotes zero or many characters, denotes one character.
2. _ denotes one character.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
testdb=> select empno , ename from emp where ename like 'ADAM%'; empno | ename -------+----------- 7876 | ADAMS 7777 | ADAMSMITH (2 rows) testdb=> select empno , ename from emp where ename like 'ADAM_'; empno | ename -------+------- 7876 | ADAMS (1 row) |
Here, though we have two names starting with ‘adam’, we got only one result which is ‘adams’ and ‘adamsmith’ is ignored.
PostgreSQL where not null/null:
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
In order to find the rows where the value of a column is or is not NULL, you would use IS NULL or IS NOT NULL.
Example.,
|
1 2 3 4 5 6 7 |
testdb=> select * from emp where comm is null; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-----------+-------+------+----------------------------------+--------+------+-------- 7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20 (1 row) testdb=> |
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
