PostgreSQL: DROP TABLE is not working


Problem: I want to drop my table, but I am not able to drop the table while I tried the query "DROP TABLE MyTable CASCADE;".

Solution: "DROP TABLE MyTable CASCADE;" this query is responsible to drop the table in PostgresSQL but it is not working it means we have to do some analysis step by step because there might be the other session using MyTable parallel.

1- Run the following query to obtain Access Exclusive lock


SELECT pid, relname
FROM pg_locks l
JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
WHERE t.relname = 'MyTable';


Note: here relname is the table name.

2- Once you run the above query you will found the list of pid, now in the second step we will kill the all pid by using the following query.


KILL 1234


Killing pid one by one is a lengthy task. You can pipe it all together, use the following command.


psql -c "SELECT pid FROM pg_locks l
    JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
    WHERE t.relname = 'MyTable';" | tail -n +3 | head -n -2 | xargs kill
     
       
3- Now, you can drop your table.


DROP TABLE MyTable CASCADE;

MongoDB Query: Remove duplicate records from collection except one


Problem: DBA work is critical and sensitive; we work on Non Relational or Relational Database and the data consistency is a common factor for both. For the data consistency, sometimes we need to remove the duplicate records except one.

Solution: some solutions are given below to remove duplicates of records in MongoDB. First, we will generate the scenario after that, and we will fix the issue with some command.

Suppose we have the Employee collection like below.


{
    "_id" : ObjectId("5e5f5cf8cad2677f9f839323"),
    "Name" : "Dilip",
    "EmpId" : 1.0,
    "Department" : "IT"
}
{
    "_id" : ObjectId("5e5f5d20cad2677f9f839327"),
    "Name" : "Dilip",
    "EmpId" : 1.0,
    "Department" : "IT"
}
{
    "_id" : ObjectId("5e5f5d27cad2677f9f839328"),
    "Name" : "Dilip",
    "EmpId" : 1.0,
    "Department" : "IT"
}
{
    "_id" : ObjectId("5e5f5d3bcad2677f9f839329"),
    "Name" : "Anil",
    "EmpId" : 2.0,
    "Department" : "HR"
}
{
    "_id" : ObjectId("5e5f5d42cad2677f9f83932a"),
    "Name" : "Anil",
    "EmpId" : 2.0,
    "Department" : "HR"
}


We can see the duplicate documents for employee "Dilip" and "Anil". Now what we will do we delete the document except each one of them.

Using Group aggregate


var duplicatesIds = [];
db.Employee.aggregate([
        {
            $group: {
                _id: {
                    EmpId: "$EmpId"
                },
                dups: {
                    "$addToSet": "$_id"
                },
                count: {
                    "$sum": 1
                }
            }
        }, {
            $match: {
                count: {
                    "$gt": 1
                }
            }
        }
    ], {
    allowDiskUse: true
}).forEach(function (doc) {
    doc.dups.shift();
    doc.dups.forEach(function (dupId) {
        duplicatesIds.push(dupId);
    })
});
printjson(duplicatesIds); 

db.Employee.remove({_id:{$in:duplicatesIds}}) 
db.Employee.find();


Now we will do an analysis of the above-written query.

1- var duplicatesIds = []: This is an array declaration where this query will push the duplicate IDs.

2-{$group:{_id:{EmpId:"$EmpId"},dups:{"$addToSet":"$_id"} ,count:{"$sum":1}}}: Here we are grouping the records on behalf of EmpId, and using $addToSet command, we can create an array "dups", and count:{"$sum":1} is counting the duplicate records.

3- {$match:{count:{"$gt":1}}}: Here we are filtering the records that have a count greater than 1. As the above group pipeline, we are counting the duplicate records on behalf of EmpId.

4- ForEach: we are iterating records one by one here which are grouped EmpId, here we will find the array of duplicate records, for example 
"dups" : [
        ObjectId("5e5f5d20cad2677f9f839327"),
        ObjectId("5e5f5d27cad2677f9f839328"),
        ObjectId("5e5f5cf8cad2677f9f839323")
    ].

5- doc.dups.shift():Here we are removing one record which will not be deleted, and It means we will delete the duplicates except one document.

6- doc.dups.forEach(function (dupId): here again, we are iterating the array to push (duplicatesIds.push(dupId)) it records (duplicatesIds)on the above-declared array.

7- db.Employee.find(): to fetch the records.
Now finally execute the above MongoDB query, and you will find the following records.


{
    "_id" : ObjectId("5e5f5d20cad2677f9f839327"),
    "Name" : "Dilip",
    "EmpId" : 1.0,
    "Department" : "IT"
}
{
    "_id" : ObjectId("5e5f5d42cad2677f9f83932a"),
    "Name" : "Anil",
    "EmpId" : 2.0,
    "Department" : "HR"
}


It means all duplicate records have been removed except one.

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: DROP TABLE is not working

Problem: I want to drop my table, but I am not able to drop the table while I tried the query "DROP TABLE MyTable CASCADE;". ...