Operating System

Programming Languages

Database

General

The image “http://images.picsearch.com/is?OwemJfIsR3x98EPtUH6RNIbb9ev_X1n6Ln4hRCbcoWk” cannot be displayed, because it contains errors.Postgre Sql - Introduction

"PostgreSQL is a unix-based freeware Object-Relational Database Management System supporting almost all SQL constructs, including sub-selects, transactions, and user-defined types and functions. PostgreSQL has evolved from the same type of sophistication as Sybase, Informix and other professional RDBMSs. It supports everything from basic table structures, to extremely large datasets. This flexibility allows PostgreSQL to compete with and often times win over a plethora of alternatives including MySQL, mSQL, Microsoft SQL Server, Oracle, and Sybase."

 

History of PostgreSQL
By 1996, it became clear that the name "Postgres95" would not stand the test of time. A new name, PostgreSQL, was chosen to reflect the relationship between original Postgres and the more recent versions with SQL capability. At the same time, the version numbering was reset to start at 6.0, putting the numbers back into the sequence originally begun by the Postgres Project.The emphasis on development for the v1.0.x releases of Postgres95 was on stabilizing the backend code. With the v6.x series of PostgreSQL, the emphasis has shifted from identifying and understanding existing problems in the backend to augmenting features and capabilities, although work continues in all areas.

Major enhancements include:
Important backend features, including subselects, defaults, constraints, and triggers, have been implemented

Additional SQL92-compliant language features have been added, including primary keys, quoted identifiers, literal string type coersion, type casting, and binary and hexadecimal integer input.

Built-in types have been improved, including new wide-range date/time types and additional geometric type support.

Overall backend code speed has been increased by approximately 20-40%, and backend startup time has decreased 80% since v6.0 was released.

Features

  1. Functions
    Functions allow blocks of code to be executed by the server. Although these blocks can be written in SQL, the lack of basic programming operations, such as branching and looping, has driven the adoption of other languages inside of functions. Some of the languages can even execute inside of triggers. Functions in PostgreSQL can be written in the following languages:
    1. A built-in language called PL/pgSQL resembles Oracle's procedural language PL/SQL
    2. Scripting languages are supported throughPL/PERL, plPHP,PL/Python,PL/Ruby,PL/sh.
    3. Compiled languages C,C++ or Java
    Functions can be defined to execute with the privileges of either the caller or the user who defined the function. Functions are sometimes referred to as stored procedures, although there is a slight technical distinction between the two.
  2. Indexes
    1. User-defined indexes can be created, or the built-in B-tree, hash and GiST indices can be used. Indexes in PostgreSQL also support the following features:
    2. PostgreSQL is capable of scanning indexes backwards when needed; you never need a separate index to support ORDER BY field DESC.
    3. Expressional indexes can be created with a index of the result of an expression or function, instead of simply the value of a column.
    4. Partial indexes , which only index part of a table, can be created by adding a WHERE clause to the end of the CREATE INDEX statement. This allows a smaller index to be created.
    5. As of version 8.1, bitmap index scans are supported. This involves reading multiple indexes and generating a bitmap that expresses their intersection with the tuples that match the selection criteria. This provides a way of composing indexes together; on a table with 20 columns, there are, in principle, 20! indexes that could be defined - which is far too many to actually use. If you create one index on each column, bitmap scans can compose arbitrary combinations of those indexes at query time for each column that seems worth considering as a constraint.
  3. Triggers
    Triggers are fully supported and can be attached to tables and to views. Multiple triggers are fired in alphabetical order. In addition to calling functions written in the native PL/PgSQL, triggers can also invoke functions written in other languages like PL/Perl.
  4. MVCC
    PostgreSQL manages concurrency through a system known as Multi-Version Concurrency Control(MVCC), which gives each user a "snapshot" of the database, allowing changes to be made without being visible to other users until a transaction is committed. This largely eliminates the need for read locks, and ensures the database maintains theACID principles in an efficient manner.
  5. Rules
    Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement updatable views.
  6. Data types
    A wide variety of native data types are supported, including:
    1. Arbitrary precision numerics
    2. Unlimited length text
    3. Geometric primitives
    4. IPand IPv6 addresses
    5. CIDRblocks, andMAC address data types
    6. Arrays
  7. User-defined objects
    New types of almost all objects inside the database can be created, including:
    1. Indices
    2. Operators
    3. Aggregates
    4. Domains
    5. Casts
    6. Conversions
  8. Inheritance
    Tables can be set to inherit their characteristics from a "parent" table. Data is shared between "parent" and "child(ren)" tables. Tuples inserted or deleted in the "child" table will respectively be inserted or deleted in the "parent" table. Also adding a column in the parent table will cause that column to appear in the child table as well. This feature is not fully supported yet -- in particular, table constraints are not currently inheritable.

    Inheritance provides a way to map the features of generalization hierarchies depicted in Entity Relationship Diagrams (ERD) directly into the PostgreSQL database.

 

Hosted by www.Geocities.ws

1