postgresql 快速入门-5

最新推荐文章于 2024-04-19 16:01:53 发布

闫辉_13510215218

最新推荐文章于 2024-04-19 16:01:53 发布

阅读量350

点赞数

分类专栏： Postgresql 文章标签： postgresql

本文链接：https://blog.csdn.net/uddiqpl/article/details/109097488

版权

Postgresql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

概述

本文基于pg 官方文档第5章“数据定义"，归纳和总结相应的知识点。主要包括：约束、修改表定义、模式、表分区等。

默认值

定义语法示例如下：

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'), --表达式
    name text,
    price numeric DEFAULT 9.99, --常量
);

注意：表达式是在插入时候被实时计算。

生成列

创建生成列的语法示例如下：

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

知识要点：

有两种生成列：:存储生成列和虚拟生成列。pg目前只实现了存储生成列。
在INSERT 或 UPDATE 时不能为生成列指定值，但可以指定关键字DEFAULT。
带默认值的列和生成列之间的差异 -> 1，行首次插入时/行每次更新时计算。2，值能/不能被覆盖。3，能/不能使用volatile 函数。例如random()函数。
外部表可以有生成列。
生成列与其基础列的访问权限是分开维护的。基于此特点，可以实现某个特定角色只能访问生成列，而不能访问基础列。
生成列在BEFORE 触发器运行后更新。因此，BEFORE 触发器中的基础列所做的变更将反映在生成列中。反过来，BEFORE 触发器中不允许访问生成列。

生成列的限制

不能使用volatile函数。
不能使用子查询或以任何方式引用当前行以外的任何内容；
不能引用另一个生成列；不能引用系统列，除了 tableoid。
不能具有DEFAULT和IDENTITY的定义。
不能是分区键的一部分。
对于继承：
- 如果父列是生成列，则子列也必须是生成列，且表达式相同。在子列的定义中，可省略GENERATED子句。
- 在多重继承的情况下，如果有一个父列是生成列，则所有父列都必须是生成列，且具有相同的表达式。
- 如果父列不是生成列，则子列可为生成列，也可不为生成列。

约束

检查约束（Check)、唯一约束（UNIQUE)、非空约束(NOT NULL)、主键（PRIMARY KEY)，语法示例如下：

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,  --唯一约束+非空约束。顺序不重要。
    -- 等价于：product_no integer PRIMARY KEY,
    name text,
    price numeric NOT NULL CHECK (price > 0), --未命名约束名称。这里有两个约束。
    price numeric CONSTRAINT positive_price CHECK (price > 0) -- 命名约束
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price),--表约束，独立于列而定义。上面为列约束。
    -- 列约束可以写成表约束。例如：UNIQUE (product_no)
    -- 可以为组合列。例如：UNIQUE (a, c)，PRIMARY KEY (a, c)
);

注意：

1，列约束可以写成表约束。反过来则不一定。因为表约束中可以写多列，列约束只能写其所依附的列。

2，尽可能使用UNIQUE, EXCLUDE,或 FOREIGN KEY约束，以表示跨行和跨表限制。原因是PostgreSQL不支持引用表数据以外的要检查的新增或更新的行的CHECK约束。详情参考：5.4.1. 检查约束。

3，Check时如果用到自定义的函数，且需要修改此函数时，建议的做法是先删除约束，在修改函数定义。

4，NOT NULL表示不可为空；但NULL不是必须为空，而是可以为空。

外键约束的语法示例：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    ...
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no), -- 如果省略括号内的product_no，则默认为products表的主键
    -- FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) 也可以写成表级约束。
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT, --禁止删除被引用的行
    order_id integer REFERENCES orders ON DELETE CASCADE, --级联删除被引用的行
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

注：

1，除了ON DELETE，还有ON UPDATE；对应动作有：RESTRICT、CASCADE、NO ACTION、SET NULL、SET DEFAULT。

2，NO ACTION和RESTRICT的区别：NO ACTION允许检查被推迟到事务的最后，而RESTRICT则不会。

3，MATCH FULL 和 MATCH SIMPLE的区别：对于MATCH FULL，一个引用行只有在它的全部引用列为null时才不需要满足外键约束；对于MATCH SIMPLE，一个引用行只有在它的任一引用列为null时便不需要满足外键约束。

4，一个外键所引用的列必须是一个主键或者被唯一约束。

排他约束

排他约束确保：如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回false或null。

CREATE EXTENSION btree_gist;

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  
   AGE WITH <>)   -- 如果整个表边式返回 true，则不满足排他约束，不允许插入。否则允许。
);

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 ); --满足排他约束，可以insert。

INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(4, 'Allen', 43, 'California', 20000.00 ); --不满足排他约束。不能insert

参考：

https://blog.csdn.net/rudygao/article/details/50547465

https://www.cndba.cn/foucus/article/3998

http://www.postgres.cn/docs/12/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

系统列

每个表的系统列包括：tableoid、xmin、cmin、xmax、cmax、ctid。

注意：

1，xmin、xmax会增长并绕回。运维中需要注意这一点。

修改表定义

--增加和删除列
ALTER TABLE products ADD COLUMN description text; --增加一个列,若无DEFAULT，则会填充空值
ALTER TABLE products ADD COLUMN description text CHECK (description <> ''); --同时也指定约束。
ALTER TABLE products DROP COLUMN description; --删除列
ALTER TABLE products DROP COLUMN description CASCADE; --级联删除列

--增加和删除约束
ALTER TABLE products ADD CHECK (name <> ''); --增加check约束
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);--增加unique约束
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;--增加foreig key约束
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --增加 not null 约束
ALTER TABLE products DROP CONSTRAINT some_name; --移除约束。约束名根据需要加双引号；根据需要使用cascade。
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; -- 移除not null约束

--增加和删除默认值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; --为某列设置默认值
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

--修改列的数据类型
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

--重命名表名和列命名
ALTER TABLE products RENAME COLUMN product_no TO product_number;
ALTER TABLE products RENAME TO items;

注意：

1，增加列时，若DEFAULT为常量，则不更新已有行；若为变量，则已有行会被更新。

2，修改列类型时，只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。

权限

权限	缩写	适用对象类型
`SELECT`	`r` (“读”)	`LARGE OBJECT`, `SEQUENCE`, `TABLE` (and table-like objects), table column
`INSERT`	`a` (“增补”)	`TABLE`, table column
`UPDATE`	`w` (“写”)	`LARGE OBJECT`, `SEQUENCE`, `TABLE`, table column
`DELETE`	`d`	`TABLE`
`TRUNCATE`	`D`	`TABLE`
`REFERENCES`	`x`	`TABLE`, table column
`TRIGGER`	`t`	`TABLE`
`CREATE`	`C`	`DATABASE`, `SCHEMA`, `TABLESPACE`
`CONNECT`	`c`	`DATABASE`
`TEMPORARY`	`T`	`DATABASE`
`EXECUTE`	`X`	`FUNCTION`, `PROCEDURE`
`USAGE`	`U`	`DOMAIN`, `FOREIGN DATA WRAPPER`, `FOREIGN SERVER`, `LANGUAGE`, `SCHEMA`, `SEQUENCE`, `TYPE`

对象类型	所有权限	默认 `PUBLIC` 权限	psql 命令
`DATABASE`	`CTc`	`Tc`	`\l`
`DOMAIN`	`U`	`U`	`\dD+`
`FUNCTION` or `PROCEDURE`	`X`	`X`	`\df+`
`FOREIGN DATA WRAPPER`	`U`	none	`\dew+`
`FOREIGN SERVER`	`U`	none	`\des+`
`LANGUAGE`	`U`	`U`	`\dL+`
`LARGE OBJECT`	`rw`	none
`SCHEMA`	`UC`	none	`\dn+`
`SEQUENCE`	`rwU`	none	`\dp`
`TABLE` (and table-like objects)	`arwdDxt`	none	`\dp`
Table column	`arwx`	none	`\dp`
`TABLESPACE`	`C`	none	`\db+`
`TYPE`	`U`	`U`	`\dT+`

语法示例：

ALTER TABLE table_name OWNER TO new_owner; --修改表的owner
GRANT UPDATE ON accounts TO joe;--授予表的update 权限
REVOKE ALL ON accounts FROM PUBLIC;--撤销权限

注：

1，授予权限时使用“with grant option”，允许接收人将权限转授给其他人。

行安全性策略

策略的创建可以使用CREATE POLICY命令，策略的修改可以使用ALTER POLICY命令，而策略的删除可以使用 DROP POLICY命令
要为一个给定表启用行安全性： ALTER TABLE ... ENABLE ROW LEVEL SECURITY

语法示例：

CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);--允许只有managers角色的成员能访问行， 并且只能访问它们账户的行
CREATE POLICY user_policy ON users USING (user_name = current_user); --没有指定角色（或者用特别的用户名PUBLIC）， 则该策略适用于系统上所有的用户。


--组合多条策略。允许所有用户查看users表中的所有行，但只能修改他们自己的行
CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);
CREATE POLICY user_mod_policy ON users USING (user_name = current_user);

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin USING (pg_catalog.inet_client_addr() IS NULL);--前面的策略为“允许”，这条策略为“限制”

注:

1，更多示例，请参考：https://www.postgresql.org/docs/13/ddl-rowsecurity.html

2，某些情境下会存在绕过行安全性策略，例如引用的完整性检查（例如唯一或主键约束和外键引用）。

3，完整备份时，注意不要因为行安全性造成遗漏。可以将row_security配置参数为 off。为

4，当策略表达式跨行时，虽然可以使用SELECT子句，但要注意其可能的副作用。

模式

语法示例

CREATE SCHEMA myschema;--创建一个schema
schema.table --访问时的限定名
database.schema.table --表面上兼容SQL。但database必须为当前正连接的db。
CREATE TABLE myschema.mytable (
 ...
);  --在新schema中创建表
DROP SCHEMA myschema; --删除一个schema
DROP SCHEMA myschema CASCADE; --级联删除
CREATE SCHEMA schema_name AUTHORIZATION user_name; --创建一个由其他人所拥有的schema。

--public schema
CREATE TABLE products ( ... ); --等价于：CREATE TABLE public.products ( ... );

schema的搜索路径：在默认配置中，任何非限定访问将只能指向public schema；

SHOW search_path; --显示当前搜索路径
SET search_path TO myschema,public; --由于myschema是路径中的第一个元素，新对象会被默认创建在其中
REVOKE CREATE ON SCHEMA public FROM PUBLIC;--回收每一个用户在schema public上的create 权限。

注意：

1，搜索路径对于数据类型名称、函数名称和操作符名称的作用与表名一样。数据类型和函数名称可以使用和表名完全相同的限定方式。

2，如果需要在一个表达式中写一个限定的操作符名称，必须写成一种特殊的形式：OPERATOR(schema.operator)，例如
SELECT 3 OPERATOR(pg_catalog.+) 4;
3，默认情况下，所有人都拥有在Schema public上的CREATE和USAGE权限；要访问Schema，需要USAGE权限。

4，Schema pg_catalog默认为优先搜索（相比用户自定义的Schema）；若有需要，可显式地指定search_path将其放在末尾。

5，不使用安全模式使用模式时，可以在每个会话开始时采取保护性措施。通过设置search_path到空字符串或在其它情况下从search_path中删除非超级用户可写的模式。

继承

SELECT默认行为是包括父表、子表；如果要限定仅父表，可以指定ONLY。INSERT或COPY则不是默认包括子表。

--假设cities表是父表，它还有个子表capitals
SELECT name, altitude FROM ONLY cities WHERE altitude > 500; --限定仅查询父表
SELECT name, altitude FROM cities* WHERE altitude > 500;--显示包括子表

父表上的所有检查约束和非空约束都将自动被它的后代所继承，除非显式地指定了NO INHERIT子句。其他类型的约束（唯一、主键和外键约束）则不会被继承。
一个表可以从超过一个的父表继承，在这种情况下它拥有父表们所定义的列的并集。如果在这个集合中出现重名列，那么这些列将被“合并”，前提是这些列必须具有相同的数据类型。

闫辉_13510215218

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
postgresql 快速入门-5

概述本文基于pg 官方文档第5章“数据定义"，归纳和总结相应的知识点。主要包括：约束、修改表定义、模式、表分区等。默认值定义语法：CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99);...
复制链接

扫一扫