PostgreSQL DDL, DML and TCL Operations

Languages:

• DDL-Data Definition Language  
• DML-Data Manipulation Language 
• TCL-Transactional Control Language

PostgreSQL DDL:

DDL has declarative character and allows us to create, modify and remove database objects such as: tables, views, indexes, name spaces, table spaces, databases, stored procedures and functions, triggers and users.

CREATE TABLE Create a table

postgres=# create table emp(id int,name text,dob date);

CREATE TABLE AS Create a table from another table's definition and data

postgres=# create table sam_emp as select * from emp;

ALTER TABLE Add, modify, and delete columns in a table

Add Column:

postgres=# alter table emp add age int;

Change Datatype:

postgres=# alter table emp alter name type varchar(25);

Rename Column:

postgres=# alter table emp rename dob TO dataofbirth;

Drop Column:

postgres=# alter table emp drop COLUMN dataofbirth ;

RENAME a table

postgres=# alter table emp rename TO emp_details;

TRUNCATE TABLE

postgres=# truncate emp_details ;

DROP TABLE Delete a table

postgres=# drop table emp_details ;

PostgreSQL DML:

INSERT TABLE

postgres=# insert into emp values (1,'muthu','27-apr-1992');

UPDATE TABLE

postgres=# update emp set name ='tiger' where id=1;

DELETE TABLE

postgres=# delete from emp where id =3;

postgres=# delete from emp;

MERGE TABLE

Not Available

PostgreSQL TCL:

Begin: To start a transaction.

postgres=# begin

postgres=# insert into emp values (2,'aaa','12-may-1993');

Commit: To save the changes, alternatively you can use END TRANSACTION command.

postgres=# commit ;

Savepoint: define a new savepoint within the current transaction

postgres=# begin

postgres=# insert into emp values (3,'ccc','16-jun-1995');

postgres=# savepoint a;

postgres=# insert into emp values (4,'ddd','1-dec-1991');

postgres=# savepoint b;

postgres=# delete from emp ;

postgres=# select * from emp ;

 id | name | dob

----+------+-----

(0 rows)

Rollback: To rollback the changes.

postgres=# rollback a;

postgres=# select * from emp ;

 id | name  |    dob    

----+-------+------------

  1 | muthu  | 1992-04-27

  2 | aaa | 1993-05-2

  3 | ccc   | 1995-06-16