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

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