The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in an existing table.

You would also use ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows −

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows −

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows −

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows −

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows −

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

If you are using MySQL, the code is as follows −

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows −

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

If you are using MySQL, the code is as follows −

ALTER TABLE table_name
DROP PRIMARY KEY;

Example

Consider our table has the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

The following is the example to ADD a new column in an existing table −

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

Now, COMPANY table is changed and the following would be the output from SELECT statement −

 id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

The following is the example to DROP gender column from existing table −

testdb=# ALTER TABLE COMPANY DROP GENDER;

Now, COMPANY table is changed and the following would be the output from SELECT statement −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

The PostgreSQL TRUNCATE TABLE command is used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure from the database and you would need to re-create this table once again if you wish to store some data.

It has the same effect as DELETE on each table, but since it does not actually scan the tables, it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

Syntax

The basic syntax of TRUNCATE TABLE is as follows −

TRUNCATE TABLE  table_name;

Example

Consider the COMPANY table has the following records −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

The following is the example to truncate −

testdb=# TRUNCATE TABLE COMPANY;

Now, COMPANY table is truncated and the following would be the output of SELECT statement −

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record, updating a record, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Practically, you will club many PostgreSQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID −

• Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

• Consistency − Ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − Enables transactions to operate independently of and transparent to each other.

• Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

The following commands are used to control transactions −

• BEGIN TRANSACTION − To start a transaction.

• COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

• ROLLBACK − To rollback the changes.

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The BEGIN TRANSACTION Command

Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. But a transaction will also ROLLBACK if the database is closed or if an error occurs.

The following is the simple syntax to start a transaction −

BEGIN;

or

BEGIN TRANSACTION;

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

The syntax for COMMIT command is as follows −

COMMIT;

or

END TRANSACTION;

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax for ROLLBACK command is as follows −

ROLLBACK;

Example

Consider the table is having the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Now, let us start a transaction and delete records from the table having age = 25 and finally we use ROLLBACK command to undo all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

If you will check COMPANY table is still having the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Now, let us start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

If you will check the COMPANY table, it still has the following records −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

The PostgreSQL LIMIT clause is used to limit the data amount returned by the SELECT statement.

Syntax

The basic syntax of SELECT statement with LIMIT clause is as follows −

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

The following is the syntax of LIMIT clause when it is used along with OFFSET clause −

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query.

Example

Consider the table having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following is an example, which limits the row in the table according to the number of rows you want to fetch from table −

testdb=# SELECT * FROM COMPANY LIMIT 4;

This would produce the following result −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

However, in certain situation, you may need to pick up a set of records from a particular offset. Here is an example, which picks up three records starting from the third position −

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

This would produce the following result −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

The PostgreSQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns.

Syntax

The basic syntax of ORDER BY clause is as follows −

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in column-list.

Example

Consider the table having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following is an example, which would sort the result in ascending order by SALARY −

testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;

This would produce the following result −

  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

The following is an example, which would sort the result in ascending order by NAME and SALARY −

testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

This would produce the following result −

 id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

The following is an example, which would sort the result in descending order by NAME −

testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

This would produce the following result −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)

In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables. It computes the aggregation once and allows us to reference it by its name (may be multiple times) in the queries.

The WITH clause must be defined before it is used in the query.

Syntax

The basic syntax of WITH query is as follows −

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Where name_for_summary_data is the name given to the WITH clause. The name_for_summary_data can be the same as an existing table name and will take precedence.

You can use data-modifying statements (INSERT, UPDATE or DELETE) in WITH. This allows you to perform several different operations in the same query.

Recursive WITH

Recursive WITH or Hierarchical queries, is a form of CTE where a CTE can reference to itself, i.e., a WITH query can refer to its own output, hence the name recursive.

Example

Consider the table having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, let us write a query using the WITH clause to select the records from the above table, as follows −

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

The above given PostgreSQL statement will produce the following result −

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, let us write a query using the RECURSIVE keyword along with the WITH clause, to find the sum of the salaries less than 20000, as follows −

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

The above given PostgreSQL statement will produce the following result −

  sum
-------
 25000
(1 row)

Let us write a query using data modifying statements along with the WITH clause, as shown below.

First, create a table COMPANY1 similar to the table COMPANY. The query in the example effectively moves rows from COMPANY to COMPANY1. The DELETE in WITH deletes the specified rows from COMPANY, returning their contents by means of its RETURNING clause; and then the primary query reads that output and inserts it into COMPANY1 TABLE −

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

The above given PostgreSQL statement will produce the following result −

INSERT 0 3

Now, the records in the tables COMPANY and COMPANY1 are as follows −

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

The PostgreSQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.

Syntax

The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows −

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example

Consider the table having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Let us add two more records to this table as follows −

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, ''Paul'', 32, ''California'', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, ''Allen'', 25, ''Texas'', 15000.00 );

Now, the records in the COMPANY table would be −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

First, let us see how the following SELECT query returns duplicate salary records −

testdb=# SELECT name FROM COMPANY;

This would produce the following result −

 name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

Now, let us use DISTINCT keyword with the above SELECT query and see the result −

testdb=# SELECT DISTINCT name FROM COMPANY;

This would produce the following result where we do not have any duplicate entry −

 name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

The HAVING clause allows us to pick out particular rows where the function”s result meets some condition.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following is the position of the HAVING clause in a SELECT query −

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause −

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Consider the table having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following is an example, which would display record for which the name count is less than 2 −

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

This would produce the following result −

  name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

Now, let us create three more records in COMPANY table using the following INSERT statements −

INSERT INTO COMPANY VALUES (8, ''Paul'', 24, ''Houston'', 20000.00);
INSERT INTO COMPANY VALUES (9, ''James'', 44, ''Norway'', 5000.00);
INSERT INTO COMPANY VALUES (10, ''James'', 45, ''Texas'', 5000.00);

Now, our table has the following records with duplicate names −

  id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

The following is the example, which would display record for which the name count is greater than 1 −

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

This would produce the following result −

 name
-------
 Paul
 James
(2 rows)

The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement to group together those rows in a table that have identical data. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

You can use more than one column in the GROUP BY clause. Make sure whatever column you are using to group, that column should be available in column-list.

Example

Consider the table having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

If you want to know the total amount of salary of each customer, then GROUP BY query would be as follows −

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

This would produce the following result −

  name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

Now, let us create three more records in COMPANY table using the following INSERT statements −

INSERT INTO COMPANY VALUES (8, ''Paul'', 24, ''Houston'', 20000.00);
INSERT INTO COMPANY VALUES (9, ''James'', 44, ''Norway'', 5000.00);
INSERT INTO COMPANY VALUES (10, ''James'', 45, ''Texas'', 5000.00);

Now, our table has the following records with duplicate names −

  id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Again, let us use the same statement to group-by all the records using NAME column as follows −

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

This would produce the following result −

 name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

Let us use ORDER BY clause along with GROUP BY clause as follows −

testdb=#  SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

This would produce the following result −

 name  |  sum
-------+-------
 Teddy | 20000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 15000
(7 rows)

Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates.

The following are commonly used constraints available in PostgreSQL.

• NOT NULL Constraint − Ensures that a column cannot have NULL value.

• UNIQUE Constraint − Ensures that all values in a column are different.

• PRIMARY Key − Uniquely identifies each row/record in a database table.

• FOREIGN Key − Constrains data based on columns in other tables.

• CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.

• EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NOT NULL constraint is always written as a column constraint.

A NULL is not the same as no data; rather, it represents unknown data.

Example

For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULL values −

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age.

Example

For example, the following PostgreSQL statement creates a new table called COMPANY3 and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with same age −

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids.

We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a ”longstanding coding oversight”, primary keys can be NULL in SQLite. This is not the case with other databases

A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example

You already have seen various examples above where we have created COMAPNY4 table with ID as primary key −

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

FOREIGN KEY Constraint

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. They are called foreign keys because the constraints are foreign; that is, outside the table. Foreign keys are sometimes called a referencing key.

Example

For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns.

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

For example, the following PostgreSQL statement creates a new table called DEPARTMENT1, which adds three columns. The column EMP_ID is the foreign key and references the ID field of the table COMPANY6.

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

CHECK Constraint

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table.

Example

For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY as Zero.

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

EXCLUSION Constraint

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

Example

For example, the following PostgreSQL statement creates a new table called COMPANY7 and adds five columns. Here, we add an EXCLUDE constraint −

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH )
);

Here, USING gist is the type of index to build and use for enforcement.

You need to execute the command CREATE EXTENSION btree_gist, once per database. This will install the btree_gist extension, which defines the exclusion constraints on plain scalar data types.

As we have enforced the age has to be same, let us see this by inserting records to the table −

INSERT INTO COMPANY7 VALUES(1, ''Paul'', 32, ''California'', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, ''Paul'', 32, ''Texas'', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, ''Paul'', 42, ''California'', 20000.00 );

For the first two INSERT statements, the records are added to the COMPANY7 table. For the third INSERT statement, the following error is displayed −

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Dropping Constraints

To remove a constraint you need to know its name. If the name is known, it is easy to drop. Else, you need to find out the system-generated name. The psql command d table name can be helpful here. The general syntax is −

ALTER TABLE table_name DROP CONSTRAINT some_name;