w3resource logo


PostgreSQL Tutorial

PostgreSQL Tutorial

<<PreviousNext>>

Description

This document gives you an overview of PostgreSQL and lists what you will learn in the following documents of w3resource PostgreSQL tutorial.

What is PostgreSQL

PostgreSQL is claimed to be the most advanced open source database solution. PostgreSQL is an object-relational database management system (ORDBMS).

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
  • Transactions and Locks
  • LIMIT and Cursor
  • Enhancing Performance
  • Managing Tables
  • Import and Export Data
  • Tools - psql, pgaccess and phpPgAdmin

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.

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

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 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 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, 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.

You may refer PostgreSQL Documentation along with this tutorial.



<<PreviousNext>>