Posts

Backup and Restore PostgreSQL Database

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...

Alter table - Modify table in PostgreSQL

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);

How to create foreign key reference to part of composite key in PosgreSQL

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);

Create a index in PostgreSQL

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

What is postgreSQL?

postgreSQL is an object-relational database management system(ORDBMS) based on postgres.It is an open-source product.It supports a large part of sql standards and offers many modern features. * complex queries * foreign keys * triggers * views * transactional integrity * multiversion concurrency control Also, postgreSQL can be extended by the user in many ways, for example by adding new * data types * functions * operators * aggregate functions * index methods * procedural languages There is a extension called postGIS to the postgreSQL. postGIS supports for geographical objects to the postgreSQL database, allowing it to be backend database for geographic information systems(GIS). And because of the liberal license, postgreSQL can be used, modified, and distributed by everyone free of charge for any purpose, be it private, commercial, or academic. How can we install postgresql in debian system..... Just search in your cache for postg

Postgresql - PostGIS

Postgresql is opensource product. There are good and bad things on it. Because if you are a programmer and you are working with web, I think postgresql is not good for back end database. Then you can use mysql for that purpose. But your going to working other stuff specially GIS stuff you can use postgresql. To add GIS capability to postgresql you have install postGIS in your machine... http://www.postgis.org/ And also it has well built server side procedural languages. To download postgresql latest version http://www.postgresql.org/ To download postgresql docs http://www.postgresql.org/docs/