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)
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

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL