PostgreSQL

PostgreSQL 是一个功能强大的开源对象关系数据库系统，经过超过 35 年的积极开发，在可靠性、功能稳健性和性能方面赢得了良好的声誉。

通过官方文档 可以找到大量描述如何安装和使用PostgreSQL 的信息。开源社区社区提供了许多有用的 地方来熟悉 PostgreSQL、发现它的工作原理和寻找职业机会。详细了解如何与社区互动。

安装

# CentOS8 关闭内置的 PostgreSQL 模块
dnf -qy module disable postgresql

# 安装repo源 https://yum.postgresql.org/repopackages/
#EL-8-x86_64
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#EL-7-x86_64
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#EL-6-x86_64
https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum安装
yum install postgresql90-server
yum install postgresql12-server

初始化

# 创建一个Postgresql的数据目录
postgresql_data_dir=/data/postgresql
sudo mkdir ${postgresql_data_dir}
#放在本地文件夹，不要放在远程文件夹上，创建表空间指向远程文件夹

# 为这个目录指定所有者同时分配权限
sudo chown postgres:postgres ${postgresql_data_dir}
sudo chmod 750 ${postgresql_data_dir}

# 修改为新的数据路径
vi /usr/lib/systemd/system/postgresql-*.service
#Environment=PGDATA=/var/lib/pgsql/xx/data/
sed -i 's#^Environment=PGDATA=.*#Environment=PGDATA='${postgresql_data_dir}'#' /usr/lib/systemd/system/postgresql-*.service

#(以init脚本启动的)
vi /etc/init.d/postgresql-
PGDATA=/data/postgresql

# 初始化数据库
/usr/pgsql-*/bin/postgresql*-setup initdb

# 修改配置

vi ${postgresql_data_dir}/postgresql.conf
#允许监听所有主机发出的连接请求
listen_addresses = '*'

vi ${postgresql_data_dir}/pg_hba.conf
#允许远程连接
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0                trust

启动

systemctl enable postgresql-9.6.service
systemctl restart postgresql-9.6.service
systemctl status postgresql-9.6.service

sudo chkconfig postgresql-9.6 on
service postgresql-9.6 restart
service postgresql-9.5 status

systemctl enable postgresql-12.service
systemctl restart postgresql-12.service
systemctl status postgresql-12.service

sudo chkconfig postgresql-12 on
service postgresql-12 restart
service postgresql-9.5 status

pg_ctl

# 手动启动pgsql
sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl start -D /data/postgresql
sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl reload -D /data/postgresql
sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl stop -D /data/postgresql

测试

[root@localhost ~]# psql -h 127.0.0.1 -U postgres
psql (9.6.19)
Type "help" for help.

postgres=#

设置密码

ALTER USER postgres WITH PASSWORD 'xxx';

Windows安装

初始化数据库
initdb.exe -D D:\pgsql

启动数据库
pg_ctl -D ^"D^:^\pgsql^" -l pgsql.log start

注册为系统服务
pg_ctl register -N PostgreSQL -D D:\pgsql

卸载系统
pg_ctl unregister -N PostgreSQL

启动服务
net start PostgreSQL

常用命令

psql -h <hostname> -p <port:5432> -U <username> -W <dbname>
psql -h 127.0.0.1 -U postgres
psql -U postgres

常用数据库命令（mysql为MySQL数据库操作命令，psql为postgresql数据库命令）
1列出所有的数据库
mysql: show databases
psql: \l或\list

2切换数据库
mysql: use dbname
psql: \c dbname

3列出当前数据库下的所有表
mysql: show tables
psql: \d

4列出指定表的所有字段
mysql: show columns from table name
psql: \d tablename

5查看表的基本情况
mysql: describe tablename
psql: \d+ tablename

\q to quit（退出登录）

列出所有表空间
psql: \db
postgres=# \db
               List of tablespaces
    Name    |  Owner   |        Location
------------+----------+-------------------------
 data1      | postgres | /data1/postgresql_data1
 data2      | postgres | /data2/postgresql_data2
 data3      | postgres | /data3/postgresql_data3
 pg_default | postgres |
 pg_global  | postgres |
(5 rows)

postgres=#

PostgreSQL语句

1、插入数据（insert 语句）

语法：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

实例：

INSERT INTO employees(  ID, NAME, AGE, ADDRESS, SALARY)
VALUES
(1, 'Maxsu', 25, '海口市人民大道2880号', 109990.00 ),
(2, 'minsu', 25, '广州中山大道 ', 125000.00 ),
(3, '李洋', 21, '北京市朝阳区', 185000.00),
(4, 'Manisha', 24, 'Mumbai', 65000.00),
(5, 'Larry', 21, 'Paris', 85000.00);

2、查询数据（SELECT语句）

语法：

SELECT "column1", "column2"..."columnN" FROM "table_name";
SELECT * FROM "table_name";

3、更新数据（UPDATE语句）

语法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

4、删除数据（DELETE语句）

语法：

DELETE FROM table_name
WHERE [condition];

　实例：

DELETE FROM EMPLOYEES
WHERE ID = 1;

5、ORDER BY子句

语法：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

　实例：

SELECT *
FROM EMPLOYEES
ORDER BY AGE ASC;

6、分组（GROUP BY子句）

语法：

SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

实例：

SELECT NAME, SUM(SALARY)
FROM EMPLOYEES
GROUP BY NAME;

　在上面的例子中，当我们使用GROUP BY NAME时，重复的名字数据记录被合并。 它指定GROUP BY减少冗余。

7、Having子句

　　语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

　实例：

　　执行以下查询表“EMPLOYEES”中name字段值计数大于1的名称。

SELECT NAME,COUNT (NAME)
FROM EMPLOYEES
GROUP BY NAME HAVING COUNT (NAME) > 1;

8、条件查询

　　条件查询有：

• • AND 条件
  • OR 条件
  • AND & OR 条件
  • NOT 条件
  • LIKE 条件
  • IN 条件
  • NOT IN 条件
  • BETWEEN 条件

　　1）AND条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]
AND [search_condition];

　　　　实例：

SELECT *
FROM EMPLOYEES
WHERE SALARY > 120000
AND ID <= 4;

　　2）OR条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]
OR [search_condition];

　　　实例：

SELECT *
FROM  EMPLOYEES
WHERE NAME = 'Minsu'
OR ADDRESS = 'Noida';

　　3）AND & OR条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]  AND [search_condition]
OR [search_condition];

　　　　实例：

SELECT *
FROM EMPLOYEES
WHERE (NAME = 'Minsu' AND ADDRESS = 'Delhi')
OR (ID>= 8);

　　4）NOT条件

　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] NOT [condition];

　　　　实例：

　　　　查询那些地址不为 NULL 的记录信息，执行以下查询：

SELECT *
FROM EMPLOYEES
WHERE address IS NOT NULL ;

　　　查询那些年龄不是21和24的所有记录，执行以下查询：

SELECT *
FROM EMPLOYEES
WHERE age NOT IN(21,24) ;

　　5）LIKE条件

　　　　like 与 where 子句一起，用于从指定条件满足 like 条件的表中获取数据。

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] LIKE [condition];

　　　　实例：

　　　　查询名字以 Ma 开头的数据记录，如下查询语句：

SELECT *
FROM EMPLOYEES
WHERE NAME LIKE 'Ma%';

　　　执行结果如下图：

　　　　查询名字以su结尾的数据记录，如下查询语句：

SELECT *
FROM EMPLOYEES
WHERE NAME LIKE '%su';

　6）IN条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] IN [condition];

　　　实例：

　　　　查询employee表中那些年龄为19，21的员工信息，执行以下查询：

SELECT *
FROM EMPLOYEES
WHERE AGE IN (19, 21);

　7）NOT IN条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] NOT IN [condition];

　　####8）BETWEEN条件

　　　　语法：

SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] BETWEEN [condition];

　　　　实例：

SELECT *
FROM EMPLOYEES
WHERE AGE BETWEEN 24 AND 27;

分类: PostgreSQL

指定新的表空间

postgresql跟oracle有点类似，新建表的时候可以指定表空间，对应的数据就存在这个表空间里面。
可以新建表空间指向新加的磁盘，然后把默认的表空间执行新的表空间 这样后面新增的表和数据就可以存在新磁盘里，这样一来就缓解了磁盘压力。

1-3、增加磁盘、格式化、挂载目录、目录授权

  chown postgres /data2
  chmod 777 /data2

4、创建表空间

# 切换postgres用户(注意'-',不加'-'shell环境不切换,报错)
[root@xxx]# su - postgres
# 进入psql交互模式
[root@xxx]# psql
# 创建新表空间 ts_gps_new
[root@xxx]#psql> CREATE TABLESPACE "ts_gps_new" location '/data2';
# 修改默认表空间 (注意:只对此会话有效!!!)
[root@xxx]#psql> set default_tablespace = ts_gps_new;
# 查看表空间
[root@xxx]#psql> \db
           List of tablespaces
    Name    |  Owner   |    Location
------------+----------+-----------------
 pg_default | postgres |
 pg_global  | postgres |
 ts_gps_new | postgres | /data2

5、修改新创建的表的表空间

# 新建表
[root@xxx]#psql> CREATE TABLE XXX(int id...) tablespace ts_gps_new;
# 设置了default_tablespace可以省略后面的(再次提醒:default_tablespace只对当前会话有效!!!)
[root@xxx]#psql> CREATE TABLE XXX(int id...)
# 查看表所在的表空间
[root@xxx]#psql> select pg_relation_filepath('XXX')

6、修改已有表的表空间

[root@xxx]#psql> alter table mt_xxx_2019 set tablespace ts_gps_new;

原来表空间里面还有文件，但是为空：

可以将一个表空间内表一起迁移到新表空间：

ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE new_disk;

Chapter 8. 数据类型

PostgreSQL 有着丰富的数据类型可用。 用户可以使用 CREATE TYPE 命令为 PostgreSQL 增加新的数据类型。

Table 8-1 显示了所有内置的普通数据类型。 在"别名"列里列出的大多数可选名字都是因历史原因 PostgreSQL 在内部使用的名字。 另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。

Table 8-1. 数据类型

兼容性: 下列类型（或者那样拼写的）是SQL声明的： bit，bit varying，boolean， char，character，character varying，varchar，date， double precision，integer， interval，numeric，decimal， real，smallint，time （包括有时区和无时区的）， timestamp （包括有时区和无时区的）。

每种数据类型都有一个由其输入和输出函数决定的外部表现形式。 许多内建的类型有明显的格式。不过，许多类型要么是 PostgreSQL 所特有的，比如几何路径，要么可能是有几种不同的格式，比如日期和时间类型。 有些输入和输出函数是不可逆的。也就是说，输出函数的输出结果和原始的输入比较的时候可能丢失精度。

8.1. 数值类型

数值类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。 Table 8-2 列出了所有可用类型。

Table 8-2. 数值类型

数值类型常量的语法在 Section 4.1.2 里描述。 数值类型对应有一套完整的数学操作符和函数。相关信息请参考 Chapter 9。 下面的几节详细描述这些类型。

8.1.1. 整数类型

类型 smallint，integer，和 bigint 存储各种范围的全部是数字的数，也就是没有小数部分的数字。 试图存储超出范围以外的数值将导致一个错误。

常用的类型是 integer，因为它提供了在范围，存储空间， 和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint。而只有在 integer 的范围不够的时候才使用 bigint，因为前者绝对快得多。

bigint 类型可能不是在所有平台上都运转正确， 因为它依赖编译器对八字节整数的支持。在那些没有这样支持的机器上， bigint 的作用和 integer 一样（但是仍然占据八字节存储）。不过，我们还不知道任何有这样的情况的平台。

SQL只声明了整数类型 integer(或int)和 smallint。类型 bigint，和类型名 int2，int4，和 int8 都是扩展， 也在许多其它 SQL 数据库系统中使用。

8.1.2. 任意精度数值

类型 numeric 可以存储最多1000位精度的数字并且准确地进行计算。 我们特别建议将它用于货币金额和其它要求计算准确的数量。不过，numeric 类型上的算术运算比整数类型或者我们下一节描述的浮点数类型要慢很多。

在随后的内容里，我们使用了下述术语： 一个 numeric 的比例是到小数点右边为止小数部分的位数， numeric 的精度是整个数字里全部数据位的数目，也就是小数点两边的数据数目。 因此数字 23.5141 的精度为6而比例为4。你可以认为整数的比例为零。

numeric 字段的最大精度和最大比例都是可以配置的。要声明一个类型为 numeric 的字段，你可以用下面的语法

NUMERIC(precision, scale)

精度必须为正数，比例可以为零或者正数。 另外，

NUMERIC(precision)

选择了 0 为比例。不带任何精度或者比例声明

NUMERIC

则创建一个可以存储一个直到实现精度上限的任意精度和比例的数值， 一个这样类型的字段将不会把输入数值转化成任何特定的比例， 而带有比例声明的 numeric 字段将把输入值转化为该比例。 （SQL标准要求缺省的比例是 0。也就是转化成整数精度。 我们觉得这样做有点没用。如果你关心移植性，那你最好总是明确声明精度和比例。）

如果一个要存储的数值的比例比字段声明的比例高， 那么系统将尝试圆整（四舍五入）该数值到指定的小数位。 然后，如果小数点左边的数据位数超过了声明的精度减去声明的比例， 那么抛出一个错误。

数值数据值物理上是不带任何前导或者后缀零的形式存储的。 因此，字段上声明的精度和比例都是最大值，而不是固定分配的。 （在这个方面，numeric 类型更类似于 varchar(*n*)， 而不像 char(*n*)。） 实际存储是每四个十进制位两个字节，然后在整个数据上加上八个字节的额外开销。

除了普通的数字值之外，numeric 类型允许特殊值 NaN， 表示"不是一个数字"。任何在 NaN 上面的操作都生成另外一个 NaN。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，比如 UPDATE table SET x = 'NaN'。在输入时，字串 NaN 当作大小写无关看待。

类型 decimal 和 numeric 是等效的。 两种类型都是SQL标准。

8.1.3. 浮点数类型

数据类型 real 和 double precision 是不准确的，变精度的数字类型。 实际上，这些类型是 IEEE 标准 754 二进制浮点数算术（分别对应单和双精度）的一般实现， 外加下层处理器，操作系统和编译器对它的支持。

不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储然后把数据再打印出来可能显示一些缺失。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点：

• 如果你要求准确的计算（比如计算货币金额），应使用 numeric 类型。
• 如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况（无穷，下溢）严重依赖的事情，那你应该仔细评诂你的实现。
• 拿两个浮点数值进行相等性比较可能象，也可能不象想像那样运转。

通常，real 类型的范围是至少 -1E+37 到 +1E+37， 精度至少是 6 位小数。double precision 类型通常有 -1E+308 到 +1E+308 的范围，精度是至少 15 位数字。太大或者太小的数值都会导致错误。 如果输入数据太高，那么可能发生园整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错。

除了普通的数字值之外，浮点类型还有几个特殊值：

Infinity`
`-Infinity`
`NaN

这些值分别表示 IEEE 754 特殊值 "正无穷大"， "负无穷大"， 以及 "不是一个数字"。（在不遵循 IEEE 754 浮点算术的机器上，这些值的含义可能不是预期的。） 如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号， 像这样 UPDATE table SET x = 'Infinity'。 输入时，这些值是以大小写无关的方式识别的。

PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于声明非精确的数值类型。 在这里，*p* 声明以二进制位表示的最低可接受精度。 在选取 real 类型的时候，PostgreSQL 接受 float(1) 到 float(24)，在选取 double precision 的时候，接受 float(25) 到 float(53)。在允许范围之外的 *p* 值将导致一个错误。 没有声明精度的 float 将被当作是 double precision。

注意: 在 PostgreSQL 7.4 以前，在 float(p) 里面的精度会被当作是这么多位数的十进制位。到 7.4 已经被修改成与 SQL 标准匹配，标准声明这个精度是以二进制位度量的。假设 real 和 double precision 分别有 24 和 53 个二进制位的位数对 IEEE 标准的浮点实现来说是正确的。 在非 IEEE 平台上，这个数值可能略有偏差，但是为了简化，我们在所有平台上都用了同样的 *p* 值范围。

8.1.4. Serial（序号）类型

serial 和 bigserial 类型不是真正的类型， 只是为在表中设置唯一标识做的概念上的便利。（类似其它一些数据库中的 AUTO_INCREMENT 属性）。 在目前的实现中，下面一句话：

CREATE TABLE tablename (
    colname SERIAL
);

等价于声明下面几句话：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

因此，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。 应用了一个 NOT NULL 约束以确保空值不会被明确地插入。 在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动发生的。

注意: 在 PostgreSQL 7.3 以前，serial 隐含 UNIQUE。但现在不再如此。 如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

要使用 serial 字段插入序列的下一个数值到表中， 主要是要注意 serial 应该赋予缺省值。 我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现， 也可以通过使用 DEFAULT 关键字来实现。

类型名 serial 和 serial4 是等效的： 两个都创建 integer 字段。类型名 bigserial 和 serial8 也一样，只不过它创建一个 bigint 字段。 如果你预计在表的生存期中使用的标识数目超过 231 个，那么你应该使用 bigserial。

一个 serial 类型创建的序列在所属的字段被删除的时候自动删除，其它情况下是不会被删除的。 （这一点在 PostgreSQL 版本 7.3 之前可不是真的。请注意，这种自动删除的关联在通过重载 7.3 以前的数据库转储的时候可不会自动发生； 那样的转储文件不包含需要建立这种关联关系的信息。） 另外，这样的序列和字段之间的依赖性只在 serial 字段本身上有； 如果任何其它字段引用了序列（可能是手工调用 nextval 函数）， 那么，如果这个序列被删除了，它们就会被破坏。我们认为这样使用 serial 字段是一种不好的形式；如果你想用同一个序列发生器给几个字段喂数据，那么还是把序列发生器作为独立对象创建。