It's simple to remove duplicates from a table. Here is how.

Assume you have a table with duplicate rows:

Table: emp
id (Primary Key)	name
1	Ela
2	Ramesh
3	Ela
4	Ashok
5	Ela

In the table above, 'Ela' is present more than once with different ids. One way to remove the duplicate is GROUP the rows by name alone and then insert the results to a new table with newly generated ids. But the new ids will not match the old (at least for non-duplicate rows).

To ensure the ids will match, we need a workaround, which is comparatively simple.

Step 1:

Try to get the ids of the unique items present in the table. This can be done either by MIN or MAX function. For example,

SELECT
      MIN(id)
FROM
      emp
GROUP BY
      name;

This will result in the following table:

min

1

3

5

Step 2:

Hah... this gives me the unique id values. This is enough to remove the other rows using a sub query. Here it is:

DELETE
FROM
      emp 
WHERE
      id NOT IN
      (
        SELECT
               MIN(id)
        FROM
                emp
        GROUP BY
                name
       );

That's all folks! The result is a cleaned up table without duplicates:

Table: emp
id (Primary Key)	name
1	Ela
2	Ramesh
4	Ashok

Free Blog Counter

Introduction to Postgres Stored Procedure

Postgres does not directly seem to support stored procedures. However, it supports it through functions and cursors. Here is an example how to write a stored procedures in PostgreSQL:

1. Create a function that returns a cursor.

CREATE OR REPLACE FUNCTION
    get_employees(emp_cursor refcursor, dept_id int)
RETURNS refcursor AS 
$BODY$
    DECLARE
        sql_statement character varying;
    BEGIN

        sql_statement = 'SELECT id, name FROM emp WHERE dept_id = ' || CAST(dept_id AS character varying);
        
        OPEN emp_cursor FOR EXECUTE sql_statement;
        RETURN emp_cursor;
        
    END
$BODY$
LANGUAGE 'plpgsql' ;

That's all. Your stored procedure is ready to be accessed from your application code.

2. To call the sp from your code, you need to run two sql statements consecutively within a single transaction. Given below is sample JAVA code (A similar approach/workaround should be available in other languages too):

/*
Create your connection object here.
*/

connection.setAutoCommit(false);

/*
The above is required since every statement will be committed automatically by default. If your language does not support the above methodology, try database level transaction with BEGIN END statements.
*/

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT get_employees('emp_cursor', 1);");
resultSet = statement.executeQuery("FETCH ALL IN emp_cursor;");

/*
Do whatever stuff you want to do with the result set.
*/

connection.commit(); //Commit the transaction
connection.close();