PostgreSQL，通常被称为Postgres，是一个功能强大的开源对象-关系型数据库管理系统（ORDBMS），它使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。

PostgreSQL的起源可以追溯到1986年，作为加州大学伯克利分校POSTGRES项目的一部分，并且在核心平台上进行了30多年的积极开发。它支持大量的数据类型，包括文本、图像、视频、声音等，并提供了丰富的SQL功能，如复杂的SQL查询、子查询、外键、触发器、视图以及多进程并发控制（MVCC）等。

此外，PostgreSQL也是一个高度可扩展的数据库系统，允许开发人员根据需要进行定制和扩展。它的事务处理功能也非常强大，支持ACID属性，保证了数据的完整性和一致性。同时，PostgreSQL还支持多种操作系统，包括WINDOWS、Linux、Solaris、macOS和BSD等。

• 官方网址：PostgreSQL: The world's most advanced open source database

• 菜鸟教程：PostgreSQL 教程 | 菜鸟教程 (runoob.com)

安装

Linux

首先输入如下命令安装

sudo apt install postgresql postgresql-client

安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空。输入如下命令进入超级用户

sudo -i -u postgres

进入后，输入命令psql，若看到如下信息，则证明安装成功

$ psql
psql (15.1)
Type "help" for help.

postgres=#

进出postgreSQL

进入超级用户postgres

sudo -i -u postgres

进入psql

在超级用户条件下输入：

psql
psql -h hostname -p port -U username -d dbname # 指定host、端口、用户名和数据库，若有密码则会在按enter后需要你输入密码才能成功进入

退出psql

\q

数据类型

数值类型

整数类型：

• SMALLINT：2字节，存储范围-32768至+32767，用于存储小范围的整数。

• INTEGER（或INT）：4字节，存储范围-2147483648至+2147483647，是最常用的整数类型，提供了范围、存储空间和性能之间的平衡。

• BIGINT：8字节，存储范围-9223372036854775808至9223372036854775807，用于存储大范围的整数。

任意精度数值：

• NUMERIC（或DECIMAL）：用户指定的精度，精确计算，适用于货币金额和其他需要精确计算的场景。缺点是算术运算比整数或浮点数慢。

浮点数类型：

• REAL：4字节，6位十进制数字精度，是不准确的、牺牲精度的数字类型，用于存储近似值。

• DOUBLE PRECISION：8字节，15位十进制数字精度，也是不准确的、牺牲精度的数字类型，但比REAL更精确。

字符类型

• CHAR(n)：定长字符串，n是要存储的字符数，不足部分用空格填充。

• VARCHAR(n)（或CHARACTER VARYING(n)）：变长字符串，n是最大存储的字符数，只占用实际需要的空间。

• TEXT：无限长的字符串，用于存储大量的文本数据。

日期/时间类型

• DATE：仅存储日期（年、月、日），用于表示特定的日期。

• TIME：仅存储时间（时、分、秒，可带时区），用于表示特定的时间。

• TIMESTAMP：存储日期和时间（可带时区），用于表示特定的日期和时间点。

• INTERVAL：存储时间间隔，用于表示时间段的长度。

布尔类型

• BOOLEAN：存储逻辑布尔值（真/假），用于表示逻辑条件或开关状态。

货币类型

• MONEY：8字节，用于存储货币金额。范围从-92233720368547758.08至+92233720368547758.07。此类型常用于财务和金融应用中，用于处理货币值。

UUID类型

• UUID：16字节，用于存储通用唯一标识符（UUID）。UUID是一个128位的数字，通常由32个十六进制数字表示。此类型常用于需要全局唯一标识符的应用中。

几何类型

1. 点（Point）：

   • 基础类型，用于表示二维空间中的一个具体位置，由一对坐标值（X, Y）定义。

   • 在PostGIS中，点是最基本的几何对象，其他类型通常由点构成。

2. 线（Linestring）：

   • 由两个或更多个点顺序连接而成，表示一条连续的线段。

   • 线段可以是直线，也可以是折线，取决于连接点的位置。

3. 多边形（Polygon）：

   • 由一条或多条闭合的线段组成，表示二维平面上的一个封闭区域。

   • 多边形可以具有一个外部边界和零个或多个内部边界（代表孔洞）。

4. 多点（Multipoint）：

   • 包含零个或多个点的集合类型。

   • 通常用于表示一组不连续的点。

5. 多线（Multilinestring）：

   • 包含零条或多条线段的集合类型。

   • 用于表示一组不相交或相交的线段集合。

6. 多多边形（MultiPolygon）：

   • 包含零个或多个多边形的集合类型。

   • 用于表示一组可能相交或分离的多边形集合。

7. 几何集合（GeometryCollection）：

   • 一种容器类型，可以包含任意数量和类型的几何对象（如点、线、多边形等）。

   • 几何集合本身不具有几何意义，只是作为一种组合多个几何对象的方式。

8. 圆（Circle）：

   • 在标准PostGIS中，并没有直接的“圆”类型。但是，圆可以用点和半径来定义，或者通过多边形来近似表示。

   • 用户可以创建一个足够多的边的多边形来近似表示一个圆。

PostGIS提供的这些几何类型支持各种空间查询和操作，如计算距离、面积、判断空间关系（如相交、包含、相邻等）以及进行空间变换等。这些功能使得PostgreSQL成为处理地理信息和空间数据的强大工具。

位串类型

• BIT(n)：固定长度的位串，其中n是指定位数。超出指定长度的位将被截断，不足的部分将用0填充。

• BIT VARYING(n)（或VARBIT(n)）：可变长度的位串，其中n是最大位数。实际存储的位数可以根据需要变化。

网络地址类型

• INET：用于存储IPv4或IPv6地址。没有固定的位数，因为它可以存储不同长度的IP地址。常用于网络应用和系统管理中，用于存储和操作IP地址。

• CIDR：类似于INET，但用于存储IP地址范围（无类别域间路由）。也没有固定的位数。

• MACADDR：用于存储MAC地址（物理地址）。通常是6字节长，用于网络设备管理和通信。

文本搜索类型

• TSVECTOR：用于全文搜索的文本向量类型。它将文本分解成词干，并存储为向量，以便进行高效的文本搜索。

• TSQUERY：用于表示文本搜索查询的类型。它允许用户构建复杂的查询表达式，以在TSVECTOR列中搜索匹配的文档。

JSON类型

• JSON：用于存储JSON（JavaScript Object Notation）格式的数据。没有固定的位数，因为它可以存储不同大小和复杂度的JSON对象。常用于存储和查询JSON格式的数据。

• JSONB：与JSON类似，但以二进制格式存储，支持索引。这使得查询和处理更加高效。

数组类型

• PostgreSQL允许一列存储同一数据类型的多个值，形成数组。数组的大小可以根据需要动态增长。例如，INT[]表示整数数组，可以存储任意数量的整数。

数据库操作

查看数据库

再查看postgre有多少数据库，我们可以输入如下命令查看：

\l
\list

进入数据库

我们可以使用如下命令进入数据库：

\c db_name

创建数据库

使用如下SQL创建数据库

CREATE DATABASE db_name

也可以使用createdb命令，该命令是PostgreSQL安装时提供的命令行工具，使用该命令时，需要退出psql窗口。使用该命令时，需要退出psql窗口。createdb 命令语法格式如下：

createdb [option...] dbname [description]

参数说明：

dbname：要创建的数据库名。

description：关于新创建的数据库相关的说明。

options：参数可选项，可以是以下值：

删除数据库

使用如下SQL删除数据库

DROP DATABASE db_name

也可以使用dropdb命令，dropdb是 DROP DATABASE 的包装器。用于删除 PostgreSQL 数据库。

dropdb命令只能由超级管理员或数据库拥有者执行，使用该命令时，需要退出psql窗口。

以下是 dropdb 命令的基本用法：

dropdb [option...] dbname

其中 dbname 是你想要删除的数据库的名称。

dropdb 命令也接受一些选项，尽管它们不如 createdb 命令那么多。以下是一些可用的选项：

• -e, --echo
  显示 dropdb 生成的并发送给服务器的命令。这主要用于调试。

• -h, --host=HOSTNAME
  指定数据库服务器的主机名。这通常用于远程连接。

• -p, --port=PORT
  指定数据库服务器的端口号。

• -U, --username=USERNAME
  指定连接数据库时要使用的用户名。

• -w, --no-password
  永远不提示输入密码。

• -W, --password
  强制提示输入密码（即使密码在命令行中已指定或在环境变量中设置）。

• --if-exists
  如果指定的数据库不存在，则不输出错误。这是一个在脚本中很有用的选项，因为它可以防止因数据库不存在而导致的脚本失败。

• --help
  显示 dropdb 的帮助信息，包括所有可用选项的列表。

• V, --version
  打印 dropdb 的版本信息并退出。

请注意，dropdb 命令需要数据库超级用户或具有适当权限的用户才能执行。

警告：dropdb 命令会永久删除数据库及其所有数据，且无法恢复。在执行此命令之前，请确保你已经备份了任何重要数据，并且确实希望删除该数据库。

模式（SCHEMA）

在PostgreSQL中，模式（Schema）是一个非常重要的概念，它可以被理解为一个命名空间或目录，类似于操作系统中的目录，但是模式不能嵌套。一个数据库可以包含一个或多个命名的模式，模式中可以包含表、视图、索引、数据类型、函数、以及操作符等命名对象。

模式的主要作用有：

1. 允许多个用户使用一个数据库而不会彼此干扰。

2. 把数据库对象组织成逻辑组，让它们更便于管理。

3. 第三方的应用可以放在不同的模式中，这样它们就不会和其它对象的名字冲突。

在PostgreSQL中，每个新创建的数据库都会有一个默认的Public模式。如果没有明确指定其他的模式，那么在创建数据库对象的时候，默认就会使用Public模式。

注意，模式不支持修改

创建模式

CREATE SCHEMA schema_name;
CREATE SCHEMA schema_name AUTHORIZATION owner_name; # 设置模式的所有者（可选）

查看所有模式

\dn

进入模式

SET search_path TO myschema;

在模式中创建对象（表格）

CREATE TABLE my_new_schema.my_table (  
    id serial PRIMARY KEY,  
    name varchar(100)  
);

删除模式

删除一个为空的模式（其中的所有对象已经被删除）：

DROP SCHEMA myschema; 

删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;

数据表操作

创建数据表

在PostgreSQL中可以使用CREATE TABLE语句来创建表格。以下是一个基本的示例，演示了如何创建一个简单的表格：

CREATE TABLE employees (  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    age INT,  
    address VARCHAR(255),  
    salary DECIMAL(10, 2)  
);

该表格有五个字段：id，name，age，address和salary。它们的意义分别是：

• id字段是一个自增字段（使用SERIAL类型），并设置为主键（PRIMARY KEY）。

• name字段是一个最大长度为100的字符串（VARCHAR(100)），并且不能为空（NOT NULL）。

• age字段是一个整数（INT）。

• address字段是一个最大长度为255的字符串（VARCHAR(255)）。

• salary字段是一个十进制数，总共10位数字，其中2位是小数（DECIMAL(10, 2)）。

约束

在PostgreSQL中，当你创建数据表时，除了PRIMARY KEY之外，还有多种约束（constraints）可以使用，以确保数据的完整性和准确性。以下是一些常用的约束：

1. PRIMARY KEY: 确保某列（或列组合）中的数据唯一，并且不允许NULL值。

CREATE TABLE tablename (  
    column1 datatype PRIMARY KEY,  
    column2 datatype  
);

2. UNIQUE: 确保某列（或列组合）中的数据唯一，但允许一个NULL值（除非使用了B-tree索引，在这种情况下，只能有一个NULL）。

CREATE TABLE tablename (  
    column1 datatype UNIQUE,  
    column2 datatype  
);

3. NOT NULL: 确保某列不能接受NULL值。这意味着在插入或更新数据时，必须为该列提供一个非NULL的值。如果尝试插入或更新一个包含NULL值的记录，并且该列设置了NOT NULL约束，数据库将返回一个错误。

CREATE TABLE tablename (  
    column1 datatype NOT NULL,  
    column2 datatype  
);

4. CHECK: 确保某列中的数据满足指定的条件。

CREATE TABLE tablename (  
    column1 datatype CHECK (column1 > 0),  
    column2 datatype  
);

5. FOREIGN KEY: 用于链接两个表的数据，确保在一个表中的数据匹配另一个表中的值。

CREATE TABLE tablename1 (  
    column1 datatype PRIMARY KEY,  
    column2 datatype  
);  
  
CREATE TABLE tablename2 (  
    columnA datatype,  
    columnB datatype,  
    column_fk datatype REFERENCES tablename1(column1)  
);

6. DEFAULT: 如果没有为某列指定值，则使用默认值。

CREATE TABLE tablename (  
    column1 datatype DEFAULT 'some_value',  
    column2 datatype  
);

7. EXCLUSION: 使用户能够为表定义一个排除约束，该约束保证如果两行在指定的列或表达式上的比较结果为真，则这两行不会同时存在。这通常用于确保范围不重叠等。

CREATE TABLE circles (  
    c circle,  
    EXCLUDE USING gist (c WITH &&)  
);

这些约束可以单独使用，也可以组合使用，以确保数据的完整性和准确性。当插入、更新或删除数据时，PostgreSQL会检查这些约束，以确保数据的完整性。如果数据不满足约束条件，操作将被拒绝，并返回一个错误。

查看表格

\d

修改表格

在PostgreSQL中，如果你想要修改（更改）一个已存在的表格，你可以使用ALTER TABLE命令。这个命令允许你执行多种操作，如添加列、删除列、修改列类型或属性、添加或删除约束等。

添加新列

ALTER TABLE 表名 ADD 列名 数据类型;

ALTER TABLE students ADD age INT;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

ALTER TABLE students DROP COLUMN age;

修改列的数据类型

ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型;

如果你想要更改数据类型并且该列有数据，你可能需要使用USING子句来提供一个转换表达式。

ALTER TABLE students ALTER COLUMN age TYPE TEXT USING age::TEXT;

重命名列

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

ALTER TABLE students RENAME COLUMN age TO student_age;

添加约束（如主键、唯一、检查等）

ALTER TABLE 表名 ADD PRIMARY KEY (列名); # 添加主键约束
ALTER TABLE 表名 ADD UNIQUE (列名); # 添加唯一约束
ALTER TABLE 表名 ADD CHECK (条件); # 添加检查约束

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

重命名表格

ALTER TABLE 旧表名 RENAME TO 新表名;

删除表格

DROP TABLE table_name;