Convert ROWS into COLUMN

convert database Rows into Column its easy and simple.

If you have a table "master_stu" its having values like

stu_id stu_name class percentage
==== ====== === =======
1 vin sslc 88
1 vin hsc 90
2 swin sslc 75
2 swin hsc 89

you want to display above table into

stu_id stu_name class percentage class percentage
==== ====== === ======= === ========
1 vin sslc 88 hsc 90
2 swin sslc 75 hsc 89

just write a query like

select
tbl1.stu_id as stu_id, tbl1.stu_name,tbl1.class as class,tbl1.percentage as percentage,tbl2.class as class,tbl2.percentage as percentage
from
(select * from master_stu where class = 'sslc') as tbl1,
(select * from master_stu where class = 'hsc') as tbl2,
where
tbl1.stu_id=tbl2.stu_id

any query's mail me!

PostgreSQL Technical Features

Why use PostgreSQL?

* It doesn't cost money.
* Speed about the same factor as commercial databases.
* Supports a broader subset of SQL than MySQL.
* Supports transactions.
* Supports large tables that exceed Linux' file limit.
* Fully Programmable.


Technical Features

* Fully ACID compliant.
* ANSI SQL compliant.
* Referential Integrity.
* Replication (non-commercial and commercial solutions) allowing the duplication of the master database to multiple slave machines.
* Native interfaces for ODBC, JDBC, .Net, C, C++, PHP, Perl, TCL, ECPG, Python, and Ruby.
*Rules.
* Views.
* Triggers.
* Unicode.
* Sequences.
* Inheritance.
* Outer Joins.
* Sub-selects.
* An open API.
* Stored Procedures.
* Native SSL support.
* Procedural languages.
* Hot stand-by (commercial solutions).
* Better than row-level locking.
* Functional and Partial indexes.
* Native Kerberos authentication.
* Support for UNION, UNION ALL and EXCEPT queries.
* Loadable extensions offering SHA1, MD5, XML, and other functionality.
* Tools for generating portable SQL to share with other SQL-compliant systems.
* Extensible data type system providing for custom, user-defined datatypes and rapid development of new datatypes.
* Cross-database compatibility functions for easing the transition from other, less SQL-compliant RDBMS.

Installing PostgreSQL on BOSS

This tutorial is about how to installing PostgreSQL on BOSS( Bharat Operating System Solutions) Server.

just 3 step to install and remote system access to the database server

1) Install PostgreSQL
2) Change authentication method
3) Restart PostgreSQL Server

kindly go through step by step,

1) Install PostgreSQL

apt-get install postgresql

2) Change authentication method

By default, PostgreSQL database server remote access disabled for security reasons.
We need to edit file pg_hba.conf and postgresql.conf to change authentification method for accessing PostgreSQL database.


I) add IP address of the system to access the database server

vi /etc/postgresql/8.2/main/pg_hba.conf

--Append the following configuration lines to give access to 10.10.29.0/24 network:
(TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD)
host all all 10.10.29.0/24 255.255.255.0 trust

--Append the following configuration lines to give access to 192.175.45.56 system:
host all all 192.175.45.56 255.255.255.0 trust

Save and close the file.

II) If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step III for older version (7.x or older).

vi /etc/postgresql/8.2/main/postgres.conf

defaults to 'localhost', and '*' is all ip address,
Find configuration line that read as follows:
listen_addresses='localhost'
changed into like below
listen_addresses='*'

Or just bind to 202.54.1.2 and 202.54.1.3 IP address
listen_addresses='202.54.1.2, 202.54.1.3'


III) Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:
vi /etc/postgresql/8.2/main/postgres.conf

Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
tcpip_socket = true

Save and close the file.

3) Restart PostgreSQL Server

Type the following command:

/etc/init.d/postgresql restart

any query's mail me!

Delete duplicate rows in PostgreSQL

The basic assumptions are that you have a table, named "emp_exp", that contains a int field, called emp_id and that the emp_id field may contain duplicate rows.

having table "emp_exp", it contain rows like

emp_id organization
------- -------------
1567 POSTGRES
1567 ORACLE
2089 WEBRAIN
2089 IBM

1) First, as always, backup your database.
2) Create a unique id (i.e. like a primary key) for each row, if one does not exist. else follow the step 3, use your unique column in where clause and select clause of sub-query in step 3

I) CREATE SEQUENCE emp_sequence
INCREMENT 1 MINVALUE 1 MAXVALUE 1999999 START 1 CACHE 1;

-- add new column to the table and Populate the field
II) ALTER TABLE emp_exp ADD COLUMN id integer;
III) UPDATE emp_exp SET id = nextval('emp_sequence');

-- after above step your table look like (You have added id column)

id emp_id organization
-- ------- -------------
1 1567 POSTGRES
2 1567 ORACLE
3 2089 WEBRAIN
4 2089 IBM



--NOTE before delete row in your table, if you want to know no.of duplicate rows in your table kindly follow step 4

3) Delete the duplicates

I) DELETE FROM emp_exp
WHERE id IN (SELECT max(id)
FROM emp_exp
GROUP BY emp_id HAVING count(*) > 1)

---same query can be re-write like (OR)

I) DELETE FROM emp_exp
WHERE id NOT IN (SELECT min(id)
FROM emp_exp
GROUP BY emp_id HAVING count(*) >= 1)


now your table emp_exp, have only unique emp_id value



4) Estimate the number of duplicates

You can use the query below to estimate the number of duplicates, so that once the deletion takes place you can figure out if something has gone horribly wrong.

SELECT sum(dupes.c) - count(dupes.c)
FROM (SELECT count(*) AS c
FROM emp_exp
GROUP BY emp_id
HAVING count(*) > 1
) AS dupes

Delete the duplicate records from table in postgresql

DELETE FROM emp_exp

WHERE ctid NOT IN (SELECT min(ctid) FROM emp_exp GROUP BY username)

CTID : It uniquely identifies row in a table and it refer the physical location of the records in table.