PostgreSQL Tutorial

PostgreSQL is a free object-relational database server (ORDBMS), released under the flexible BSD license.

PostgreSQL developers pronounce it aspost-gress-Q-L。

PostgreSQL's slogan is "The world's most advanced open source relational database".

Reference content:PostgreSQL 10.1 Manual

What is a database?

A database is a warehouse that organizes, stores, and manages data according to data structures.

Each database has one or more distinct APIs for creating, accessing, managing, searching, and replicating the stored data.

We can also store data in files, but reading and writing data in files is relatively slow.

Therefore, we now use relational database management systems (RDBMS) to store and manage large amounts of data. A relational database is a database built on the relational model, using mathematical concepts and methods such as set algebra to process data in the database.

ORDBMS (Object-Relational Database Management System) is the product of combining object-oriented technology with traditional relational databases. Query processing is an important part of ORDBMS, and its performance directly affects the performance of the DBMS.

ORDBMS adds some new features on the basis of the original relational databases.

RDBMS is a relational database management system that establishes relationships between entities and ultimately produces relational tables.

OODBMS (object-oriented database management system) is an object-oriented database management system, which treats all entities as objects and encapsulates these object classes. Communication between objects is through messages. OODBMS object-relational databases are essentially still relational databases.

ORDBMS terminology

Before we start learning the PostgreSQL database, let's first understand some ORDBMS terminology:

  • Database:A database is a collection of related tables.
  • Data table:A table is a matrix of data. Tables in a database look like a simple spreadsheet.
  • Column:A column (data element) contains the same type of data, such as postal code data.
  • Row:A row (= tuple, or record) is a group of related data, such as a user subscription record.
  • Redundancy: Storing twice the data, redundancy reduces performance but improves data security.
  • Primary key: The primary key is unique. A data table can contain only one primary key. You can use the primary key to query data.
  • Foreign key:A foreign key is used to associate two tables.
  • Composite key: A composite key (combined key) uses multiple columns as an index key, generally used for composite indexes.
  • Index:Using an index allows quick access to specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. It is similar to a book's table of contents.
  • Referential integrity:Referential integrity requires that relationships do not allow references to non-existent entities. Together with entity integrity, they are integrity constraints that the relational model must satisfy, aiming to ensure data consistency.

PostgreSQL features

  • Functions: Through functions, instruction programs can be executed on the database server side.

  • Indexes: Users can customize index methods, or use built-in B-trees, hash tables, and GiST indexes.

  • Triggers: A trigger is an event triggered by an SQL statement query. For example, an INSERT statement may trigger a trigger that checks data integrity. Triggers are usually triggered by INSERT or UPDATE statements.

  • Multiversion concurrency control:PostgreSQL uses a multiversion concurrency control (MVCC) system for concurrency control. This system provides each user with a "snapshot" of the database. Every modification made by a user within a transaction is invisible to other users until the transaction is successfully committed.

  • Rules: Rules (RULE) allow a query to be rewritten, usually used to implement operations on views (VIEW), such as insert (INSERT), update (UPDATE), and delete (DELETE).

  • Data types: Including text, numeric arrays of arbitrary precision, JSON data, enumeration types, XML data

    and so on.

  • Full-text search: Through Tsearch2 or OpenFTS; Tsearch2 is embedded in version 8.3.

  • NoSQL: JSON, JSONB, XML, HStore native support, external data wrappers to NoSQL databases.

  • Data warehouse: Can smoothly migrate to GreenPlum, DeepGreen, HAWK, etc., which are also in the PostgreSQL ecosystem, using FDW for ETL.

Related resources

PostgreSQL 10.1 Manual Online manual:http://www.example.com/manual/PostgreSQL/

Offline manual - PDF version:https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA, extraction code:xs7r。

Offline manual - CHM version:https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA, extraction code:tq4z。

View latest version:https://github.com/postgres-cn/pgdoc-cn/releases

Other extensions