PostgreSQL Alter Table -2

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)