PostgreSQL SELECT
An SQL SELECT statement is used to retrieve data from a table
The statement is divided into:
- Select list - the part that lists the columns to be returned,
- Table list -the part that lists the tables from which to retrieve the data.
Below command will show all the column's of demo table:
postgres=# select * from demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
Below command will show selected column only:
postgres=# select id,name from demo;
id | name
----+---------
1 | anbu
2 | nijam
3 | umar
4 | junaith
3 | umar
4 | junaith
9 | mohad
7 | kadahar
(8 rows)
You can write expressions, not just simple column references, in the select list. For example, you can do:
postgres=# select (id+pincode) as adding,name from demo;
adding | name
--------+---------
601206 | anbu
601208 | nijam
601208 | umar
601210 | junaith
601208 | umar
601210 | junaith
601211 | mohad
601208 | kadahar
(8 rows)
Note: how the AS clause is used to relabel the output column "addind"
A WHERE clause that specifies which rows are wanted:
The WHERE clause contains a Boolean (truth value) expression, and only rows for which the Boolean expression is true are returned. The usual Boolean operators (AND, OR, and NOT) are allowed in the qualification.
postgres=# select * from demo where id=3 and pincode=601205;
id | name | place | pincode
----+------+---------+---------
3 | umar | pulicat | 601205
3 | umar | pulicat | 601205
(2 rows)postgres=# select * from demo where id=3 and pincode=601205;
You can request that column of id sorted by ascending order:
postgres=# select * from demo order by id;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
4 | junaith | pulicat | 601206
7 | kadahar | pulicat | 601201
9 | mohad | pulicat | 601202
(8 rows)
You can request that duplicate rows be removed from the result of a query:
postgres=# select distinct id from demo;
id
----
4
1
3
9
2
7
(6 rows)
Here again, the result row ordering might vary. You can ensure consistent results by using DISTINCT and ORDER BY together:
postgres=# select distinct id from demo order by id;
id
----
1
2
3
4
7
9
(6 rows)
Postgresql Joins:
To join the table sale with the table demo
table 1:
postgres=# select * from sale;
item_id | volume
---------+--------
10 | 2200
20 | 1000
1 | 2000
(3 rows)
table 2:
postgres=# select * from demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
--Now join the two table result will be show following as
postgres=# select d.id,d.name,s.volume from sale s,demo d where d.id =s.item_id;
id | name | volume
----+------+--------
1 | anbu | 2000
(1 row)
To sum the column id of all demo and group the results by pincode:
postgres=# SELECT sum(id) AS total,pincode FROM demo GROUP BY pincode;
total | pincode
-------+---------
7 | 601205
9 | 601202
10 | 601206
7 | 601201
(4 rows)
Comments
Post a Comment