Author Topic: PostgreSQL Features  (Read 441 times)

0 Members and 1 Guest are viewing this topic.

Offline h2obubbler

  • Full Member
  • *
  • Posts: 228
  • Karma: +0/-0
  • Life is like a box of chocolate.
    • View Profile
PostgreSQL Features
« on: October 21, 2008, 05:10:29 PM »


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:

    * A built-in language called PL/pgSQL resembles Oracle\'s procedural language PL/SQL.
    * Scripting languages are supported through PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl and PL/Scheme.
    * Compiled languages C, C++, or Java (via PL/Java).
    * The statistical language R through PL/R.

PostgreSQL supports row-returning functions, where the output of the function is a set of values which can be treated much like a table within queries.

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.


PostgreSQL includes built-in support for B+-tree, hash, GiST and GiN indexes. In addition, user-defined index methods can be created, although this is quite an involved process. Indexes in PostgreSQL also support the following features:

    * PostgreSQL is capable of scanning indexes backwards when needed; a separate index is never needed to support ORDER BY field DESC.
    * Expression indexes can be created with an index of the result of an expression or function, instead of simply the value of a column.
    * 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.
    * The planner is capable of using multiple indexes together to satisfy complex queries, using temporary in-memory bitmap index operations.


Triggers are events triggered by the action of SQL DML statements. For example, an INSERT statement might activate a trigger that checked if the values of the statement were valid. Most triggers are only activated by either INSERT or UPDATE statements.

Triggers are fully supported and can be attached to tables but not to views. Views can have rules, though. 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.


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 the ACID principles in an efficient manner.


Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement updatable views.

Data types

A wide variety of native data types are supported, including:

    * Arrays
    * Arbitrary precision numerics
    * Variable length text
    * Geometric primitives
    * IPv4 and IPv6 addresses
    * CIDR blocks and MAC addresses
    * XML (as of 8.3)
    * UUID (as of 8.3)

In addition, users can create their own data types which can usually be made fully indexable via PostgreSQL\'s GiST infrastructure. Examples of these are the geographic information system (GIS) data types from the PostGIS project for PostgreSQL.

User-defined objects

New types of almost all objects inside the database can be created, including:

    * Casts
    * Conversions
    * Data types
    * Domains
    * Functions, including aggregate functions
    * Indexes
    * Operators (existing ones can be overloaded)
    * Procedural languages


Tables can be set to inherit their characteristics from a "parent" table. Data is shared between "parent" and "child" 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. This means that attempting to insert the id of a row from a child table into a table that has a foreign key constraint referencing a parent table will fail because Postgres doesn\'t recognize that the id from the child table is also a valid id in the parent table.

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

Techronnati | where technology never sleeps

PostgreSQL Features
« on: October 21, 2008, 05:10:29 PM »

Mountain View


Related Topics

  Subject / Started by Replies Last post
0 Replies
Last post October 21, 2008, 04:57:28 PM
by h2obubbler
0 Replies
Last post October 21, 2008, 04:58:39 PM
by h2obubbler
0 Replies
Last post January 06, 2009, 07:20:01 AM
by Corps
0 Replies
Last post September 03, 2014, 07:08:32 AM
by CyborgRepublic
0 Replies
Last post February 16, 2016, 10:47:13 AM
by JusticeLeague

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.