PostgreSQL Tutorial

This tutorial is prepared for beginners to help them understand the basic to advanced concepts related to the PostgreSQL database.

PostgreSQL is a free object database.-Relational database server (ORDBMS), distributed under the flexible BSD license.

The PostgreSQL developers pronounce it as post-gress-Q-L.

The slogan of PostgreSQL is "the world's most advanced open-source relational database".

What is a database?

A database (Database) is a repository that organizes, stores, and manages data according to data structures.

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

We can also store data in files, but the speed of 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. So-called relational databases are based on the relational model and use mathematical concepts and methods such as set algebra to process data in the database.

ORDBMS (object-relational database system) is a product of combining object-oriented technology with traditional relational databases. Query processing is an important part of ORDBMS, and its performance will directly affect the performance of DBMS.

ORDBMS adds some new features on the basis of the original relational database.

RDBMS is a relational database management system that establishes relationships between entities, resulting in relational tables.

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

ORDBMS Terms

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

• Database: A database is a collection of related tables.

• Data Table: A table is a matrix of data. In a database, a table looks like a simple electronic spreadsheet.

• Column: A column (data element) contains the same data, for example, postal code data.

• Row:A row (equal to a tuple or record) is a set of related data, such as a user subscription data.

• Redundancy: Storing double data, redundancy reduces performance but improves 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 data.

• Foreign Key:Foreign keys are used to associate two tables.

• Composite Key: A composite key (combined key) uses multiple columns as an index key and is generally used for composite indexes.

• Index:Indexing allows for quick access to specific information within a 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.

• Reference integrity: Reference integrity requires that no reference be made to non-existent entities. Reference integrity is a necessary integrity constraint condition that the relational model must meet, with the purpose of ensuring data consistency.

PostgreSQL Features

• Functions：Instructions can be executed on the database server side through functions.

• Indexes：Users can customize index methods or use built-in B-tree, hash table, and GiST index.

• Triggers：Triggers are events triggered by SQL statements. 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 the multi-version concurrency control (MVCC, Multiversion concurrency control) system for concurrency control, which provides each user with a "snapshot" of the database, and each modification made by the 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 (INSERT), UPDATE (UPDATE), DELETE (DELETE).

• Data types：Including text, arbitrary precision numeric arrays, JSON data, enumeration types, XML data, etc.

• Full-text search：Through Tsearch2 or OpenFTS,8.3Version embedded Tsearch2.

• 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. within the PostgreSQL ecosystem, and use FDW for ETL.

PostgreSQL is a powerful open-source object-relational database system. It has more than15years of active development experience and mature architecture, winning an extremely high reputation in terms of reliability, data integrity, and correctness. PostgreSQL can run on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64）and Windows. This tutorial will provide you with a quick start to PostgreSQL and make you satisfied with PostgreSQL programming.