1) What is PostgreSQL?
PostgreSQL is a most advance open source database system. PostgreSQL is an object Oriented Relational Database Management System (ORDBMS). PostgreSQL source code is available free of charge and it is not controlled by any corporation.
2) What are the features provided by PostgreSQL?
Following advance features are provided by PostgreSQL:
Indexes
Triggers
Multiversion concurrency control (MVCC).
Complex SQL queries
SQL Sub-selects
Foreign keys
Views
Tablespaces
Point-in-time recovery
3) Name data types which are used in PostgreSQL.
PostgreSQL supports the following data types:
Booloean
Character (char, varchar, text)
Numeric (Integer, Floating-point)
Temporal (date, time, timestamp, interval)
Array (array string, number)
JSON
hstore (key-value pair)
4) What are the different database tools for PostgreSQL?
Following are the database tools are used for PostgreSQL:
5) How to start the PostgreSQL database server?
/usr/local/etc/rc.d/010.pgsql.sh start
/usr/local/etc/rc.d/postgresql start
6) How to check whether PostgreSQL server is running?
/usr/local/etc/rc.d/010.pgsql.sh status
/usr/local/etc/rc.d/postgresql status
7) How to stop the PostgreSQL database server?
/usr/local/etc/rc.d/010.pgsql.sh stop
/usr/local/etc/rc.d/postgresql stop
8) How to create PostgreSQL user?
Syntax:
CREATE USER user_name with condation;
Example:
CREATE USER Rohan with password="password";
9) How to create database?
There are two ways to create database in PostgreSQL:
Using CREATE DATABASE command
Using createdb a command line executable.
Syntax:
CREATE DATABASE database_name;
Example:
In PosstgreSQL schema the above command is written as:
postgres=# CREATE DATABASE database_name;
10) How to drop PostgreSQL database?
DROP DATABASE command deletes all information (table, view, procedure etc) stored in the database.
Example:
DROP DATABASE database_name;
11) How to create a new table in PostgreSQL?
We can create a new table by specifying table name, column name with their data type.
Syntax:
CREATE TABLE tablename(column1 datatype, column2 datatype);
Example:
CREATE TABLE employee(
id int,
name varchar(20),
salary int,
);
12) How to drop table from database?
DROP TABLE command is used to remove (delete) table and all its data, rules, indexes, constraints etc from database.
Syntax:
13) What is TRUNCATE statement?
TRUNCATE TABLE statement is used to remove all data quickly and efficiently from the table.
Syntax:
TRUNCATE TABLE table_name;
14) What is DELETE statement in PostgreSQL?
DELETE statement is used to delete rows from the table.
Syntax:
DELETE FROM table_name WHERE condation;
15) Compare TRUNCATE, DROP and DELETE statement for table in PostgreSQL.
Suppose that we have a database with name
employeedb contain table
employee_table. Let consider employee_table has following structure.
| emp_id |
emp_name |
emp_salary |
department |
| 101 |
Ajay |
35000 |
Sales |
| 102 |
Prakas |
40000 |
Finance |
| 103 |
Sunil |
45000 |
IT |
Let see what happen when we execute the above statements:
DELETE FROM employee_table WHERE emp_id=102;
It gives the following result.
| emp_id |
emp_name |
emp_salary |
department |
| 101 |
Ajay |
35000 |
Sales |
| 103 |
Sunil |
45000 |
IT |
TRUNCATE TABLE employee_table;
It gives the following result.
| emp_id |
emp_name |
emp_salary |
department |
DROP TABLE employee_table;
It remove complete table from database
employeedb.
