PostgreSQL Select

An SQL SELECT statement is used to retrieve data from a table 

The statement is divided into: 

1. Select list - the part that lists the columns to be returned, 
2. 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)