Home
/
Blog
/
Ultimate PostgreSQL Tutorial for Beginner Programmers

Ultimate PostgreSQL Tutorial for Beginner Programmers

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
How to apply all the constraints on one table
PostgreSQL – Group by
Locks

CONSTRAINTS

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.

How to add NOT NULL Constraint?

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.

How to add UNIQUE 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
);

PRIMARY KEY Constraint

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
);

FOREIGN KEY Constraint

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)
);

CHECK Constraint

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)
);

How to apply all the constraints on one table?

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 );

PostgreSQL – GROUP BY

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;

LOCKS

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

Union Clause

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

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

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)