PostgreSQL is a general-purpose and relational database management system. It is one of the most advanced open-source database systems. It was generated on POSTGRES 4.2 at Berkeley Computer Science Department, University of California. It operates on all principal operating systems such as Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
Let’s take a look at some of the most important topics of PostgreSQL:
Constraints are the controls imposed on data columns on the table. These are applied to check invalid data from being inserted into the database. This assures the dependability and security of the data in the database. Constraints can be column level or table level. Column level constraints are used only to one particular column while table level constraints are used to the entire table.
Following are the types of constraints along with an example.
The Not Null ensures that a column cannot have a NULL value. It is a column level constraint. The following PostgreSQL command creates a table called COMPANY and it has five columns, three of which, EMPID and ENAME and EAGE, will not accept NULL values.
Example:
CREATE TABLE COMPANY(
EMPID INT PRIMARY KEY NOT NULL,
ENAME TEXT NOT NULL,
EAGE INT NOT NULL,
EADDRESS CHAR(50),
ESALARY REAL
);
Now if you insert a null value in the columns of EAGE and ENAME then you will get an error.
[Err] ERROR: null value in column “EAGE” violates the not-null constraint.
The UNIQUE Constraint blocks two rows from having the same data in a singular column. In the COMPANY table, for example, if you want to limit two or more employees from having the same age.
Example
CREATE TABLE COMPANY(
EID INT PRIMARY KEY NOT NULL,
ENAME TEXT NOT NULL,
EAGE INT NOT NULL UNIQUE,
EADDRESS CHAR(60),
ESALARY REAL DEFAULT 70000.00
);
The PRIMARY KEY constraint classifies each row in a database table. There can be only one primary key in a table. If a table has a primary key set on any column, then you cannot have two rows having the same content of that field(s). A primary key is a value in a table, which distinguishes each row in the table. Primary keys must include individual values. A primary key column of any table must not contain NULL values.
Example
CREATE TABLE COMPANY(
EID INT PRIMARY KEY NOT NULL,
ENAME TEXT NOT NULL,
EAGE INT NOT NULL,
EADDRESS CHAR(50),
ESALARY REAL
);
A foreign key constraint defines that the data in a column (or more than one column) must resemble the data present in some row of another table.
Example
CREATE TABLE DEPT(
DNO INT PRIMARY KEY NOT NULL,
DNAME CHAR(50) NOT NULL,
EMPID INT references COMPANY(EID)
);
The CHECK Constraint allows a requirement to check the data being inserted into a table. If the condition assesses to invalid, the record breaks the constraint and is not inserted into the table.
Example
CREATE TABLE COMPANY(
EID INT PRIMARY KEY NOT NULL,
ENAME TEXT NOT NULL,
EAGE INT NOT NULL,
EADDRESS CHAR(50),
ESALARY REAL CHECK(ESALARY > 2000)
);
Now if you want to apply all constraints on one table then the following script is displaying the use of all constraints
CREATE TABLE products ( prodno integer Primary Key,
name text not null, price numeric CHECK (price > 0),
sale_price numeric CHECK (sale_price > 0),
CHECK (price > sale_price),
salary integer Unique );
The PostgreSQL GROUP BY clause is applied along with the SELECT statement to group collectively those rows in a table that have the same data. This is done to exclude repetition in the output and/or calculate totals that refer to these groups.
Syntax
SELECT col1,col2...
FROM table
WHERE [ conditions ]
GROUP BY col1, col2....colN
ORDER BY col1, col2....colN
Example: Now If you use group by clause to calculate the total sum of salaries of all the employees then the query will be like this:
testdb=# SELECT ENAME, SUM(SALARY) FROM COMPANY GROUP BY ENAME;
Output:
ename | sum
——-+——-
Scott | 30000
Paul | 10000
Allen | 85000
Tim | 55000
Jeff | 25000
John | 75000
Turner | 20000
(7 rows)
Locks or Exclusive Locks restrict users from altering a row or a complete table. Rows altered by UPDATE and DELETE are then completely locked automatically for the span of the transaction. This limits other users from modifying the data until the transaction is either committed or rolled back. The database implements locking implicitly. In particular circumstances, though, locking must be constrained manually. Standard locking can be achieved by applying the LOCK command. It enables the designation of a transaction’s lock nature and range.
Syntax
LOCK [ TABLE_NAME ]
name
IN
Lock_mode
Where lock_mode defines which locks this lock disputes with. If no lock mode is defined, then ACCESS EXCLUSIVE is applied. Probable conditions are ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.
Example
testdb=#BEGIN;
LOCK TABLE department IN ACCESS EXCLUSIVE MODE;
Output
LOCK TABLE
The PostgreSQL UNION clause is applied to join the outputs of two or more SELECT commands without yielding any duplicate rows.
To use UNION, each SELECT should have an equal number of columns picked, the equal number of column definitions, the identical data type, and in the same order.
Syntax
SELECT col1 [, col2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT col1 [, col2 ]
FROM table2 [, table2 ]
[WHERE condition]
Example
testdb=# SELECT EID, ENAME, EDEPT FROM COMPANY INNER JOIN DEPT
ON COMPANY.EID = DEPT.ID
UNION
SELECT EID, ENAME, EDEPT FROM COMPANY LEFT OUTER JOIN DEPT
ON COMPANY.EID = DEPT.ID;
The UNION ALL clause is applied to join the outputs of two SELECT commands including duplicate rows. The equivalent rules that pertain to UNION use to the UNION ALL clause as well.
Syntax
SELECT col1 [, col2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT col1 [, col2 ]
FROM table2 [, table2 ]
[WHERE condition]
Example
testdb=# SELECT EID, ENAME, EDEPT FROM COMPANY INNER JOIN DEPT
ON COMPANY.EID = DEPT.ID
UNION ALL
SELECT EID, ENAME, EDEPT FROM COMPANY LEFT OUTER JOIN DEPT
ON COMPANY.EID = DEPT.ID;
TRUNCATE TABLE command is utilized to delete entire data from the table. One can also use DROP TABLE command to remove the entire table but it would eliminate whole table formation from the database. The important thing about TRUNCATE command is that it restores disk space instantly, rather than needing a succeeding VACUUM method. This is most beneficial on big tables.
Syntax
TRUNCATE TABLE table name;
Example
testdb=# TRUNCATE TABLE DEPT;
testdb=# SELECT * FROM DEPT;
dno | dname | eage | daddress | esalary
----+------+-----+---------+--------
(0 rows)