-
BASIC POSTGRESQL
6-
PostgreSQL Brief History 30 minLecture1.1
-
PostgreSQL Features 20 minLecture1.2
-
PostgreSQL Monitoring Tools Overview 30 minLecture1.3
-
PostgreSQL – Oracle Vs PostgreSQL 30 minLecture1.4
-
PostgreSQL Data Types 30 minLecture1.5
-
PostgreSQL Maximum Objects Size 30 minLecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Vmware Installation 30 minLecture2.1
-
Creating Linux Virtual Machine 01 hourLecture2.2
-
PostgreSQL Installation Types 30 minLecture2.3
-
PostgreSQL GUI Installation 30 minLecture2.4
-
PostgreSQL Text Mode Installation 30 minLecture2.5
-
PostgreSQL Unattended Mode Installation 30 minLecture2.6
-
Configure the network & Disk Partition 30 minLecture2.7
-
How to install PostgreSQL10 On Linux Server ? 20 minLecture2.8
-
PostgreSQL -11 Installation (rpm & source code) 30 minLecture2.9
-
-
POSTGRESQL DATABASE
6-
Connect Postgres Server 10 minLecture3.1
-
PostgreSQL startup / shutdown /restart the postgresql server 30 minLecture3.2
-
PostgreSQL .bash_profile Set Up 30 minLecture3.3
-
PostgreSQL Database Creation 30 minLecture3.4
-
PostgreSQL Connect Database 30 minLecture3.5
-
PostgreSQL Drop Database 30 minLecture3.6
-
-
POSTGRESQL TABLE
16-
PostgreSQL Languages 30 minLecture4.1
-
PostgreSQL Create Table 30 minLecture4.2
-
PostgreSQL Select Table 30 minLecture4.3
-
PostgreSQL Alter Table 30 minLecture4.4
-
PostgreSQL Drop Table 30 minLecture4.5
-
PostgreSQL Truncate Table 30 minLecture4.6
-
PostgreSQL Rename 30 minLecture4.7
-
PostgreSQL Comment 30 minLecture4.8
-
PostgreSQL Insert 30 minLecture4.9
-
PostgreSQL Update 30 minLecture4.10
-
PostgreSQL Table Delete 30 minLecture4.11
-
PostgreSQL Merge Table 30 minLecture4.12
-
PostgreSQL UNION 30 minLecture4.13
-
PostgreSQL UNION ALL 30 minLecture4.14
-
PostgreSQL INTERSECT 30 minLecture4.15
-
PostgreSQL DISTINCT 30 minLecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
PostgreSQL Grant 30 minLecture5.1
-
PostgreSQL Revoke 30 minLecture5.2
-
Postgres user creation and restrict DDL statement & other database access 30 minLecture5.3
-
-
TRANSACTIONS - MVCC
3-
PostgreSQL Commit 30 minLecture6.1
-
PostgreSQL Rollback 30 minLecture6.2
-
PostgreSQL Savepoint 30 minLecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
PostgreSQL User Creation 30 minLecture7.1
-
PostgreSQL User Creation Using Utility 30 minLecture7.2
-
PostgreSQL Drop user 30 minLecture7.3
-
PostgreSQL Drop User Using Utility 30 minLecture7.4
-
PostgreSQL Password Changing and Views 30 minLecture7.5
-
PostgreSQL Group/role 30 minLecture7.6
-
Alter PostgreSQL User/Role/group 30 minLecture7.7
-
PostgreSQL Schema 30 minLecture7.8
-
PostgreSQL user creation and restrict DDL & database access 30 minLecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
PostgreSQL Constraints 30 minLecture8.1
-
PostgreSQL Primary Key 30 minLecture8.2
-
PostgreSQL UNIQUE Constraint 30 minLecture8.3
-
PostgreSQL CHECK Constraint 30 minLecture8.4
-
PostgreSQL NOT NULL Constraint 30 minLecture8.5
-
PostgreSQL Foreign Key 30 minLecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
PostgreSQL DOMAIN Data Type 30 minLecture9.1
-
PostgreSQL Alter Domain 30 minLecture9.2
-
PostgreSQL Drop DOMAIN 30 minLecture9.3
-
PostgreSQL Json Data Type 30 minLecture9.4
-
PostgreSQL Sequence 30 minLecture9.5
-
-
POSTGRESQL VIEWS
1-
How to Create PostgreSQL View 30 minLecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
PostgreSQL Tablespace 30 minLecture12.1
-
PostgreSQL UnderStanding Of Objects 30 minLecture12.2
-
PostgreSQL Architecture 30 minLecture12.3
-
PostgreSQL Query Flow 30 minLecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
PostgreSQL Backup & Recovery Overview 30 minLecture13.1
-
PostgreSQL Database Backup Tools – Pg_dump 30 minLecture13.2
-
PostgreSQL Database Backup Tools – Pg_dumpall 30 minLecture13.3
-
PostgreSQL Database Backup Tools – Pg_basebackup 30 minLecture13.4
-
PostgreSQL COPY data From Text File 30 minLecture13.5
-
PostgreSQL COPY data to Text File 30 minLecture13.6
-
PostgreSQL Clone Table 20 minLecture13.7
-
Postgres Database Schema Migration 30 minLecture13.8
-
PostgreSQL Database clone/migration 30 minLecture13.9
-
PostgreSQL Compress backup format 30 minLecture13.10
-
PostgreSQL Archivelog 30 minLecture13.11
-
PostgreSQL Point In Time Recovery 30 minLecture13.12
-
Taking Backup On Postgres Slave (Standby) Server 30 minLecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
PostgreSQL Index 30 minLecture14.1
-
PostgreSQL Reindex 30 minLecture14.2
-
PostgreSQL PerformanceTuning 30 minLecture14.3
-
Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 minLecture14.4
-
Postgres Autovacuum Configuration/Setup 30 minLecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
POSTGRESQL STREAMING REPLICATION 30 minLecture15.1
-
SSL Streaming replication with PostgreSQL 10 30 minLecture15.2
-
How to make Linux partition and configure the network on Redhat Linux7.3 30 minLecture15.3
-
How To Fix Firewall Issues while connecting the other server? 30 minLecture15.4
-
How to install the PostgreSQL10 On Linux ? 30 minLecture15.5
-
How to Configure the cascade replication On PostgreSQL 10.3 ? 30 minLecture15.6
-
How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 minLecture15.7
-
PostgreSQL Switchover 30 minLecture15.8
-
Postgres Failover 30 minLecture15.9
-
Postgres Upgrade 30 minLecture15.10
-
PostgreSQL Upgrade 9.5 to 11.3 . 30 minLecture15.11
-
PostgreSQL Alter Table
alter command is used for alteration of table structures. There are various uses of alter command, such as,
- to add a column to existing table
- to rename any existing column
- to change datatype of any column or to modify its size.
- alter is also used to drop a column.
- add and drop various constraints on an existing table.
- Rename table on an existing table.
Examples:
Let us take demo table for altering
postgres=# SELECT * FROM DEMO;
id | name
----+-------
1 | JOHN
1 | KEVIN
1 | ABDUL
1 | TRUMP
1 | OBAMA
(5 rows1. ALTER TABLE to add a AGE column in an existing(DEMO) table is as follows:
postgres=# ALTER TABLE demo ADD age int;
ALTER TABLE
--After
postgres=# SELECT * FROM DEMO;
id | name | age
----+-------+-----
1 | JOHN |
1 | KEVIN |
1 | ABDUL |
1 | TRUMP |
1 | OBAMA |
(5 rows)
--UPDATE some information
postgres=# UPDATE DEMO SET AGE=18 WHERE NAME='JOHN';
UPDATE 1
postgres=# SELECT * FROM DEMO;
id | name | age
----+-------+-----
1 | KEVIN |
1 | ABDUL |
1 | TRUMP |
1 | OBAMA |
1 | JOHN | 18
(5 rows)2. ALTER TABLE to DROP COLUMN in an existing table is as follows:
postgres=# ALTER TABLE demo DROP COLUMN id;
ALTER TABLE
--After
postgres=# SELECT * FROM DEMO;
name | age
-------+-----
KEVIN |
ABDUL |
TRUMP |
OBAMA |
JOHN | 18
(5 rows)3. To change the types of two existing columns in one operation:
--before
Table "benz2.demo"
Column | Type | Modifiers
--------+-------------------+-----------
name | character varying |
age | integer |
postgres=# postgres=# ALTER TABLE demo ALTER COLUMN age TYPE varchar(80),ALTER COLUMN name TYPE varchar(100);
--After
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
age | character varying(80) |4. To rename an existing column: from age to ages
--before
Table "benz2.demo"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
age | character varying(80) |
postgres=# ALTER TABLE demo RENAME COLUMN age TO ages;
ALTER TABLE
--After
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
ages | character varying(80) |5. To rename an existing table:
--before
postgres=# select * FROM DEMO;
name | age
-------+-----
KEVIN |
ABDUL |
TRUMP |
OBAMA |
JOHN | 18
(5 rows)
postgres=# ALTER TABLE demo RENAME TO practis;
--After
postgres=# select * FROM practis;
name | ages
-------+------
KEVIN |
ABDUL |
TRUMP |
OBAMA |
JOHN | 18
(5 rows)6. To add a not-null constraint to a column:
--Before
postgres=# \d practis
Table "benz2.practis"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
ages | character varying(80) |
postgres=# ALTER TABLE practis ALTER COLUMN name SET NOT NULL;
ALTER TABLE
--After
postgres=# \d practis
Table "benz2.practis"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) | not null
ages | character varying(80) |7. To remove a not-null constraint from a column:
postgres=# ALTER TABLE practis ALTER COLUMN name DROP NOT NULL;
ALTER TABLE
--After remove not-null
postgres=# \d practis
Table "benz2.practis"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
ages | character varying(80) |8.To add a CHECK constraint to a table:
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
postgres=# alter table demo add constraint c1 check(id<100);
ALTER TABLE
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
Check constraints:
"c1" CHECK (id < 100)9. To remove a check constraint from a table and all its children:
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
Check constraints:
"c1" CHECK (id < 100)
postgres=# ALTER TABLE demo DROP CONSTRAINT c1;
ALTER TABLE
--After alter table
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |10.To add a foreign key constraint to a table:
–Creating parent table
postgres=# CREATE TABLE product (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE–Creating Child Table
postgres=# CREATE TABLE orders (
order_id integer PRIMARY KEY,
P_product_no integer,
quantity integer
);
CREATE TABLE–Checking the two table before creating foreign key
postgres=# \d orders
Table "benz2.orders"
Column | Type | Modifiers
--------------+---------+-----------
order_id | integer | not null
p_product_no | integer |
quantity | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
postgres=# \d product
Table "benz2.product"
Column | Type | Modifiers
------------+---------+-----------
product_no | integer | not null
name | text |
price | numeric |
Indexes:
"product_pkey" PRIMARY KEY, btree (product_no)
postgres=# ALTER TABLE orders ADD CONSTRAINT orderfk FOREIGN KEY (P_product_no) REFERENCES product(product_no);
ALTER TABLE–Checking the two table After altering foregin key
postgres=# \d orders
Table "benz2.orders"
Column | Type | Modifiers
--------------+---------+-----------
order_id | integer | not null
p_product_no | integer |
quantity | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
"orderfk" FOREIGN KEY (p_product_no) REFERENCES product(product_no)
postgres=# \d product
Table "benz2.product"
Column | Type | Modifiers
------------+---------+-----------
product_no | integer | not null
name | text |
price | numeric |
Indexes:
"product_pkey" PRIMARY KEY, btree (product_no)
Referenced by:
TABLE "orders" CONSTRAINT "orderfk" FOREIGN KEY (p_product_no) REFERENCES product(product_no)11.To add a (multicolumn) unique constraint to a table:
--Before adding unique key
postgres=# \d products
Table "benz2.products"
Column | Type | Modifiers
------------+---------+-----------
product_no | integer |
name | text |
price | numeric |
--Adding unique key to products table
postgres=# ALTER TABLE products ADD CONSTRAINT prod_no_price UNIQUE(product_no,p
rice);
ALTER TABLE
--After adding Unique key to products table
postgres=# \d products
Table "benz2.products"
Column | Type | Modifiers
------------+---------+-----------
product_no | integer |
name | text |
price | numeric |
Indexes:
"prod_no_price" UNIQUE CONSTRAINT, btree (product_no, price)12. To add an automatically named primary key constraint to a table,noting that a table can only ever have one primary key:
postgres=# \d prim_tab
Table "benz2.prim_tab"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
postgres=# ALTER TABLE prim_tab ADD PRIMARY KEY (id);
ALTER TABLE
--after altering table
postgres=# \d prim_tab
Table "benz2.prim_tab"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes:
"prim_tab_pkey" PRIMARY KEY, btree (id)13. To move a table to a different tablespace:
— su as postgres user
[root@r1 ~]# su postgres
[postgres@r1 root]$ cd— making tablespace directory
[postgres@r1 ~]$ mkdir tbslocation
[postgres@r1 ~]$ cd tbslocation/
[postgres@r1 tbslocation]$ pwd
/home/postgres/tbslocation— creating postgres tablespace
postgres=# create tablespace tbs location '/home/postgres/tbslocation';
CREATE TABLESPACE— just list down the “tbslocation” directory
[postgres@r1 tbslocation]$ ll
total 4
drwx------ 2 postgres postgres 4096 Feb 4 11:29 PG_9.3_201306121
[postgres@r1 tbslocation]$— check the table which tablespace is taken
postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables where tablename='prim_tab';
tablename | tableowner | tablespace | schemaname
-----------+------------+------------+------------
prim_tab | u2 | | benz2
(1 row)Note:prim_tab table stored in default tablespace
–moving prim_tab table to tbs tablespace
postgres=# ALTER TABLE prim_tab SET TABLESPACE tbs;
ALTER TABLE–after altering check the prim_tab table whether moved or not to tbs tablespace
postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables where tablename='prim_tab';
tablename | tableowner | tablespace | schemaname
-----------+------------+------------+------------
prim_tab | u2 | tbs | benz2
(1 row)14. To move a table to a different schema:
prim_tab table is in benz2 schema now we are moving the table to books schema as logically
postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables where tablename='prim_tab';
tablename | tableowner | tablespace | schemaname
-----------+------------+------------+------------
prim_tab | u2 | tbs | benz2
(1 row— List down the Schema to where do you want to move the table
postgres=# \dn+
List of schemas
Name | Owner | Access privileges | Description
---------+----------+----------------------+------------------------
benz2 | postgres | postgres=UC/postgres+|
| | u3=UC/postgres +|
| | u5=UC/postgres +|
| | u6=U/postgres +|
| | u7=UC/postgres |
books | u2 | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =U/postgres |
sara | u3 | u3=UC/u3 +|
| | u5=C/u3 +|
| | u6=C/u3 |
schema1 | u2 | |
schema2 | u2 | |
schema5 | postgres | |
schema6 | u2 | |
(8 rows)— Now move the table from benz2 schema to books
postgres=# ALTER TABLE benz2.prim_tab SET SCHEMA books;
ALTER TABLE— Check the table whether table is moved or not
postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables where tablename='prim_tab';
tablename | tableowner | tablespace | schemaname
-----------+------------+------------+------------
prim_tab | u2 | tbs | books
(1 row)
Login with Google