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

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL