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
Trigger
Index

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

Trigger

A “trigger” is described as any situation that establishes a course of action. In PostgreSQL, the trigger is used to perform some specific action on particular database commands like INSERT, UPDATE, DELETE, or TRUNCATE. The trigger feature is beneficial as it performs the necessary function on specified events.

The trigger is connected with the defined table, view, or foreign table and will perform the defined task when specific actions are executed on that table. Depending on the condition user can create a trigger BEFORE, AFTER, or INSTEAD. If the trigger is listed FOR EACH ROW then it is called for each row that will be changed by the event. For example, if you UPDATE 250 rows in the table, then the UPDATE trigger event will be fired 250 times. The other type of trigger is a statement-level trigger. In this, the FOR EACH STATEMENT will invoke the trigger only one time for each statement, despite the number of the rows getting updated.
CREATE TRIGGER my_trigger AFTER INSERT ON emp_table FOR EACH ROW EXECUTE PROCEDURE salary();

The above trigger is defined on the table emp_table for updating the salary of all the employees.

Indexes

PostgreSQL index is one powerful instrument to improve database performance. Indexes support the database server to obtain explicit rows much quicker than it could do without indexes. Yet, indexes combine write and storage burdens to the database operation. Hence, the appropriate application of the index is very crucial.

An index is a distributed data structure e.g., B-Tree that improves the speed of the data retrieval at the cost of extra writes and storage to support it.
CREATE INDEX simple_firstname ON Employee(FirstName);

The SQL statement above is creating an index called “simple_firstname” on the “FirstName” column in the “Employee” table. When there is no index, the database will browse the entire table and then order the rows to execute the query. Yet, the index will give the database a previously ordered list of the table’s columns. The database can simply browse the index from the first row to the last row and get the rows in proper order.

Indexes are utilized to instantly find data without having to explore every row in a table every time a database table is used. Indexes can be formed by applying one or more columns of a database table, giving the support for both speedy arbitrary lookups and effective access to organized records. Simply put, they are specific lookup tables that the database can utilize to speed up data retrieval.