PostgreSQL Tutorial

Introduction

Welcome to the PostgreSQL Tutorial. This tutorial is designed to give details to PostgreSQL, relational database concepts, and the SQL language. We only assume some general knowledge on DBMS and SQL language. No particular programming experience is required.

PostgreSQL is claimed to be the most advanced open source database solution. PostgreSQL is an object-relational database management system (ORDBMS). PostgreSQL is pronounced Post-Gres-Q-L. PostgreSQL development is performed by a team of mostly volunteer developers spread throughout the world and communicating via the Internet. It is a community project and is not controlled by any company.

The "PGDG" is an international, unincorporated association of individuals and companies who have contributed to the PostgreSQL project. The PostgreSQL Core Team (A committee of five to seven (currently six) senior contributors.) generally act as spokespeople for the PGDG.

PostgreSQL is distributed under a license similar to BSD and MIT. Basically, it allows users to do anything they want with the code, including reselling binaries without the source code. The only restriction is that you not hold them legally liable for problems with the software. There is also the requirement that this copyright appears in all copies of the software.

History of PostgreSQL

In 1986 the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc sponsored Berkeley POSTGRES Project which was led by Michael Stonebraker.

In 1987 the first demo version of the project is released. In June 1989, Version 1 was released to some external users. Version 2 and 3 were released in 1990 and 1991. Version 3 had support for multiple storage managers, a query executor was improved, rule system was rewritten. After that, POSTGRES has been started to be implemented in various research and development projects. For example, in late 1992, POSTGRES became the primary data manager for the Sequoia 2000 scientific computing project4. User community around the project also has been started increasing; by 1993, it was doubled.

In 1994, an SQL language interpreter was added to POSTGRES. And then, it was released to the web under the name of Postgres95. By 1996, the new name PostgreSQL was chosen for the project.

Supported Platforms

In general, PostgreSQL can be expected to work on these CPU architectures: x86, x86_64, IA64, PowerPC, PowerPC 64, S/390, S/390x, Sparc, Sparc 64, Alpha, ARM, MIPS, MIPSEL, M68K, and PA-RISC. Code support exists for M32R, NS32K, and VAX, but these architectures are not known to have been tested recently.

PostgreSQL can be expected to work on these operating systems: Linux (all recent distributions), Windows (Win2000 SP4 and later), FreeBSD, OpenBSD, NetBSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru64 Unix, and UnixWare. Other Unix-like systems may also work but are not currently being tested. In most cases, all CPU architectures supported by a given operating system will work.

Key features of PostgreSQL

Procedural languages:

PostgreSQL supports four standard procedural languages (which allows the users to write their own code which can be executed by database server) - PL/pgSQL, PL/Tcl, PL/Perl and PL/Python.

Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java etc. are also supported. But all these and many more non-standard procedural languages need additional packages to be installed, unlike standard procedural languages.

Indexes:

PostgreSQL supports B+-tree, hash, generalized search trees (GiST) and generalized inverted indexes (GIN). Users can also create their own customized indexes.

Triggers:

Triggers, which initializes an action by an SQL Data Management Language statement (DML), mostly by INSERT and UPDATE statement are fully supported.

Views can create virtual tables. It is supported by PostgreSQL.

MVCC:

Multiversion concurrency control (MVCC) is a system to manage concurrency where each user's action is not visible to others until a transaction is committed and thus multiple users can work simultaneously.

Rules:

Rules, which allow a hierarchy of an incoming query to be re-written, are supported by PostgreSQL.

Data Types :

The following data types are supported by PostgreSQL : Boolean, Arbitrary precision numeric, Character (text, varchar, char), Binary, Date/time (timestamp/time with/without timezone, date, interval), Money Enum, Bit strings, Text search type, Composite Variable length arrays (including text and composite types) up to 1 GB in total storage size, Geometric primitives, IPv4 and IPv6 addresses, CIDR blocks and MAC addresses, XML supporting XPath queries (as of 8.3), UUID (as of 8.3).

User defined objects:

It supports the creation of almost all new objects inside the database like Casts, Conversions, Data types, Domains, Functions including aggregate functions and window functions, Indexes including custom indexes for custom types Operators.

Inheritance:

In PostgreSQL, a table can be set to inherit their characteristics from a "parent" table.

Extensions:

To add additional functionality to PostgreSQL, several extensions can be installed.

Other important features:

Referential integrity constraints, Inner, outer (full, left and right), and cross joins, Sub-selects, Transactions, SSL, Domains, Tablespaces, Savepoints, Point-in-time recovery, implemented using Write-ahead logging, Two-phase commit, TOAST (The Oversized-Attribute Storage Technique) is used to transparently store large table attributes (such as big MIME attachments or XML messages) in a separate area, with automatic compression, Regular expressions, Common table expressions, Embedded SQL, Full text search, Per-column collation (from 9.1).

PostgreSQL supports most of the major features of SQL:2008 standard.

Tools to manage PostgreSQL

There are several Open Source as well as Paid tools are available as front-end to PostgreSQL. Here are a few of them which are widely used :

psql:

It is a command line tool and the primary tool to manage PostgreSQL. pgAdmin

It is a free and open source graphical user interface administration tool for PostgreSQL.

phpPgAdmin:

It is a web-based administration tool for PostgreSQL written in PHP. It is based on phpMyAdmin tool to manage MySQL.OpenOffice.org Base

It can be used as a front end tool to PostgreSQL.

pgFouine:

It is a log analyzer which creates reports from PostgreSQL log files. Proprietary tools

Lightning Admin for PostgreSQL, Borland Kylix, DBOne, DBTools Manager PgManager, Rekall, Data Architect, SyBase Power Designer, Microsoft Access, eRWin, DeZign for Databases, PGExplorer, Case Studio 2, pgEdit, RazorSQL, MicroOLAP Database Designer, Aqua Data Studio, Tuples, EMS Database Management Tools for PostgreSQL, Navicat, SQL Maestro Group products for PostgreSQL, Datanamic DataDiff for PostgreSQL, Datanamic SchemaDiff for PostgreSQL, DB MultiRun PostgreSQL Edition, SQLPro, SQL Image Viewer, SQL Data Sets etc.

What you will learn in w3resource PostgreSQL Tutorial

• Overview, History and What you will learn
• Data Types
• Basic PostgreSQL Commands
• Advanced Queries
• Aggregates
• Functions and Operators
• Constrains
• Joins
• Numbering Rows
• Advanced SELECT Statements
• PL/pgSQL
• Subqueries
• Views
• Transactions and Locks
• LIMIT and Cursor
• Enhancing Performance
• Managing Tables
• Import and Export Data
• Tools - psql, pgaccess and phpPgAdmin

Database Management System

If you are not habituated with database management system your can learn from the following :

You may refer PostgreSQL Documentation along with this tutorial.

Next: PostgreSQL Installation