Table Manipulation

Alter tables

1.Add a new column

Syntax

ALTER TABLE table_name ADD new_column datatype;

Example

ALTER TABLE emp ADD emp_email text;

Output

select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal
--------+----------+----------+-----------+---------
(0 rows)

2.Dropping a column

Syntax

ALTER table_name DROP column_name;

Example

ALTER TABLE emp DROP emp_email;

3.Dropping tables

Syntax

DROP TABLE <tablename>

Example

DROP TABLE emp;

4.Truncate a table

Syntax

TRUNCATE <tablename>

Example

TRUNCATE emp;

5.Creating an index on a table

Syntax

CREATE INDEX <identifier> ON <tablename>

Example

CREATE INDEX name ON emp (emp_name);

6.Dropping an index

Example

drop index name;

7.Creating Batch operations

Syntax

BEGIN BATCH
<insert-stmt>/ <update-stmt>/ <delete-stmt>
APPLY BATCH

Example

BEGIN BATCH
INSERT INTO emp (emp_id, emp_city, emp_name, emp_phone, emp_sal) values(  4,'Pune','rajeev',9848022331, 30000);
UPDATE emp SET emp_sal = 50000 WHERE emp_id =3;
DELETE emp_city FROM emp WHERE emp_id = 2;
APPLY BATCH;

Output

Before

emp_id | emp_city  | emp_email | emp_name | emp_phone  | emp_sal
--------+-----------+-----------+----------+------------+---------
      1 | Hyderabad |      null |      ram | 9848022338 |   50000
      2 |     Delhi |      null |    robin | 9848022339 |   50000

After

emp_id | emp_city  | emp_email | emp_name | emp_phone  | emp_sal
--------+-----------+-----------+----------+------------+---------
      1 | Hyderabad |      null |      ram | 9848022338 |   50000
      2 |      null |      null |    robin | 9848022339 |   50000
      4 |      Pune |      null |   rajeev | 9848022331 |   30000
      3 |      null |      null |     null |       null |   50000