PostgreSQL Tutorial

PostgreSQL is a free object-relational database server (ORDBMS), distributed 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 different APIs for creating, accessing, managing, searching, and copying saved 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. The so-called 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 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 database.

RDBMS is a relational database management system, which establishes connections between entities and finally obtains relational tables.

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

ORDBMS terminology

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

  • Database:A database is a collection of related tables.
  • Data table:A table is a matrix of data. A table in a database looks like a simple spreadsheet.
  • Column:A column (data element) contains the same kind of data, such as postal code data.
  • Row:A row (= tuple, or record) is a group of related data, such as a piece of user subscription data.
  • 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 indexes can quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table, 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, it is an integrity constraint that the relational model must satisfy, in order to ensure data consistency.

PostgreSQL features

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

  • Index: Users can customize index methods, or use the built-in B-tree, hash table, 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.

  • Multi-version concurrency control:PostgreSQL uses a multi-version concurrency control (MVCC, Multiversion concurrency control) 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, and are usually used to implement operations on views (VIEW), such as insert (INSERT), update (UPDATE), and delete (DELETE).

  • Data types: Including text, arbitrary-precision numeric arrays, JSON data, enumerated types, XML data

    etc.

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

  • NoSQL: Native support for JSON, JSONB, XML, HStore, and external data wrappers for NoSQL databases.

  • Data warehouse: Can smoothly migrate to GreenPlum, DeepGreen, HAWK, etc. in the same 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 the latest version:https://github.com/postgres-cn/pgdoc-cn/releases

Other extensions