PostgreSQL is an “Object Relational Database Management System (ORDBMS)”. This database management system (DBMS) is similar to a relational database but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query langauge. It supports a large number of programming interfaces, including ODBC, Java (JDBC), Tcl/Tk, PHP, Perl and Python.
Let me explain some advance feature of Postgresql:
* Foreign keys
* Triggers
* Views
* Transactional integrity
* Table Inheritance
* Multiversion concurrency control
* Supports complex queries
* Server side language: sql, java, ruby, Paython, TCL, etc.
Trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.
Table inheritance, can be a useful tool for database designers. In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendant tables.
The features like foreign key references, and views will allow you to hide the complexity of the database from the application. You can also avoid the creation of complicated SQL commands.
Multiversion concurrency control will allow two or more sessions try to access the same data at the same time from a database.
Basic PSQL commands
| Command |
Actions |
| l |
List all databases |
| c dbname |
Connect to new database. |
| dt |
To view list of relations/tables |
| d tablename |
Describe the details of given table. |
| h |
Get a help on syntax of SQL commands |
| ? |
Lists all psql slash commands. |
| set |
System variables list. |
| q |
Quit psql |
Why to use PostgreSQL?????????
There are several reasons to go with Postgresql, I will try to make a brief outline.A well tuned Postgres is pretty close on SELECT performance to MySQL with small databases. With large tables MySQL has some bad performance problems, and Postgres performs much better. Write performance is also an issue with MySQL -- with a lot of traffic, it has serious problems with concurrent writes. Under heavy load, Postgres performs much better.
Security is huge, but PostgreSQL’s support and focus on data integrity, granular access controls, ACID compliance.
PostgreSQL operates on the principle that certain users have certain types of access to data. In PostgreSQL, these are called “roles” and can be created or managed using CREATE ROLE, ALTER ROLE, and DROP ROLE. Unlike MySQL, these can also be mapped and tied to system users, which means it can leverage different forms of system authentication, identify server authentication, LDAP server authentication, PAM, and Kerberos. For local connections, you can also use filesystem permissions by changing who can access the UNIX domain socket, and where it is located.
What is a PostgreSQL database used for?
PostgreSQL is used by several different web programming languages including PHP, and Python. These programming languages make it extremely easy to connect to a PostgreSQL database. It is also used for many content management scripts like Joomla, wordpress etc.
Final Notes:
In closing, as we know disadvantage always comes with advantage therefore, postgresql is also having some vulnerability. But Postgresql is ideal for those who want to create a Web application and performance is his main concern. Finally, it is upto you to find what database meet your requirement. If you have any questions, we would be happy to talk to you! :)
About the Author :
Saurabh Suman works as a Software Engineer in Bobcares. He loves reading books and listening to music in his free time.
Español
Deutsch
