Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Alter Table -1

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)

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?