PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. when we search the book by book "title" the result set should follow some rule in sorting the data. The rules are defined below.
1- First, the data sort by the title but searching value name.
2- Second, a new release book means result set should sort by publish_date, but it is on second priority.
Answer: This scenario can be implemented using the CASE statement with ORDER BY clause, to understanding the problem and its solution I am creating a book_inventory table, see the following script.


create table book_inventory
(
       id serial not null,
       isbn text,
       title text,
       author text,
       publisher text,
       publish_date date
);
--Insert some data in above table
insert into book_inventory
(
       isbn,
       title,
       author,
       publisher,
       publish_date 
)
values
(
       '1234567890123',
       'great',
       'Dawn Brown',
       'Publisher1',
       '12/01/2002'::date
),
(
       '2345678901234',
       'The great',
       'Jane Austen',
       'pb2',
       '01/01/2002'::date
),
(
       '3456789012345',
       'The great job',
       'Harper Lee',
       'pb3',
       '28/03/2019'::date
),
(
       '4567890123456',
       'My great job',
       'Dilip Kumar Singh',
       'PB3',
       '12/02/2017'::date
),
(
       '1234567890143',
       'great',
       'Dilip',
       'pb4',
       '12/01/2010'::date
),
(
       '1234567890181',
       'heart',
       'DK',
       'pb5',
       '12/01/2003'::date
),
(
       '1234567896781',
       'article of great leaders',
       'DK',
       'pb6',
       '12/01/2005'::date
);


Now, we will write the queries to fetch data as per our requirement, and we will see the differences of result-set of the following PostgreSQL statements.
Step 1: In the following query, I will sort the data of the result-set by title value in ascending order. You will see the result-set is not as per our expectation.

select *
  from book_inventory
  where title like '%great%'
    order by title asc;


Result-set

Analysis of result-set: Our expectation- Title should be sort from the value 'great' but result-set sorted by alphabetically.

Step 2: Now, I will write the query to sort by column value.

select *
  from book_inventory
  where title like '%great%'
    order by
       case
       when title='great'
       then '1'
       else title end asc;


Result-set

Analysis of Result-set: You can see the result is sorted by the value "great" but it not sorted as per our expectations. For assumption rows 1 and 2 is the wrong to place, these should be in place of each other if sort by publish_date.

Step 3: Now, the following query will sort the records as per our expectation, means first, it should be sort with column particular column (title) value and with publish_date too, execute the following query it will return the expected result-set.

select *
  from book_inventory
  where title like '%great%'
    order by
       case
       when title='great'
       then '1'
       else title end asc
       ,publish_date desc;


Result-set


No suitable driver found for jdbc postgreSQL eclipse

Problem: Suppose you are creating an application using Java and for the backend you are using PostgreSQL, sometimes we face the error as below.
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432/postgres
    at java.sql.DriverManager.getConnection(DriverManager.java:700)
    at java.sql.DriverManager.getConnection(DriverManager.java:258)
    at MyClass.main(MyClass.java:28)
       
Answer: There may be many reasons to generates this error.
1- First, make sure you have installed the jdbc.
2- It may be incorrect classpath, please check classpath the Dependent Jars should not be included inside your own jar, they should be placed next to your own jar and then be specified in the Class-Path in the MANIFEST.MF.

Eg Your layout could be:

your.jar
postgresql-9.3-1101.jdbc3.jar


Then the MANIFEST.MF of your.jar should have an entry:

Class-Path: postgresql-9.3-1101.jdbc3.jar


Follow the link for more details: https://docs.oracle.com/javase/tutorial/deployment/jar/downman.html

3- JDBC 3 driver: The driver you are using is a JDBC 3 driver. Driver auto-loading was only introduced with JDBC 4 (Java 6). JDBC 4 autoloading works by drivers declaring what their driver class(es) are, presumably a JDBC 3 driver does not have that file. Your code has the Class.forName... commented out and the driver won't be loaded.

If you are using Java 6 or higher, upgrade to the JDBC 4 (Java 6) or JDBC 4.1 (Java 7) driver.

For the same types of the issue, there are some useful links are given below.

PostgreSQL Trigger and its Types

What are the triggers: The PostgreSQL contains database callback functions that are automatically applied when a specified action occurs within the database. In PostgreSQL, triggers are associated with a particular table or view and execute the specified function on the occurrence of specific events.

Why we Need Trigger:

1- Triggers help the database designer ensure specific actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.
2- To improve data integrity, the trigger can be used. When an action is performed on data, it is possible to check if the manipulation of the data concurs with the underlying business rules, and thus avoids erroneous entries in a table.

There are three types of triggers available in PostgreSQL

1- After Trigger
2- Before Trigger
3- instead of Trigger

When
Event
Row-level
Statement-level
BEFORE
INSERT/UPDATE/DELETE
Tables
Tables and views
AFTER
INSERT/UPDATE/DELETE
Tables
Tables and views
INSTEAD OF
INSERT/UPDATE/DELETE
Views
TRUNCATE
Tables


For example creating table first:

CREATE TABLE public.employee
(
    empid serial NOT NULL,
    fname text,
    lname text,
    username text,
    password text,
    contact text,
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL
);

After Trigger:

This trigger occurs after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed).

Syntax:

CREATE TRIGGER trigger_name
          AFTER <action/ insert/delete/update> ON Table_Name
          FOR EACH ROW
          EXCECUTE PROCEDURE function_name

         
Callback function: This function will execute automatically through trigger "employee_update" once an event occurs on the "Employee."

CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                new.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                ); 
        RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql;  



Example for UPDATE (AFTER TRIGGER).

Any changes in a row will trigger the following example.

CREATE TRIGGER after_employee_update
    AFTER UPDATE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();

         
1- Insert records in employee table- No Action
2- Update records in employee table- Trigger fire
3- Delete records from employee table- No action

If you want to execute check_employee_update function if salary value will be changed, see the following trigger example.


CREATE TRIGGER after_employee_update_for_salary
    AFTER UPDATE  Of Salary ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- The trigger will fire but only when salary will update.
3- Delete records from employee table- No action

Example for INSERT (AFTER TRIGGER):


CREATE TRIGGER after_employee_insert
    AFTER INSERT ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- Trigger fire
2- Update records in employee table- No Action
3- Delete records from employee table- No Action

Example for DELETE (AFTER TRIGGER):

If you are using the trigger on delete you have to make a little bit change in the above function (check_employee_update) because we are considering new value in check_employee_update while it will be not finding any new records, it will work for the INSERT and UPDATE but failed for DELETE, I am creating a new function as below for the Delete trigger.


CREATE OR REPLACE FUNCTION check_employee_delete() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                old.empid,
                                                old.fname,
                                                old.lname,
                                                old.username,
                                                old.password,
                                                old.contact,
                                                old.salary,
                                                old.doj,
                                                current_timestamp
                                ); 
        RETURN OLD;  
    END; 
$employee$ LANGUAGE plpgsql;


Note: I am considering old value after delete. If you are using the same audit table for all operations, then you can use a flag column in the audit table to recognize the action.

Trigger:

CREATE TRIGGER after_employee_delete
    AFTER DELETE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_delete();


1- Insert records in employee table- No Action
2- Update records in employee table- No Action
3- Delete records from employee table- Trigger fire

Before Trigger

This trigger occurs before the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has ended).

Syntax:

CREATE TRIGGER trigger_name
          BEFORE <action/ insert/delete/update> ON Table_Name
          FOR EACH ROW
          EXCECUTE PROCEDURE function_name

         
Note: BEFORE TRIGGER is used when you want to perform any task before any changes affect the table.

Example for UPDATE (BEFORE TRIGGER)

I am using the same function above described

CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                new.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                ); 
        RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql; 


Before Trigger:

CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- Trigger fire
3- Delete records from employee table- No action

If you want to execute check_employee_update function if salary value will be changed, see the following trigger example.

CREATE TRIGGER before_employee_update_for_salary
    BEFORE UPDATE  Of Salary ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- The trigger will fire but only when salary will update.
3- Delete records from employee table- No action         

Example for INSERT (BEFORE TRIGGER):


CREATE TRIGGER before_employee_insert
    BEFORE INSERT ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();

1- Insert records in employee table- Trigger fire
2- Update records in employee table- No Action
3- Delete records from employee table- No Action

Example for DELETE (BEFORE TRIGGER)

For the BEFORE DELETE TRIGGER for the employee table I will follow the same as above (After Delete in AFTER TRIGGER) like create a new function for delete that will insert the old records in audit.

CREATE TRIGGER before_employee_delete
    BEFORE DELETE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_delete();
         
1- Insert records in employee table- No Action
2- Update records in employee table- No Action
3- Delete records from employee table- Trigger fire

INSTEAD of Trigger

INSTEAD of Trigger operation (in the case of inserts, updates, or deletes on a view).
Suppose we have a view in PostgreSQL and you want to perform a trigger event then you have to choose the INSTEAD of TRIGGER. I am creating a VIEW, please refer to the following PostgreSQL statement to create VIEW.


CREATE VIEW employee_view AS
SELECT * FROM employee


I have only one table (employee) so create a VIEW using it but the purpose of VIEW and its implementation I will describe in the next article.
If I am going to use INSTEAD of TRIGGER, then I have to make some changes in function.

CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$ 
    BEGIN                                
        INSERT INTO employee
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$ 
    BEGIN                                
        INSERT INTO employee
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj
                                )
                                SELECT
                                                NEW.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj;
                                                               
                                INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                NEW.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                );
      
                                RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql;



In the above function, you can see I am inserting data into table employee and table audit because we know VIEW is a statement and its fetch the value from the tables at run time when we INSERT the row into VIEW we have to write some query as above function to insert rows at its related table.

INSERT SOME Records into VIEW as below,

INSERT INTO employee_view
(
          empid,
          fname,
          lname,
          username,
          password,
          contact,
          salary,
          doj
)
VALUES
(
          101,
          'Naina',
          'Singh',
          'naina@gmail.com',
          'xyz',
          '9088877767',
          120000,
          current_timestamp
);


You will see the inserted records as you wish.
Same as it is if you want to perform trigger on another action like UPDATE/DELETE, you have to change the function according to your requirements.

Please comment your suggestion/question/understanding in comment section.
You have any query you can directly contact me on dilip.ravi084@gmail.com, At the weekend definitely, I will reply.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...