PostgreSQL tutorial

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

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

PostgreSQL's Slogan is "the world's most advanced open source relational database."

What is a database?

A database is a repository that organizes, stores, and manages data according to its data structure.

Each database has one or more different APIs for creating, accessing, managing, searching, and copying saved data.

We can also store data in a file, but reading and writing data in a file 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 is a database based on the relational model, and the data in the database is processed by means of mathematical concepts and methods such as set algebra.

ORDBMS (Object Relational Database System) is the product of object-oriented technology combined with traditional relational database. Query processing is an important part of ORDBMS, and its performance will directly affect the performance of DBMS.

ORDBMS adds some new features to the original relational database.

RDBMS is a relational database management system that establishes the relationship between entities and finally gets a relational table.

OODBMS object-oriented database management system, all entities are looked at the object, and these object classes are encapsulated, the communication between the objects through the message OODBMS object relational database is essentially a relational database.

ORDBMS terminology

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

  • 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.
  • Columns: A column (data element) contains the same data, such as zip code data.
  • Line: A line (=tuple, or record) is a set of related data, such as data subscribed by a user.
  • Redundancy: Stores twice as much data, redundancy reduces performance, but increases data security.
  • Primary key: The primary key is unique. A data table can only contain one primary key. You can use the primary key to query the data.
  • Foreign key: The foreign key is used to associate two tables.
  • Composite keys: Composite keys (combination keys) use multiple columns as an index key, which is typically used for composite indexes.
  • Index: 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 directory similar to a book.
  • Reference Integrity: Referential integrity requirements do not allow references to entities that do not exist. Entity integrity is an integrity constraint that must be met by the relational model to ensure data consistency.

PostgreSQL features

  • Function: The function program can be used to execute the instruction program on the database server side.

  • Index: Users can customize the indexing method or use the built-in B-tree, hash table and GiST index.

  • Trigger: A trigger is an event triggered by a SQL statement query. For example, an INSERT statement may trigger a trigger to check data integrity. Triggers are usually triggered by an INSERT or UPDATE statement. Multi-version concurrency control: PostgreSQL uses the Multiversion concurrency control (MVCC) system for concurrency control. The system provides each user with a "snapshot" of the database, each modification made by the user within the transaction, for other The user is not visible until the transaction is successfully submitted.

  • Rules: Rules (RULE) allow a query to be overridden, usually used to implement operations on views (VIEW), such as inserts (INSERT), updates (UPDATE), deletes (DELETE). ).

  • Data types: Includes text, numeric arrays of arbitrary precision, JSON data, enumerated types, XML data

    , and more.
  • Full-text search: Tsearch2 is embedded in version 8.3 via Tsearch2 or OpenFTS.

  • NoSQL: JSON, JSONB, XML, HStore native support, external data wrapper to NoSQL database.

  • Data Warehousing: Smooth migration to GreenPlum, DeepGreen, HAWK, etc. in the same PostgreSQL ecosystem, using FDW for ETL.