PostgreSQL/Introduction to PostgreSQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search
TOC PostgreSQL
Introduction to PostgreSQL
Features

PostgreSQL (or Postgres for short) is an open source relational database management systems (RDBMS). In nearly all cases the main application interface to an RDBMS is SQL, the standardised language for accessing relational databases. But beyond SQL every RDBMS needs a lot of additional tools to handle the installation process as well as maintenance and optimization tasks. As such tasks act very closely to the implementation of each RDBMS, they greatly differ from RDBMS to RDBMS - unlike SQL.

The purpose of this book is an introduction to the PostgreSQL specific aspects like its architecture, installing procedure, maintenance and optimization tasks. Thus it is primarily aimed to database administrators. But it is only a starting point in these objectives and many topics are omitted. For a complete treatment we recommend to get through professional training courses offered by many consulting companies. And don't forget: there is a great and complete documentation of all the stuff we try to explain in the Wikibook on hand. Additionally some special topics are summarized in the PostgreSQL wiki.

The SQL interface is out of the scope of the Wikibook on hand. If you are interested in this specific topic, you may want to refer to the Wikibook SQL, which describes the SQL standard independent from any RDBMS implementation. It's a good starting point for PostgreSQL as its implementation of SQL is very close to the standard.

The Wikibook on hand is based on PostgreSQL version 10.

Characteristic features of PostgreSQL[edit]

  • You can define your own types. Just as with tables, types consist of a collection of named attributes so you could define an "employee" type consisting of a text name, an integer employee number and a decimal salary. Unlike tables, you also define input and output functions so that values of your new type can be read and written. It makes sense to ensure that the input function can interpret the values as represented by the output function so that your application can read its own handwriting, but that is not a formal requirement.
  • Inheritance is supported. Suppose you have a table of employees, none of whom currently earn commission. You want to add data about sales representatives to your table, and they will need a commission column. If you add a commission column to your existing employees table, it will always contain NULL in almost every row. Alternatively, you could create a new table just for the sales representatives. It inherits all the columns from the employees table and adds a commission column. PostgreSQL keeps track of the inheritance so you can query all employees (including sales reps), just the standard employees, or just the reps.
  • You can extend it with new functions (including aggregates) and operators. In fact this is essential if you want to support any new types you create beyond simply inputting, storing and retrieving them. User-defined functions are also required if you create your own indexing scheme, another possible consequence of creating new data types.

History of PostgreSQL[edit]

  • Originated from the Ingres project.
  • Post-Ingres project was an attempt to improve the type system.


TOC PostgreSQL
Introduction to PostgreSQL
Features