PostgreSQL Alter Table -1

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.

PRACTICAL FOR ALTER TABLE:

--just take demo table for practical purposes

postgres=# SELECT * FROM DEMO;
 id | name  
----+-------
  1 | JOHN
  1 | KEVIN
  1 | ABDUL
  1 | TRUMP
  1 | OBAMA
(5 rows

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


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)