PostgreSQL Learning (I.)

PostgreSQL Learning (I.)

1. The special place of PostgreSQL and the reason of choosing

(1) PostgreSQL supports writing process and functions in various programming languages

(2) PostgreSQL supports very powerful user-definable data type features

2. PostgreSQL Resources

(1) Plantet PostgreSQL (http://planet.postgresql.org) is the aggregation site for PostgreSQL technology blog posts, which includes from PostgreSQL core developers to regular users

A variety of articles, including new features and instructions for using existing features

(2) PostgreSQL Wiki (https://wiki.postgresql.org) provides instructions on how to use all aspects of PostgreSQL and how to move values from other databases to PostgreSQL

(3) PostgreSQL Books (https://www.postgresql.org/docs/books/) provides bookcase list information about PostgreSQL;

3. PostgreSQL Management Tools

There are four types of PostgreSQL common management tools: Psql, Pgadmin,phppgadmin, and Adminer. PostgreSQL's core development team maintains the top three types.

(1) Psql is a command-line tool for executing queries

(2) Pgadmin is a widely used open source PostgreSQL GUI management tool

(3) Phppgadmin Web page-based management tools

4. PostgreSQL Default Listening Port 5432

5. PostgreSQL database Objects

(1) Service

On most operating systems, PostgreSQL is installed as a service (or daemon). Multiple PostgreSQL services can run on the same physical server, but their listening ports cannot

Duplicate, and cannot share a data store directory.

(2) database

Each PostgreSQL service can contain multiple independent database

(3) schema

The next logical structure of database is schema

(4) Catalog

Catalog is a system-level schema for storing system functions and system metadata. When each database is created, the default will contain two catalog:

A named Pg_catalog that stores the functions, tables, system views, data type converters, and data type definitions that the PostgreSQL system comes with as metadata

The other is information_schema, which stores the metadata query views required in the ANSI standard, which conform to the requirements of the ANSI SQL standard and provide PostgreSQL in a formatted format to the outside world.

Meta-data information

The most commonly used views in PostgreSQL Information_schema are the following:

Columns View: Lists all the table columns in the database

Tables view: List all tables in the database (including views)

View view: Lists all views and the associated SQL used to build or rebuild the view

(4) Variables

(5) Expansion pack

(6) Table

In PostgreSQL, the table first belongs to a schema, and the schema belongs to a database, which makes up a class three storage structure

The PostgreSQL table supports two powerful functions,

The first is table inheritance, where a table can have parent and child tables

The second is to create a table, and the system automatically creates a corresponding custom data type for this table

(7) External table and external data wrapper

External tables allow you to access data from external data sources directly in the local database

The external Table mapping relationship is established by configuring the external data wrapper (Foreign data WRAPPER,FDW). The FDW is a magic bridge between PostgreSQL and an external data source that enables both sides

Interoperability of data.

(8) Table space

Tablespace is the physical space used to store data

(9) View

(10) function

(11) built-in programming language

(12) operator

(13) Data type

(14) Data type converter

(15) Sequence

(16) Line or record

(17) Trigger

(18) Rules

PostgreSQL Learning (i)