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 configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

PostgreSQL Sequence