Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL