PostgreSQL¶

Basic functions¶

ACID compliant

ACID (A tomicity, C onsistency, I solation, D urability) is a series of properties that database transactions should fulfil to guarantee the validity of the data even in the event of a fault.

SQL:2011

temporal_tables also meet the SQL standard ISO/IEC 9075:2011, including:

Time period definitions
Valid time tables
Transaction time tables (system-versioned tables) with time-sliced and sequenced queries

Data types

The following data types are supported out of the box:

primitive data types: Integer, Numeric, String, Boolean

structured data types: Date/Time, Array, Range, UUID

document types: JSON/JSONB, XML, key-value (Hstore)

geometric data types: point, line, circle, polygon

adjustments: composite, custom Types
transactional data definition language (DDL)

Transactional DDL is implemented via write-ahead logging. Big changes are also possible, but not adding and dropping databases and tables:
$ psql mydb
mydb=# DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist
DROP TABLE
mydb=# BEGIN;
BEGIN
mydb=# CREATE TABLE foo (bar int);
CREATE TABLE
mydb=# INSERT INTO foo VALUES (1);
INSERT 0 1
mydb=# ROLLBACK;
ROLLBACK
mydb=# SELECT * FROM foo;
ERROR: relation "foo" does not exist

Concurrent Index

PPostgreSQL can create indexes without having to lock write access to tables.

See also

Building Indexes Concurrently

Extensions

PostgreSQL can easily be extended. The contrib/ directory supplied with the source code contains various extensions that are described in Appendix F. Other extensions have been developed independently, such as PostGIS.

Common Table Expression

WITH Queries (Common Table Expressions) divides complex queries into simpler queries, e.g .:

WITH regional_insolation AS (
    SELECT region, SUM(amount) AS total_insolation
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_insolation
    WHERE total_insolation > (SELECT SUM(total_insolation)/10 FROM regional_insolation)
)

There is also a RECURSIVE modifier that refers the WITH query to its own output. The following is an example of how to sum the numbers from 1 to 100:

WITH RECURSIVE t (n) AS (
    WERTE (1)
  UNION ALL
    SELECT n + 1 FROM t WO <100
)
SELECT sum (n) FROM t;

Multi-Version Concurrency Control (MVCC)

Multi-Version Concurrency Control allows two or more sessions to access the same data at the same time without compromising the integrity of the data.

Cross platform

PostgreSQL runs on common CPU architectures such as x86, PowerPC, Sparc, ARM, MIPS or PA-RISC. Most operating systems are also supported: Linux, Windows, FreeBSD, OpenBSD, NetBSD, Mac OS, AIX, HP/UX and Solaris.