- 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.
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.
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 or Slony-I.
- 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
RECURSIVEmodifier that refers the
WITHquery 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
PA-RISC. Most operating systems are also supported: