Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

Script to kill ALL IDLE Connection In postgreSQL

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?