PostgreSQL

Databases are indispensable when it comes to contemporary web applications. True - there are many personal static web pages on the Internet working without databases, but in today's dynamic information stream static pages are archaic. Dynamic content is starting to replace static content even on personal pages. When it comes to corporate business sites the presence of databases is a mandatory element.

Building a website which uses databases has many advantages. The whole content is structured, easily accessible and editable, and can be transferred or exported to another web application or a newer version of the latter. And while most of the amateur, non-professional, or open source projects use the light, quick and free MySQL databases, the professional web environments count on expensive powerful databases offering a higher level of security such as Oracle, MsSQL, Sybase. And as a free alternative to powerful corporate applications comes PostgreSQL.

PostgreSQL's history

PostgreSQL's origins lead us back to the 1970s. Back then, at the University of Berkeley, California, a new database software was created - Ingres. This software became the foundation of other popular database solutions, such as Sybase, MsSQL and NonStop SQL. However, in 1985, the leader of the Ingres project started a new, post-Ingres project, with which he aimed to address the database problems at the time. The new project was based on the ideas used in Ingres, but not on its source code. In the following years the project was completed and several versions of the Postgre database were released. Back then, it was not based on SQL, but on the QUEL query language. The last version using the QUEL interpreter was released in 1993.

In 1994, two Berkeley graduates converted Postgre to use the SQL interpreter instead of the QUEL one and labeled their version Postgre95. Since then, the SQL version gained huge popularity and in 1996 the project was officially renamed PostgreSQL.

PostgreSQL was licensed under the BSD license, which allows for the product to be modified and then distributed commercially. Due to this fact, there are a lot of companies, which offer commercial distributions of the PostgreSQL software, alongside with the free version.

PostgreSQL features

• Objects defined by the user

  The user can create new types of almost all the objects inside the database:

  • Conversions
  • Data types
  • Domains
  • Functions, including aggregate functions
  • Indexes
  • Operators

• MVCC

  MVCC stands for Multi-Version Concurrency Control - it allows the user to make changes to the database, which will remain invisible to others until the transaction is commuted. This way, the need for read locks is eliminated. Usually, the reader of the database must wait for the writer to finish and vice versa, since most database software applications rely on table-level, page-level, column-level, or row-level locking.

  In PostgreSQL, each row in a table has two transaction IDs - a creation and an expiration transaction ID for the transactions that created and expired the row. When there is an UPDATE, PostgreSQL creates a new row and expires the old one. The new row is the same as the old one, just a different version.

  MVCC also gives another edge to PostgreSQL - the ability to create live backups of the database, i.e. it can create backups for tables which are used at that moment - no matter if data is inserted, deleted or updated at the moment of the backup. With a database software application lacking MVCC, the table will have to be locked before a backup is created.

• Database triggers

  Database triggers are events, executed when a specific command is entered in a table or a database. They are most often used to verify input data or to restrict access. There are several classes of triggers - "BEFORE" and "AFTER" triggers, depending on the time of the trigger execution. There is also the "INSTEAD OF" trigger, which will execute in place of the triggering statement.

  Mostly, triggers are used with the UPDATE, DELETE or INSERT statements.

  An example of How to Create Table in PostgreSQL

  CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));

  An example of how to Insert data into PostgreSQL table

  INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America'); (inserts data into phonebook table)

  An example of how to Select data from PostgreSQL table (1)

  SELECT * FROM phonebook ORDER BY lastname; (returns all data for lastname)

  An example of how to Select data from PostgreSQL table (2)

  SELECT * FROM phonebook WHERE lastname = 'Doe'; (returns all data for Doe as a family name)

  An example of how to Select data from PostgreSQL table (3)

  SELECT * FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe'; (returns all data for the first and the last name)

  An example of how to Update data in PostgreSQL database

  UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe'; (updates the address and the telephone number of all persons named John Doe)

  An example of how to Delete data from PostgreSQL database

  DELETE FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe'; (deletes all data for John Doe)

• Indexes

  In PostgreSQL, the developers can enjoy the built-in support for several types of indexes: B+, hish, GiST and GiN. Additionally, everybody can create their own indexing method. In PostgreSQL, the indexes support several additional features:

  • backward index scanning
  • PostgreSQL can go through multiple indexes at the same time to satisfy a query
  • the user can create partial indexes using the WHERE statement when creating a new table. This significantly reduces the index size.
  • PostgreSQL allows the creation of expression indexes with an index of the result of an expression or function. The creation of expression indexes is not limited to the value of the columns.

• Data types

  PostgreSQL supports several native data types:

  • arrays
  • variable length text
  • IPv4 and IPv6 addresses
  • XML with support for Xpath queries

  Besides the native data types, PostgreSQL allows the users to create their own data types, which are later indexed using the GiST indexing type.

  PosgreSQL Data types
  Name	Aliases	Description
  bigint	int8	signed eight-byte integer
  bigserial	serial8	autoincrementing eight-byte integer
  bit [ (n) ]		fixed-length bit string
  bit varying [ (n) ]	varbit	variable-length bit string
  boolean	bool	logical Boolean (true/false)
  box		rectangular box in the plane
  bytea		binary data (“byte array”)
  character varying [ (n) ]	varchar [ (n) ]	variable-length character string
  character [ (n) ]	char [ (n) ]	fixed-length character string
  cidr		IPv4 or IPv6 network address
  circle		circle in the plane
  date		calendar date (year, month, day)
  double precision	float8	double precision floating-point number
  inet		IPv4 or IPv6 host address
  integer	int, int4	signed four-byte integer
  interval [ (p) ]		time span
  line		infinite line in the plane
  lseg		line segment in the plane
  macaddr		MAC address
  money		currency amount
  numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric of selectable precision
  path		geometric path in the plane
  point		geometric point in the plane
  polygon		closed geometric path in the plane
  real	float4	single precision floating-point number
  smallint	int2	signed two-byte integer
  serial	serial4	autoincrementing four-byte integer
  text		variable-length character string
  time [ (p) ] [ without time zone ]		time of day
  time [ (p) ] with time zone	timetz	time of day, including time zone
  timestamp [ (p) ] [ without time zone ]		date and time
  timestamp [ (p) ] with time zone	timestamptz	date and time, including time zone

• Cross platform

  Another great advantage of the PostgreSQL platform is that it is available on almost any operating system in use. It has a native support for all UNIX environments and in the latest version native support for Microsoft's Windows has been included. This allows for an easy migration between systems, no matter how they are set up.

• Extensible

  PostgreSQL is a completely free, open source-based database. Its license allows everyone to make modifications to the core functionality, adding new or improving the existing features. Thanks to this, PostgreSQL has a very big community of users, who modify it on a daily basis. PostgreSQL also allows commercial versions to be released, and there are several companies that have made a modified commercial version of the PostgreSQL software.

Enterprises using PostgreSQL

Among the Internet community, PostgreSQL is known as the most stable and powerful open source database software available. This is why several big enterprises have adopted PostgreSQL as their database software of choice. Among them are Yahoo, Whitepages.com, the U.S. State and Labor Departments, Greenpeace, IMDB (the Internet Movie Data Base) and Skype.

PostgreSQL with NTC Hosting

NTC Hosting offers its business clients and those whose sites serve many visitors a professional PgSQL Hosting solution. Our PgSQL hosting plans allow you to create between 1 and 5 databases and to use an up to 160GB database quota.

Postgre SQL is included in our Value, Plus and Exclusive plans and as an optional feature in the rest of our plans. If, at any point, you decide that you need additional PgSQL databases, you can always purchase more from the Add Services/Upgrades section.

Resources:

• PostgreSQL insert
• PostgreSQL select
• PostgreSQL update
• PostgreSQL delete