-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
PostgreSQL Select Table
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)
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)
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)