PostgreSQL course

PostgreSQL Is a free object - Relational database server (ORDBMS), In a flexible BSD Issued under license .

PostgreSQL Developers read it as post-gress-Q-L.

PostgreSQL of Slogan yes " The most advanced open source relational database in the world ".

References :PostgreSQL 10.1 Manual

What is a database ?

database (Database) It is organized according to the data structure 、 A warehouse for storing and managing data .

Each database has one or more different API Used to create , visit , Administration , Search and copy saved data .

We can also store data in files , But the speed of reading and writing data in files is relatively slow .

So , Now we use a relational database management system (RDBMS) To store and manage large amounts of data . The so-called relational database , It is a database based on relational model , With the help of mathematical concepts and methods such as set algebra to process the data in the database .

ORDBMS( Object relational database system ) It is the product of the combination of object-oriented technology and traditional relational database , Query processing is ORDBMS An important part of , Its performance will directly affect DBMS Performance .

ORDBMS Based on the original relational database , Added some new features .

RDBMS Is a relational database management system , Is to establish connections between entities , The final result is a relational table .

OODBMS Object oriented database management system , Look at all entities as objects , And encapsulate these object classes , Objects communicate through messages OODBMS Object relational database is still relational database in essence .

ORDBMS The term

Before we start learning PostgreSQL Before the database , Let's first understand ORDBMS Some terms of :

  • database : A database is a collection of associated tables .
  • data sheet : A table is a matrix of data . A table in a database looks like a simple spreadsheet .
  • column : A column ( Data elements ) Contains the same data , For example, zip code data .
  • that 's ok : a line (= Tuples , Or record ) Is a set of related data , For example, a piece of data subscribed by a user .
  • Redundancy : Store twice as much data , Redundancy reduces performance , But it improves the security of data .
  • Primary key : The primary key is unique . A data table can only contain one primary key . You can use the primary key to query data .
  • Foreign keys : Foreign keys are used to associate two tables .
  • Compound bond : Compound bond ( Key combination ) Use multiple columns as an index key , Generally used for composite indexes .
  • Indexes : Use indexes to 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 . A catalogue similar to a Book .
  • Referential integrity : Referential integrity requires that nonexistent entities cannot be referenced in a relationship . And entity integrity are the integrity constraints that the relational model must meet , The purpose is to ensure the consistency of data .

PostgreSQL features

  • function : By function , The instruction program can be executed on the database server .

  • Indexes : You can customize the index method , Or use the built-in B Trees , Hash table and GiST Indexes .

  • Trigger : The trigger is composed of SQL Event triggered by query statement . as : One INSERT Statement may trigger a trigger to check data integrity . Triggers are usually composed of INSERT Or UPDATE Statement triggers . Multi version concurrency control :PostgreSQL Use multi version concurrency control (MVCC,Multiversion concurrency control) The system performs concurrency control , The system provides a database for each user " Snapshot ", Every modification made by the user within the transaction , Not visible to other users , Until the transaction is successfully committed .

  • rule : rule (RULE) Allow a query to be rewritten , It is usually used to implement the view (VIEW) The operation of , For example, insert (INSERT)、 to update (UPDATE)、 Delete (DELETE).

  • Data type : Include text 、 An array of numeric values of arbitrary precision 、JSON data 、 Enumeration type 、XML data

    etc. .
  • Full text search : Through Tsearch2 Or OpenFTS,8.3 Embedded in version Tsearch2.

  • NoSQL:JSON,JSONB,XML,HStore Native support , to NoSQL External data wrapper for database .

  • Data warehouse : It can smoothly migrate to the same genus PostgreSQL ecological GreenPlum,DeepGreen,HAWK etc. , use FDW conduct ETL.

Related resources

PostgreSQL 10.1 Manual Online manual :

Offline manual - PDF edition :, Extraction code : xs7r.

Offline manual - CHM edition :, Extraction code : tq4z.

View the latest version :