PostgreSQL Development Essentials

A view is a virtual table based on the result set of an SQL statement. Just like a real table, a view consist of rows and columns. The fields in a view are from one or more real tables in the database. Generally speaking, a table has a set of definitions that physically stores data. A view also has a set of definitions built on top of table(s) or other view(s) that does not physically store data. The purpose of creating views is to make sure that the user does not have access to all the data and is being restricted through a view. Also, it's better to create a view if we have a query based on multiple tables so that we can use it straightaway rather than writing a whole PSQL again and again.

Database views are created using the CREATE VIEW statement. Views can be created from a single table or multiple tables, or another view.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE [condition];

Let's take a look at each of these commands:

You can then query this view as though it were a table. (In PostgreSQL, at the time of writing, views are read-only by default.) You can SELECT data from a view just as you would from a table and join it to other tables; you can also use WHERE clauses. Each time you execute a SELECT query using the view, the data is rebuilt, so it is always up-to-date. It is not a frozen copy stored at the time the view was created.

Let's create a view on supplier and order tables. But, before that, let's see what the structure of the suppliers and orders table is:

CREATE TABLE suppliers
(supplier_id number primary key,
Supplier_name varchar(30),
Phone_number number);
CREATE TABLE orders
(order_number number primary key,
Supplier_id  number references suppliers(supplier_id),
Quanity number,
Is_active varchar(10),
Price number);
CREATE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name  orders.quantity, orders.price 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';

The preceding example will create a virtual table based on the result set of the SELECT statement. You can now query the PostgreSQL VIEW as follows:

SELECT * FROM active_supplier_orders;

DROP VIEW IF EXISTS view_name;

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name(s)
WHERE condition;

CREATE OR REPLACE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name  orders.quantity, orders.price,order. order_number 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';;

A materialized view is a table that actually contains rows but behaves like a view. This has been added in the PostgreSQL 9.3 version. A materialized view cannot subsequently be directly updated, and the query used to create the materialized view is stored in exactly the same way as the view's query is stored. As it holds the actual data, it occupies space as per the filters that we applied while creating the materialized view.

CREATE MATERIALIZED VIEW  view_name AS SELECT  columns FROM table;

CREATE MATERIALIZED VIEW suppliers_matview AS
SELECT * FROM suppliers;

CREATE MATERIALIZED VIEW  view_name  FOR UPDATE 
AS 
SELECT columns FROM table;

CREATE MATERIALIZED VIEW suppliers_matview FOR UPDATE
AS
SELECT * FROM suppliers;

A cursor in PostgreSQL is a read-only pointer to a fully executed SELECT statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL backend. By executing a cursor and maintaining a reference to its returned result set, an application can more efficiently manage which rows to retrieve from a result set at different times without re-executing the query with different LIMIT and OFFSET clauses.

The four SQL commands involved with PostgreSQL cursors are DECLARE, FETCH, MOVE, and CLOSE.

The DECLARE command both defines and opens a cursor, in effect defining the cursor in memory, and then populates the cursor with information about the result set returned from the executed query. A cursor may be declared only within an existing transaction block, so you must execute a BEGIN command prior to declaring a cursor.

Here is the syntax for DECLARE:

DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ] CURSOR  FOR query
[ FOR { READ ONLY | UPDATE [ OF  column [, ...] ] } ]

DECLARE cursorname is the name of the cursor to create. The optional BINARY keyword causes the output to be retrieved in binary format instead of standard ASCII; this can be more efficient, though it is only relevant to custom applications as clients such as psql are not built to handle anything but text output. The INSENSITIVE and SCROLL keywords exist to comply with the SQL standard, though they each define PostgreSQL's default behavior and are never necessary. The INSENSITIVE SQL keyword exists to ensure that all data retrieved from the cursor remains unchanged from other cursors or connections. As PostgreSQL requires the cursors to be defined within transaction blocks, this behavior is already implied. The SCROLL SQL keyword exists to specify that multiple rows at a time can be selected from the cursor. This is the default in PostgreSQL, even if it is unspecified.

The CURSOR FOR query is the complete query and its result set will be accessible by the cursor when executed.

The [FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] cursors may only be defined as READ ONLY, and the FOR clause is, therefore, superfluous.

Let's begin a transaction block with the BEGIN keyword, and open a cursor named order_cur with SELECT * FROM orders as its executed select statement:

BEGIN;
DECLARE order_cur CURSOR
FOR SELECT * FROM orders;

Once the cursor is successfully declared, it means that the rows retrieved by the query are now accessible from the order_cur cursor.

FETCH [ FORWARD | BACKWARD]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM } 
cursor

FETCH 4 FROM order_cur;

CLOSE
Cursorname;

The GROUP BY clause enables you to establish data groups based on columns. The grouping criterion is defined by the GROUP BY clause, which is followed by the WHERE clause in the SQL execution path. Following this execution path, the result set rows are grouped based on like values of grouping columns and the WHERE clause restricts the entries in each group.

The following statement illustrates the syntax of the GROUP BY clause:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

The expression1, expression2, ... expression_n commands are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

Let's take a look at these commands:

The GROUP BY clause must appear right after the FROM or WHERE clause. Followed by the GROUP BY clause is one column or a list of comma-separated columns. You can also put an expression in the GROUP BY clause.

As mentioned in the previous paragraph, the GROUP BY clause divides rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function, for example, to calculate the sum of items or count the number of items in the groups.

Let's look at a GROUP BY query example that uses the SUM function (http://www.techonthenet.com/oracle/functions/sum.php). This example uses the SUM function to return the name of the product and the total sales (for the product).

SELECT product, SUM(sale) AS "Total sales"
FROM order_details
GROUP BY product;

In the select statement, we have sales where we applied the SUM function and the other field product is not part of SUM, we must use in the GROUP BY clause.

In the previous section, we discussed about GROUP BY clause, however if you want to restrict the groups of returned rows, you can use HAVING clause. The HAVING clause is used to specify which individual group(s) is to be displayed, or in simple language we use the HAVING clause in order to filter the groups on the basis of an aggregate function condition.

Note: The WHERE clause cannot be used to return the desired groups. The WHERE clause is only used to restrict individual rows. When the GROUP BY clause is not used, the HAVING clause works like the WHERE clause.

The syntax for the PostgreSQL HAVING clause is as follows:

SELECT expression1, expression2, ... expression_n, 
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING group_condition;

SELECT product, SUM(sale) AS "Total sales"
FROM order_details
GROUP BY product
Having sum(sales)>10000;

The PostgreSQL UPDATE query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update selected rows; otherwise, all the rows will be updated.

The basic syntax of the UPDATE query with the WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine n number of conditions using the AND or OR operators.

The following is an example that will update SALARY for an employee whose ID is 6:

UPDATE employee SET SALARY = 15000 WHERE ID = 6;

This will update the salary to 15000 whose ID = 6.

The LIMIT clause is used to retrieve a number of rows from a larger data set. It helps fetch the top n records. The LIMIT and OFFSET clauses allow you to retrieve just a portion of the rows that are generated by the rest of the query from a result set:

SELECT select_list
FROM table_expression
[LIMIT { number | ALL }] [OFFSET number]

If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). LIMIT ALL is the same as omitting the LIMIT clause.

The OFFSET clause suggests skipping many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause. If both OFFSET and LIMIT appear, then the OFFSET rows will be skipped before starting to count the LIMIT rows that are returned.

A subquery is a query within a query. In other words, a subquery is a SQL query nested inside a larger query. It may occur in a SELECT, FROM, or WHERE clause. In PostgreSQL, a subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. It is usually added within the WHERE clause of another SQL SELECT statement. You can use comparison operators, such as >, <, or =. Comparison operators can also be a multiple-row operator, such as IN, ANY, SOME, or ALL. It can be treated as an inner query that is an SQL query placed as a part of another query called as outer query. The inner query is executed before its parent query so that the results of the inner query can be passed to the outer query.

The following statement illustrates the subquery syntax:

SELECT column list
FROM table 
WHERE table.columnname expr_operator
(SELECT column FROM table)

The query inside the brackets is called the inner query. The query that contains the subquery is called the outer query.

PostgreSQL executes the query that contains a subquery in the following sequence:

Let's consider an example where you want to find employee_id, first_name, last_name, and salary for employees whose salary is higher than the average salary throughout the company.

We can do this in two steps:

        SELECT avg(salary) from employee;

        Result: 25000
        SELECT employee_id,first_name,last_name,salary
        FROM employee
        WHERE salary > 25000;

This does seem rather inelegant. What we really want to do is pass the result of the first query straight into the second query without needing to remember it, and type it back for a second query.

The solution is to use a subquery. We put the first query in brackets, and use it as part of

a WHERE clause to the second query, as follows:

SELECT employee_id,first_name,last_name,salary
FROM employee
WHERE salary > (Select avg(salary) from employee);

PostgreSQL runs the query in brackets first, that is, the average of salary. After getting the answer, it then runs the outer query, substituting the answer from the inner query, and tries to find the employees whose salary is higher than the average.

In the previous section, we saw subqueries that only returned a single result because an aggregate function was used in the subquery. Subqueries can also return zero or more rows.

Subqueries that return multiple rows can be used with the ALL, IN, ANY, or SOME operators. We can also negate the condition like NOT IN.

SELECT column1,column2,..
FROM table 1 outer
WHERE column1 operator( SELECT column1 from table 2 WHERE  column2=outer.column4)

SELECT last_name, salary, department_id
FROM employee outer
WHERE salary >
(SELECT AVG(salary)
FROM employee
WHERE department_id = outer.department_id);

WHERE EXISTS ( subquery );

SELECT *
FROM products
WHERE EXISTS (SELECT 1
FROM inventory
WHERE products.product_id = inventory.product_id);

SELECT *
FROM products

WHERE NOT EXISTS (SELECT 1
FROM inventory
WHERE products.product_id = inventory.product_id);

The PostgreSQL UNION clause is used to combine the results of two or more SELECT statements without returning any duplicate rows.

The basic rules to combine two or more queries using the UNION join are as follows:

By default, the UNION join behaves like DISTINCT, that is, eliminates the duplicate rows; however, using the ALL keyword with the UNION join returns all rows, including the duplicates, as shown in the following example:

SELECT <column_list>
FROM  table 
WHERE  condition
GROUP BY  <column_list>  [HAVING ] condition
UNION
SELECT <column_list>
FROM  table 
WHERE  condition
GROUP BY  <column_list>  [HAVING ] condition
ORDER BY column list;

The queries are all executed independently, but their output is merged. The Union operator may place rows in the first query, before, after, or in between the rows in the result set of the second query. To sort the records in a combined result set, you can use ORDER BY.

Let's consider an example where you combine the data of customers belonging to two different sites. The table structure of both the tables is the same, but they have data of the customers from two different sites:

SELECT customer_id,customer_name,location_id
FROM  customer_site1 
UNION
SELECT customer_id,customer_name,location_id
FROM  customer_site2
ORDER BY customer_name  asc;

Both the SELECT queries would run individually, combine the result set, remove the duplicates (as we are using UNION), and sort the result set according to the condition, which is customer_name in this case.

The tables we are joining don't have to be different ones. We can join a table with itself. This is called a self join. In this case, we will use aliases for the table; otherwise, PostgreSQL will not know which column of which table instance we mean. To join a table with itself means that each row of the table is combined with itself, and with every other row of the table. The self join can be viewed as a joining of two copies of the same table. The table is not actually copied but SQL carries out the command as though it were.

The syntax of the command to join a table with itself is almost the same as that of joining two different tables:

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b 
WHERE condition1 and/or condition2

To distinguish the column names from one another, aliases for the actual table names are used as both the tables have the same name. Table name aliases are defined in the FROM clause of the SELECT statement.

Let's consider an example where you want to find a list of employees and their supervisor. For this example, we will consider the Employee table that has the columns Employee_id, Employee_name, and Supervisor_id. The Supervisor_id contains nothing but the Employee_id of the person who the employee reports to.

In the following example, we will use the table Employee twice; and in order to do this, we will use the alias of the table:

SELECT a.emp_id AS "Emp_ID", a.emp_name AS "Employee Name",
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name"
FROM employee a, employee b
WHERE a.supervisor_id = b.emp_id;

For every record, it will compare the Supervisor_id to the Employee_id and the Employee_name to the supervisor name.

Another class of join is known as the OUTER JOIN. In OUTER JOIN, the results might contain both matched and unmatched rows. It is for this reason that beginners might find such joins a little confusing. However, the logic is really quite straightforward.

The following are the three types of Outer joins:

Left outer join

Left outer join returns all rows from the left-hand table specified in the ON condition, and only those rows from the other tables where the joined fields are equal (the join condition is met). If the condition is not met, the values of the columns in the second table are replaced by null values.

The syntax for the PostgreSQL LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON condition1, condition2

In the case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table1 that does not satisfy the join condition with any row in table2, a joined row is added with null values in the columns of table2. Thus, the joined table always has at least one row for each row in table1.

Let's consider an example where you want to fetch the order details placed by a customer. Now, there can be a scenario where a customer doesn't have any order placed that is open, and the order table contains only those orders that are open. In this case, we will use a left outer join to get information on all the customers and their corresponding orders:

SELECT customer.customer_id, customer.customer_name, orders.order_number
FROM customer
LEFT OUTER JOIN orders
ON customer.customer_id = orders.customer_id

This LEFT OUTER JOIN example will return all rows from the customer table and only those rows from the orders table where the join condition is met.

If a customer_id value in the customer table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

Right outer join

Another type of join is called a PostgreSQL RIGHT OUTER JOIN. This type of join returns all rows from the right-hand table specified in the ON condition, and only those rows from the other table where the joined fields are equal (join condition is met). If the condition is not met, the value of the columns in the first table is replaced by null values.

The syntax for the PostgreSQL RIGHT OUTER JOIN is as follows:

SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;
Condition1, condition2;

In the case of RIGHT OUTER JOIN, an inner join is performed first. Then, for each row in table2 that does not satisfy the join condition with any row in table1, a joined row is added with null values in the columns of table1. This is the converse of a left join; the result table will always have a row for each row in table2.

Let's consider an example where you want to fetch the invoice information for the orders. Now, when an order is completed, we generate an invoice for the customer so that he can pay the amount. There can be a scenario where the order has not been completed, so the invoice is not generated yet. In this case, we will use a right outer to get all the orders information and corresponding invoice information.

SELECT invoice.invoice_id, invoice.invoice_date,  orders.order_number
FROM invoice
RIGHT OUTER JOIN orders
ON invoice.order_number= orders.order_number

This RIGHT OUTER JOIN example will return all rows from the order table and only those rows from the invoice table where the joined fields are equal. If an order_number value in the invoice table does not exist, all the fields in the invoice table will display as <null> in the result set.

Full outer join

Another type of join is called a PostgreSQL FULL OUTER JOIN. This type of join returns all rows from the left-hand table and right-hand table with nulls in place where the join condition is not met.

The syntax for the PostgreSQL FULL OUTER JOIN is as follows:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Condition1,condition2;

First, an inner join is performed. Then, for each row in table1 that does not satisfy the join condition with any row in table2, a joined row is added with null values in the columns of table2. Also, for each row of table2 that does not satisfy the join condition with any row in table1, a joined row with null values in the columns of table1 is added.

Let's consider an example where you want to fetch an invoice information and all the orders information. In this case, we will use a full outer to get all the orders information and the corresponding invoice information.

SELECT invoice.invoice_id, invoice.invoice_date, orders.order_number
FROM invoice
FULL  OUTER JOIN orders
ON invoice.order_number= orders.order_number;

This FULL OUTER JOIN example will return all rows from the invoice table and the orders table and, whenever the join condition is not met, <null> will be extended to those fields in the result set.

If an order_number value in the invoice table does not exist in the orders table, all the fields in the orders table will display as <null> in the result set. If order number in order's table does not exist in the invoice table, all fields in the invoice table will display as <null> in the result set.

After reading this chapter, you will be familiar with advanced concepts of PostgreSQL. We talked about views and materialized views, which are really significant. We also talked about cursors that help run a few rows at a time rather than full query at once. This helps avoid memory overrun when results contain a large number of rows. Another usage is to return a reference to a cursor that a function has created and allow the caller to read the rows. In addition to these, we discussed the aggregation concept by using the GROUP BY clause, which is really important for calculations. Another topic that we discussed in this chapter is subquery, which is a powerful feature of PostgreSQL. However, subqueries that contain an outer reference can be very inefficient. In many instances, these queries can be rewritten to remove the outer reference, which can improve performance. Other than that, the concept we covered is join, along with self, union, and outer join; these are really helpful when we need data from multiple tables. In the next chapter, we will discuss conversion between the data types and how to deal with arrays. Also we will talk about some complex data types, such as JSON and XML.