YouTip

# PostgreSQL Tutorial !(#) PostgreSQL is a free object-relational database server (ORDBMS) released under the 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: [PostgreSQL 10.1 Manual](#) * * * ## What is a Database? A database is a warehouse for organizing, storing, and managing data 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, 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 a product of combining object-oriented technology with traditional relational databases. Query processing is an important component of ORDBMS, and its performance directly affects the performance of the DBMS. ORDBMS adds some new features on top of the original relational database. RDBMS is a relational database management system that establishes connections between entities, resulting in relational tables. OODBMS (Object-Oriented Database Management System) treats all entities as objects and encapsulates these object classes. Communication between objects is done 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. * **Table:** A table is a matrix of data. In a database, a table looks like a simple spreadsheet. * **Column:** A column (data element) contains the same type of data, such as postal codes. * **Row:** A row (= tuple, or record) is a set of related data, such as a user's subscription data. * **Redundancy:** Storing data twice, redundancy reduces performance but improves data security. * **Primary Key:** The primary key is unique. A table can only contain 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 (combination 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 the table of contents of a book. * **Referential Integrity:** Referential integrity requires that references to non-existent entities are not allowed in a relationship. Along with entity integrity, it is an integrity constraint 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 define custom index methods or use built-in B-tree, hash, and GiST indexes. * **Triggers:** Triggers are events triggered by SQL statement queries. For example, an INSERT statement might trigger a trigger that checks data integrity. Triggers are usually triggered by INSERT or UPDATE statements. * **Multiversion Concurrency Control:** PostgreSQL uses Multiversion Concurrency Control (MVCC) for concurrency control. This system provides each user with a "snapshot" of the database. Each 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, UPDATE, DELETE. * **Data Types:** Includes text, arrays of arbitrary precision numbers, JSON data, enumeration types, XML data, etc. * **Full-Text Search:** Through Tsearch2 or OpenFTS, Tsearch2 is embedded in version 8.3. * **NoSQL:** Native support for JSON, JSONB, XML, HStore, and foreign data wrappers to NoSQL databases. * **Data Warehouse:** Can be smoothly migrated to GreenPlum, DeepGreen, HAWK, etc., which belong to the PostgreSQL ecosystem, using FDW for ETL. * * * ## Related Resources PostgreSQL 10.1 Manual Online Manual: [ Offline Manual - PDF Version: [https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA](https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA), Extraction Code: **xs7r**. Offline Manual - CHM Version: [https://pan.baidu.com/s/1h1J14i8tzJUY3p9yyeW0mA](https://pan.baidu.com/s/1zzBu-Z9unrPew9wl7L_y9w), Extraction Code: **tq4z**. View Latest Version: [https://github.com/postgres-cn/pgdoc-cn/releases](https://github.com/postgres-cn/pgdoc-cn/releases)