Let's learn postgreSQL

Major enhancements were included in postgreSQL 13 release. More information can be found in here Many more features including, Server - Paritioning, Indexes, Optimizer, General Performance, Monitoring, System views, Authentication etc

Add user account to postgreSQL database Step:1 Add user to unix/linux - debian system First you have issue following command to add unix/linux user.. # adduser user It will ask name, password and bla bla......... You can change password by issuing command like this... # passwd password Step:2 Becomming a super user... # su - postgres Then u can connect to the database.. # psql template1 or # psql -d template1 -U postgres Step:3 Add user.... It creates user called "user" and password called "password" template1=# CREATE USER user WITH PASSWORD 'password'; Step:4 Add a database called testdb.... template1=# CREATE DATABASE testdb; Add user account to postgreSQL database Step:5 Grant privileges to that database... template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to user; exit from psql.. template1=# \q Step:6 Try to connect database .... Try to connect to the database "testdb" user "user" # su - user # p

First you have start the postgreSQL server.... # /etc/init.d/postgresql start step:1 Allow remote access to postgresql database.... First you open the file called /etc/postgresql/pg_hba.conf. # vim /etc/postgresql/8.1/main/pg_hba.conf Now append the following line. host all all ip_address net_mask auth_method "ip_address" means your ip. "net_mask" means net mask. "auth_method" means authentication method. There are few authentication methods. such as "password, trust, md5" like that... eg: host all all 192.168.0.2 255.255.255.0 trust When we add auth_method as 'trust', think as trust user. If we add as 'md5', have to issue password while connect to this machine. more details on postgresql doc then save and close step:2 Allow TCP/IP communication open the file called /etc/postgresql/8.1/main/postgresql.conf. (8.1 means version. It may be vary) Uncomment &quo

There are three approaches to backing up Postgresql database.. 1.SQL dump 2.File system level backups 3.On-line backups 1.SQL dump First log as postgres user.. #su - postgres or su -l postgres After that you have issue these .. #pg_dump dname > dump_name "dname" is the database name,you are going to backup. "dump_name" is dump name. 2.File system level backups 3.On-line backups comming soon...

Postgresql doesnot provide to modify tables, like Oralcle To modify table first you have to drop column and then ADD column again.. test2=# ALTER TABLE tbl1 DROP column col1; test2=# ALTER TABLE tbl1 ADD column col_new VARCHAR(10);

Just say two tables tbl1 and tbl2. tbl1 contains composite key (A,B,C). Then tbl2 contains composite key (A,B). When we want to create foriegn key to table tbl1(A,B) from tbl2(A,B), it gives error.... Because there is not a unique key in tbl1... eg: tbl1 A B C ---------- sa tt w sa tt q tbl2 A B ------ sa tt In that case, there is no unique key on tbl1 that match to tbl2. solution is, you have to create unique index to that A and B feilds in tbl1 table. eg: CREATE UNIQUE INDEX tbl1_A_B ON tbl1 (A,B);

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search. CREATE [ UNIQUE ] INDEX name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ TABLESPACE tablespace ] [ WHERE predicate ] Parameters UNIQUE Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error. name The name of the index to be created. No schema name can be included here; the index