PostgreSQL Languages or Operations

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Transactional Control Language (TCL)
4. Data Control Language (DCL)
5. Data Retrieval Language (DRL)

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 a New 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:

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