postgres 的使用
postgres的安装参考：在Centos7上安装PostgreSQL16的详细步骤_centos7安装postgresql16-CSDN博客
postgres的常用命令：
查看所有库： \l
进入库 ：\c
查看所有表：\d 库名;
查看表结构：\d 表名;
查看所有用户：\du
显示当前库下schema信息： \dn
postgres的防火墙配置：
在安装目录的/var/lib/pgsql/data/pg_hba.conf 文件中
创建用户：
在postgres中创建、修改用户使用role，删除用户使用user
-- 创建角色 test1 带超级权限 登录权限 配置密码为 dyh666
create role test1 with SUPERUSER LOGIN password 'dyh666';
-- 普通用户
create role test2 with LOGIN password 'dyh666';
-- 创建复制用户
create role test3 with REPLICATION LOGIN password 'dyh666';
-- 修改用户
alter role test1 with NOSUPERUSER login password 'dyh666';
-- 删除用户
drop user test1;
--修改将属主为 test 的表分配给 test2
reassign owned by test1 to test2;
-- 删除属于 test1 的表
-- 需要手动删除数据库或表空间
drop owned by test1;
权限管理：
授权test1用户对test库拥有create权限
grant create on database test to test1;
schema 权限
-- 创建schema 默认为当前用户
create schema test_schema;
-- 创建schema test2 授权给 test1 用户
create schema test2 authorization test1;
-- 进入schema
set search_path to test_schema;
-- 查看一个database下有几个schema
select * from information_schema.schemata;
-- 将test_schema的拥有者设置为test用户
alter schema test_schema owner to test;
-- 进入test 库
\c test
-- 授权 test 用户 test_chema 下的所有表，增删改查权限
grant select,insert,update,delete on all tables in schema test_schema to test;
-- 删除模式
drop schema test1;
-- 删除模式及其包含的对象
drop schema test2 cascade;
角色权限：
-- 创建角色
create role select_group;
-- 授权 t1 表查询权限 给角色 select_group
grant select on t1 to select_group;
-- 授权 select_group 组给 user_test 用户
grant select_group to user_test;
在库里面执行： set search_path to test_app_schema; 切换到指定schema下面；
postgres的数据类型：
1、数值类型：
| 名字 | 存储长度 | 描述 | 范围 |
| smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
| decimal | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| numeric | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| real | 4 字节 | 可变精度(整数或小数)，不精确 | 6 位十进制数字精度 |
| double precision | 8 字节 | 可变精度，不精确 | 15 位十进制数字精度 |
| smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
| serial | 4 字节 | 自增整数 | 1 到 2147483647 |
| bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |
create table num_test(
real_id real,
int_id integer,
double_dou double precision,
money_dou money,
serial_id serial
);
insert into num_test(real_id,int_id,double_dou,money_dou )values(2,3,11.22,123123.2178);
2、货币类型：money
money类型存储默认两位小数(四舍五入)的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。
| 名字 | 存储容量 | 描述 | 范围 |
| money | 8 字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
3、字符类型：
| 名字 | 描述 |
| varchar(n) 或 character varying(n) | 变长，有长度限制，最大为 10485760（1G） |
| char(n)或 character(n) | 定长，不足补空白，最大为 10485760（1G） |
| text | 变长，无长度限制 |
char(n)和 character(n) 通常是效率最低的，基本上不使用，varchar和text 几乎无差异；
4、日期时间类型：
BC表示公元前、AD表示公元后；
| 名字 | 存储空间 | 描述 | 最低值 | 最高值 | 分辨率 |
| timestamp [ (p) ] [ without time zone ] | 8 字节 | 日期和时间(无时区) | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
| timestamp [ (p) ] with time zone | 8 字节 | 日期和时间(有时区) | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
| data | 4 字节 | 只用于日期 | 4713 BC | 5874897 AD | 1 天 |
| time [ (p) ] [ without time zone ] | 8 字节 | 只用于一日内时间(无时区) | 0:00:00 | 24:00:00 | 1 毫秒 / 14 位 |
| time [ (p) ] with time zone | 12 字节 | 只用于一日内时间(有时区) | 00:00:00+1459 | 24:00:00-1459 | 1 毫秒 / 14 位 |
| interval [ fields ] [ (p) ] | 12 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 毫秒 / 14 位 |
(1)、建表
create table time_test(
timestamp_wiout timestamp without time zone,
timestamp_wi timestamp with time zone,
date_da date,
time_wiout time without time zone,
time_wi time with time zone,
interval_in interval
);
(2)、插入数据：
insert into time_test values(TIMESTAMP '2024-10-15 12:11:11'
,TIMESTAMP '2024-10-15 12:11:11', date '2024-10-15'
,time '12:11:11', time '12:11:11', interval '1 day');
(3)、查询：to_char 将日期\时间转为字符串
select a.*, to_char(timestamp_wiout,'YYYY-MM-DD HH24:MI:SS'),to_char(date_da,'YYYY-MM-DD'), to_char(time_wiout,'HH24:MI:SS')from time_test a;
5、布尔类型：
| 名称 | 存储空间 | 描述 |
| boolean | 1 字节 | true / false |
6、枚举类型：
创建枚举类：CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
create table boen_test(
boolean_b boolean, -- 布尔类型
enum_en mood
);
insert into boen_test values (false,'ok'),(true,'happy'),(null,'sad');
select * from boen_test;
7、几何类型：
| 名字 | 存储空间 | 说明 | 表现形式 |
| point | 16 字节 | 平面中的点 | (x,y) |
| line | 32 字节 | (无穷)直线(未完全实现) | ((x1,y1),(x2,y2)) |
| lseg | 32 字节 | (有限)线段 | ((x1,y1),(x2,y2)) |
| box | 32 字节 | 矩形 | ((x1,y1),(x2,y2)) |
| path | 16+16n 字节 | 闭合路径(与多边形类似) | ((x1,y1),…) |
| path | 16+16n 字节 | 开放路径 | [(x1,y1),…] |
| polygon | 40+16n 字节 | 多边形(与闭合路径相似) | ((x1,y1),…) |
| circle | 24 字节 | 圆 | <(x,y),r> (圆心和半径) |
create table jihe_test(
point_po point,
line_l line,
lseg_l lseg,
box_b box,
path_p path,
polygon_p polygon,
circle_c circle
);
insert into jihe_test values('(1,1)','(1,1), (2,2)','(1,1),(2,2)','(1,1),(2,2)','(1,1),(2,2),(3,3)','(1,1),(2,2),(3,3)','(1,1),1');
8、网络地址类型：
| 名字 | 存储空间 | 描述 |
| cidr | 7 或 19 字节 | IPv4 或 IPv6 网络 |
| inet | 7 或 19 字节 | IPv4 或 IPv6 主机和网络 |
| macaddr | 6 字节 | MAC 地址 |
create table ip_test(
cidr_c cidr,
inet_i inet,
macaddr_m macaddr
);
insert into ip_test values('192.168.100.128/25','192.168.1.1/8','08:00:2b:01:02:03');
插入数据时会校验数据格式，如果格式不对会报错；
9、位串类型：
| 名字 | 描述 | 说明 |
| bit(n) | bit 等效 bit(1) | bit 类型的数据必须准确匹配长度 n，存储短些或者长一些的数据都是错误的 |
| bit varying(n) | bit varying 表示无长度限制 | bit varying 类型数据是最长 n 的变长类型，更长的串会被拒绝 |
create table bit_test (
bit_b bit(1),
bit_v bit varying(10)
);
insert into bit_test values(B'1', B'11111111');
10、文本搜索类型：
| 名字 | 描述 |
| tsvector | tsvector 的值是一个无重复值的 lexemes 排序列表， 即一些同一个词的不同变种的标准化。 |
| tsquery | tsquery 存储用于检索的词汇，并且使用布尔操作符 &(AND)，|(OR)和!(NOT) 来组合它们，括号用来强调操作符的分组。 |
create table text_test(
tsvector_t tsvector,
tsquery_t tsquery
);
---- $$表述双引号，当字符串中存在单引号时使用双引号
insert into text_test values(to_tsvector('the lexeme contains spaces') ,to_tsquery($$fat & (rat | cat)$$)),
(to_tsvector($$the lexeme 'tt' contains spaces$$) ,to_tsquery($$fat & (rat | cat)$$)),
(to_tsvector($$a:1 fat:2 a:6 fat:11$$) ,to_tsquery($$fat & (rat | cat)$$));
---- 条件匹配
select * from text_test
WHERE tsvector_t @@ to_tsquery('the & lexeme')
and tsquery_t @@ to_tsvector('fat cat');
11、UUID 类型：
一组 8 位数字 + 3 组 4 位数字 + 一组 12 位数字 的组合，常用作唯一标识；
如： a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
uuid数据类型的字段插入时会校验数据，不符合规则就报错，通过特定的方法生成，自己写的同样组合的数据也不能插入；
12、XML 类型：
使用 xmlparse 产生 xml 类型的值，postgres默认安装的不支持xml，需要在安装时指定参数：configure --with-libxml(我自己也没有指定这个)，用法如下：
CREATE TABLE xml_table (xml_data XML );
INSERT INTO xml_table (xml_data) VALUES (xmlparse(DOCUMENT '<root><name>John</name></root>'));
SELECT xpath('/root/name/text()', xml_data) FROM xml_table;
13、JSON类型值：
json也可以使用字符串类型类存储，使用json格式可以检查数据的格式，有JSON 和 JSONB两种类型，其中JSONB 使用二进制格式存储数据，更易于处理。
| 实例 | 结果 |
| array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,'foo')) | {“f1”:1,“f2”:“foo”} |
14、数组类型：
数组的类型可以是任意的基本类型，如 integer[]、text[][]等；
15、复合类型：
申明复合类型：create type items as (name text, age integer, address text);
----- JSON、数组、复合类型用法
create table items_table(
json_s jsonb,
text_arr text[],
item_i items
);
insert into items_table values(jsonb_build_object ( 'code', '100015', 'price', 3 ),'{"beijing","shanghai"}',('zs',12,'武汉'));
select * from items_table
where json_s ->> 'code' = '100015' -- -> 获取的元素是json格式，->> 获得的是字符串
and text_arr[2] = 'shanghai'
and (item_i).age = 12 -- 复合列要用括号，否则会报错
16、范围类型：
范围数据类型代表着某一元素类型在一定范围内的值。
int4range — integer 的范围；
int8range —bigint的范围；
numrange —numeric的范围；
tsrange —timestamp without time zone的范围；
tstzrange —timestamp with time zone的范围；
daterange —date的范围；
17、对象标识符：
| 名字 | 引用 | 描述 | 数值栗子 |
| oid | 任意 | 数字化的对象标识符 | 564182 |
| regproc | pg_proc | 函数名字 | sum |
| regprocedure | pg_proc | 带参数类型的函数 | sum(int4) |
| regoper | pg_operator | 操作符名 | + |
| regoperator | pg_operator | 带参数类型的操作符 | *(integer,integer) 或 -(NONE,integer) |
| regclass | pg_class | 关系名 | pg_type |
| regtype | pg_type | 数据类型名 | integer |
| regconfig | pg_ts_config | 文本搜索配置 | english |
| regdictionary | pg_ts_dict | 文本搜索字典 | simple |
18、伪类型：
| 名字 | 描述 |
| any | 表示一个函数接受任何输入数据类型。 |
| anyelement | 表示一个函数接受任何数据类型。 |
| anyarray | 表示一个函数接受任意数组数据类型。 |
| anynonarray | 表示一个函数接受任意非数组数据类型。 |
| anyenum | 表示一个函数接受任意枚举数据类型。 |
| anyrange | 表示一个函数接受任意范围数据类型。 |
| cstring | 表示一个函数接受或者返回一个空结尾的 C 字符串。 |
| internal | 表示一个函数接受或者返回一种服务器内部的数据类型。 |
| language_handler | 一个过程语言调用处理器声明为返回 language_handler。 |
| fdw_handler | 一个外部数据封装器声明为返回 fdw_handler。 |
| record | 标识一个函数返回一个未声明的行类型。 |
| trigger | 一个触发器函数声明为返回 trigger。 |
| void | 表示一个函数不返回数值。 |
| opaque | 一个已经过时的类型，以前用于所有上面这些用途。 |
原文地址：https://blog.csdn.net/qishiheyongshi/article/details/142926616
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！