PostgreSQL Tutorial
PostgreSQL is a free object-relational database server (ORDBMS) distributed under a flexible BSD license.
PostgreSQL developers pronounce it as post-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 (Database) is a repository organized, stored, and managed according to data structures.
Each database has one or more different 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, nowadays we use relational database management systems (RDBMS) to store and manage large amounts of data. A relational database is a database based 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 the combination of object-oriented technology and 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 links between entities, ultimately resulting in relational tables.
OODBMS is an object-oriented database management system, which treats all entities as objects and encapsulates these object classes. Communication between objects is done through messages. OODBMS, in essence, is still a relational database.
ORDBMS Terminology
Before we start learning PostgreSQL, let's understand some ORDBMS terminology:
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 type of data, such as postal code data.
Row: A row (=tuple, or record) is a set of related data, such as data for a user subscription.
Redundancy: Storing double data, redundancy reduces performance but improves data security.
Primary Key: A primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query data.
Foreign Key: A foreign key is used to relate two tables.
Composite Key: A composite key (combined key) uses multiple columns as an index key, typically used for composite indexing.
Index: An index allows quick access to specific information in the database table. An index is a structure that sorts the values of one or more columns in a database table, similar to the table of contents in a book.
Referential Integrity: Referential integrity requires that relationships do not reference non-existent entities. It is a constraint that the relational model must satisfy to ensure data consistency.
PostgreSQL Features
Functions: Functions allow executing command programs on the database server side.
Indexes: Users can define custom indexing methods or use built-in B-tree, hash table, and GiST indexes.
Triggers: Triggers are events triggered by SQL statement queries. For example, an INSERT statement may trigger a trigger to check data integrity. Triggers are usually triggered by INSERT or UPDATE statements.
Multi-Version Concurrency Control: PostgreSQL uses Multi-Version Concurrency Control (MVCC) for concurrency control, providing each user with a "snapshot" of the database. Modifications made by a user within a transaction are invisible to other users until the transaction is successfully committed.
Rules: Rules allow a query to be rewritten, typically used to implement operations on views such as INSERT, UPDATE, and DELETE.
Data Types: Includes text, arrays of arbitrary precision values, JSON data, enumerated types, XML data.
Full-Text Search: Integrated Tsearch2 or OpenFTS, embedded Tsearch2 in version 8.3.
NoSQL: Native support for JSON, JSONB, XML, HStore, and external data wrappers to NoSQL databases.
Data Warehouse: Smooth migration to GreenPlum, DeepGreen, HAWK, etc., part of the PostgreSQL ecosystem, using FDW for ETL.
Related Resources
PostgreSQL 10.1 Manual Online Manual: http://www.tutorialpro.org/manual/PostgreSQL/
Offline Manual - PDF Version: https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA, Extraction Code: xs7r. Offline Manual - CHM Version: https://pan.baidu.com/s/1zzBu-Z9unrPew9wl7L_y9w, Extraction Code: tq4z.
Check for the Latest Version: https://github.com/postgres-cn/pgdoc-cn/releases