Operators (existing ones can be Tables can be set to inherit their characteristics from a parent table. Data in child tables will appear to exist in the parent tables, unless data is selected from the parent table using the ONLY keyword, i.e. SELECT * FROM ONLY parent_table;
. Adding a column in the parent table will cause that column to appear in the child table.
Inheritance can be used to implement table partitioning, using either triggers or rules to direct inserts to the parent table into the proper child tables.
As of 2010[update], this feature is not fully supported yet – in particular, table constraints are not currently inheritable. All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
Inheritanc
Inheritance can be used to implement table partitioning, using either triggers or rules to direct inserts to the parent table into the proper child tables.
As of 2010[update], this feature is not fully supported yet – in particular, table constraints are not currently inheritable. All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
Inheritance provides a way to map the features of generalization hierarchies depicted in entity relationship diagrams (ERDs) directly into the PostgreSQL database.
PostgreSQL can link to other systems to retrieve data via foreign data wrappers (FDWs).[37]
These can take the form of any data source, such as a file system, another relational database management system (RDBMS), or a web service. This means that regular database queries can use these data sources like regular tables, and even join multiple data-sources together.
Interfaces
For connecting to applications, PostgreSQL includes the built-in interfaces libpq (the official C application interface) and ECPG (an embedded C system). Third-party libraries for connecting to PostgreSQL are available for many programming languages, including C++,[38] Java,[39] Python,[40] Node.js,[41] Go,[42] and Rust.[43]
Procedural languagesECPG
(an embedded C system). Third-party libraries for connecting to PostgreSQL are available for many programming languages, including C++,[38] Java,[39] Python,[40] Node.js,[41] Go,[42] and Rust.[43]
Procedural languages
Triggers are events triggered by the action of SQL data manipulation language (DML) statements. For example, an INSERT statement might activate a trigger that checks if the values of the statement are valid. Most triggers are only activated by either INSERT or UPDATE statements.
Triggers are fully supported and can be attached to tables. Triggers can be per-column and conditional, in that UPDATE triggers can target specific columns of a table, and triggers can be told to execute under a set
Triggers are events triggered by the action of SQL data manipulation language (DML) statements. For example, an INSERT statement might activate a trigger that checks if the values of the statement are valid. Most triggers are only activated by either INSERT or UPDATE statements.
Triggers are fully supported and can be attached to tables. Triggers can be per-column and conditional, in that UPDATE triggers can target specific columns of a table, and triggers can be told to execute under a set of conditions as specified in the trigger's WHERE clause. Triggers can be attached
Triggers are fully supported and can be attached to tables. Triggers can be per-column and conditional, in that UPDATE triggers can target specific columns of a table, and triggers can be told to execute under a set of conditions as specified in the trigger's WHERE clause. Triggers can be attached to views by using the INSTEAD OF condition. 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/Python or PL/Perl.
PostgreSQL provides an asynchronous messaging system that is accessed through the NOTIFY, LISTEN and UNLISTEN commands. A session can issue a NOTIFY command, along with the user-specified channel and an optional payload, to mark a particular event occurring. Other sessions are able to detect these events by issuing a LISTEN command, which can listen to a particular channel. This functionality can be used for a wide variety of purposes, such as letting other sessions know when a table has updated or for separate applications to detect when a particular action has been performed. Such a system prevents the need for continuous polling by applications to see if anything has yet changed, and reducing unnecessary overhead. Notifications are fully transactional, in that messages are not sent until the transaction they were sent from is committed. This eliminates the problem of messages being sent for an action being performed which is then rolled back.
Many connectors for PostgreSQL provide support for this notification system (including libpq, JDBC, Npgsql, psycopg and node.js) so it can be used by external applications.
PostgreSQL can act as an effective, persistent "pub/sub" server or job se
Many connectors for PostgreSQL provide support for this notification system (including libpq, JDBC, Npgsql, psycopg and node.js) so it can be used by external applications.
PostgreSQL can act as an effective, persistent "pub/sub" server or job server by combining LISTEN with FOR UPDATE SKIP LOCKED,[48] a combination which has existed since PostgreSQL version 9.5[49][50]
Rules allow the "query tree" of an incoming query to be rewritten. "Query Re-Write Rules" are attached to a table/class and "Re-Write" the incoming DML (select, insert, update, and/or delete) into one or more queries that either replace the original DML statement or execute in addition to it. Query Re-Write occurs after DML statement parsing, but before query planning.
Other querying features
PostgreSQL manages its internal security on a per-role basis. A role is generally regarded to be a user (a role that can log in), or a group (a role of which other roles are members). Permissions can be granted or revoked on any object down to the column level, and can also allow/prevent the creation of new objects at the database, schema or table levels.
PostgreSQL's SECURITY LABEL feature (extension to SQL standards), allows for additional security; with a bundled loadable module that supports label-based mandatory access control (MAC) based on Security-Enhanced Linux (SELinux) security policy.[59][60]
PostgreSQL natively supports a broad number of external authentication mechanisms, including:
The GSSAPI, SSPI, Kerberos, peer, ident and certificate methods can also use a specified "map" file that lists which users matched by that authentication system are allowed to connect as a specific database user.
These methods are specified in the cluster's host-based authentication configuration file (pg_hba.conf), w
PostgreSQL manages its internal security on a per-role basis. A role is generally regarded to be a user (a role that can log in), or a group (a role of which other roles are members). Permissions can be granted or revoked on any object down to the column level, and can also allow/prevent the creation of new objects at the database, schema or table levels.
PostgreSQL's SECURITY LABEL feature (extension to SQL standards), allows for additional security; with a bundled loadable module that supports label-based mandatory access control (MAC) based on mandatory access control (MAC) based on Security-Enhanced Linux (SELinux) security policy.[59][60]
PostgreSQL natively supports a broad number of external authentication mechanisms, including:
The GSSAPI, SSPI, Kerberos, peer, ident and certificate methods can also use a specified "map" file that lists which users matched by that authentication system are allowed to connect as a specific database user.
These methods are specified in the cluster's host-based authentication configuration file (pg_hba.conf), which determines what connections are allowed. This allows control over which user can connect to which database, where they can connect from (IP address, IP address range, domain socket), which authentication system will be enforced, and whether the connection must use Transport Layer Security (TLS).
Standards compliance
PostgreSQL claims high, but not complete, conformance with the SQL standard. One exception is the handling of unquoted identifiers like table or column names. In PostgreSQL they are folded, internally, to lower case characters[62] whereas the standard says that unquoted identifiers should be folded to upper case. Thus, Foo
should be equivalent to FOO
not These methods are specified in the cluster's host-based authentication configuration file (pg_hba.conf), which determines what connections are allowed. This allows control over which user can connect to which database, where they can connect from (IP address, IP address range, domain socket), which authentication system will be enforced, and whether the connection must use Transport Layer Security (TLS).
PostgreSQL claims high, but not complete, conformance with the SQL standard. One exception is the handling of unquoted identifiers like table or column names. In PostgreSQL they are folded, internally, to lower case characters[62] whereas the standard says that unquoted identifiers should be folded to upper case. Thus, Foo
should be equivalent to FOO
not foo
according to the standard.
Benchmarks and performance
Notable organizations and products that use PostgreSQL as the primary database include:
- In 2009, the social-networking website Myspace used Aster Data Systems's nCluster database for data warehousing, which was built on unmodified PostgreSQL.[89][90]
- Geni.com uses PostgreSQL for their main genealogy database.[91]
- OpenStreetMap, a collaborative project to create a free editable map of the world.[92]
- Afilias, domain registries for .org, .info and others.[93][94]
- Sony Online multiplayer online games.[95]
- BASF, shopping platform for their agribusiness portal.[96]
- Reddit social news website.[97]
- Skype VoIP application, central business databases.[98]
- Sun xVM, Sun's virtualization and datacenter automation suite.[99]
- MusicBrainz, open online music encyclopedia.[100]
- The International Space Station – to collect telemetry data in orbit and replicate it to the ground.[101]
- MyYearbook social-networking site.[102]
- Instagram, a mobile photo-sharing service.[103]
- Disqus, an online discussion and commenting service.
- TripAdvisor, travel-information website of mostly user-generated content.[105]
- Yandex, a Russian internet company switched its Yandex.Mail service from Oracle to Postgres.[106]
- Amazon Redshift, part of AWS, a columnar online analytical processing (OLAP) system based on ParAccel's Postgres modifications.
- National Oceanic and Atmospheric Administration's (NOAA) National Weather Service (NWS), Interactive Forecast Preparation System (IFPS), a system that integrates data from the NEXRAD weather radars, surface, and hydrology systems to build detailed localized forecast models.Myspace used Aster Data Systems's nCluster database for data warehousing, which was built on unmodified PostgreSQL.software as a service