Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)





Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform