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.

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)

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

--crete 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]$ 

--checking table which tablespace it created

postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables wher
e tablename='prim_tab';
 tablename | tableowner | tablespace | schemaname 
-----------+------------+------------+------------
 prim_tab  | u2         |            | benz2
(1 row)

Note:prim_tab table stored in default tablespace like pg_default tablespace

--moving table to another tablespace

postgres=# ALTER TABLE prim_tab SET TABLESPACE tbs;                    
ALTER TABLE

--checking tablespace after altering table

postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables wher
e tablename='prim_tab';
 tablename | tableowner | tablespace | schemaname 
-----------+------------+------------+------------
 prim_tab  | u2         | tbs        | benz2
(1 row)

14.To move a table to a different schema:

--Fetch the table which schema it having

postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables wher
e tablename='prim_tab';
 tablename | tableowner | tablespace | schemaname 
-----------+------------+------------+------------
 prim_tab  | u2         | tbs        | benz2
(1 row

--Listdown the Schema for where do you want to move that 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 moving table from benz2 schema to books schema

postgres=# ALTER TABLE benz2.prim_tab SET SCHEMA books;
ALTER TABLE

--Checking the table for which schema it having currently

postgres=# select tablename,tableowner,tablespace,schemaname from pg_tables where tablename='prim_tab';
 tablename | tableowner | tablespace | schemaname 
-----------+------------+------------+------------
 prim_tab  | u2         | tbs        | books
(1 row)