PG中的序列
CREATE SEQUENCE
CREATE SEQUENCE — 定义一个新的序列发生器
大纲
1 2 3 4 | CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] |
描述
CREATE SEQUENCE创建一个新的序列数 发生器。这涉及到用名称name创建并且初始化 一个新的特殊的单行表。该发生器将由发出该命令的用户所拥有。
如果给出一个模式名称，则该序列将将被创建在指定的模式中。否则它会被 创建在当前模式中。临时序列存在于一个特殊的模式中，因此在创建临时序列 时不能给出模式名。序列名称必须与同一模式中任何其他序列、表、索引、 视图或者外部表的名称不同。
在序列被创建后，可以使用函数 nextval、 currval以及 setval来操作该序列。这些函数在 第 9.17 节中有介绍。
尽管无法直接更新一个序列，可以使用这样的查询：
1 | SELECT * FROM name; |
来检查一个序列的参数以及当前状态。特别地，序列的 last_value域显示被任意会话最后一次取得的值（当然， 在被打印时该值可能已经过时了，因为可能有其他会话正在执行 nextval调用）。
参数
TEMPORARYorTEMP如果被指定，只会为这个会话创建序列对象，并且在会话退出时自动 删除它。当临时序列存在时，已有的同名永久序列（在这个会话中） 会变得不可见，不过可以用模式限定的名称来引用同名永久序列。
IF NOT EXISTS如果已经存在一个同名的关系时不要抛出错误。这种情况下会发出一个 提示。注意这不保证现有的关系与即将创建的序列相似 — 它甚至可能 都不是一个序列。
name要创建的序列的名称（可以是模式限定的）。
data_type可选的子句
AS *data_type*制定序列的数据类型。有效类型是smallint、integer、 和bigint。默认是bigint。 数据类型决定了序列的默认最小和最大值。increment可选的子句
INCREMENT BY *increment*指定为了 创建新值会把哪个值加到当前序列值上。一个正值将会创造一个上升 序列，负值会创造一个下降序列。默认值是 1。minvalueNO MINVALUE可选的子句
MINVALUE *minvalue*决定一个序列 能产生的最小值。如果没有提供这个子句或者指定了NO MINVALUE，那么会使用默认值。 升序序列的默认值为1。降序序列的默认值为数据类型的最小值。maxvalueNO MAXVALUE可选的子句
MAXVALUE *maxvalue*决定该序列 的最大值。如果没有提供这个子句或者指定了NO MAXVALUE，那么将会使用默认值。 升序序列的默认值是数据类型的最大值。降序序列的默认值是-1。start可选的子句
START WITH *start*允许序列从任何 地方开始。对于上升序列和下降序列来说，默认的开始值分别是minvalue和maxvalue。cache可选的子句
CACHE *cache*指定要预分配多少 个序列数并且把它们放在内存中以便快速访问。最小值为 1 （一次只生成 一个值，即没有缓存），默认值也是 1。CYCLENO CYCLE对于上升序列和下降序列，
CYCLE选项允许序列 在分别达到maxvalue和minvalue时回卷。如果达到 该限制，下一个产生的数字将分别是minvalue和maxvalue。如果指定了NO CYCLE，当序列到达其最大值 后任何nextval调用将返回一个错误。如果CYCLE和NO CYCLE都没有 被指定，则默认为NO CYCLE。OWNED BYtable_name.column_nameOWNED BY NONEOWNED BY选项导致序列被与一个特定的表列关联 在一起，这样如果该列（或者整个表）被删除，该序列也将被自动删除。 指定的表必须和序列具有相同的拥有者并且在同一个模式中。默认选项OWNED BY NONE指定该序列不与某个列关联。
注解
使用DROP SEQUENCE移除一个序列。
序列是基于bigint算法的，因此范围是不能超过一个八字节 整数的范围（-9223372036854775808 到 9223372036854775807）。
由于nextval和setval调用绝不会回滚， 如果需要序数的“无间隙”分配，则不能使用序列对象。可以 通过在一个只包含一个计数器的表上使用排他锁来构建无间隙的分配， 但是这种方案比序列对象开销更大，特别是当有很多事务并发请求序数 时。
如果对一个将由多个会话并发使用的序列对象使用了大于 1 的cache设置，可能会得到意想不到的结果。 每个会话会在访问该序列对象时分配并且缓存后续的序列值，并且相应地增加 该序列对象的last_value。然后，在该会话中下一次 nextval会做 cache-1，并且简单地 返回预分配的值而不修改序列对象。因此，任何已分配但没有在会话中使用的 数字将会在该会话结束时丢失，导致该序列中的“空洞”。
进一步，尽管多个会话能分配到不同的序列值，这些值可能会在所有会话都被 考虑时生成出来。例如， cache的设置为 10，会话 A 可能储存值 1..10 并且返回nextval=1，然后会话 B 可能储存值 11..20 并且在 A 生成nextval=2 之前返回 nextval=11。因此，如果 cache设置为 1，可以 安全地假设nextval值被顺序地生成。如果cache设置大于 1，就只能假定 nextval值都是可区分的，但不能保证它们被完全地顺序生成。 还有，last_value将反映服务于任意会话的最后一个值，不管它 是否已经被nextval返回过。
另一个考虑是，在这样一个序列上执行的setval将不会通知 其他会话，直到它们用尽了任何已缓存的预分配值。
示例
创建一个称作serial的上升序列，从 101 开始：
1 | CREATE SEQUENCE serial START 101; |
从这个序列中选取下一个数字：
1 2 3 4 5 | SELECT nextval('serial'); nextval --------- 101 |
再从这个序列中选取下一个数字：
1 2 3 4 5 | SELECT nextval('serial'); nextval --------- 102 |
在一个INSERT命令中使用这个序列：
1 | INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); |
在一次COPY FROM后更新新列值：
1 2 3 4 | BEGIN; COPY distributors FROM 'input_file'; SELECT setval('serial', max(id)) FROM distributors; END; |
兼容性
CREATE SEQUENCE符合SQL 标准，不过下列除外：
- 使用
nextval()而不是标准的NEXT VALUE FOR表达式获取下一个值。 OWNED BY子句是一种PostgreSQL扩展。
ALTER SEQUENCE
ALTER SEQUENCE — 更改一个序列发生器的定义
大纲
1 2 3 4 5 6 7 8 9 10 11 | ALTER SEQUENCE [ IF EXISTS ] name [ AS data_type ] [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ RESTART [ [ WITH ] restart ] ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema |
描述
ALTER SEQUENCE更改一个现有序列发生器的参数。 任何没有在ALTER SEQUENCE命令中明确设置的参数 保持它们之前的设置。
要使用ALTER SEQUENCE，你必须拥有该序列。要更改一个序列 的模式，你还必须拥有新模式上的CREATE特权。要更改拥有者，你还必须 是新拥有角色的一个直接或者间接成员，并且该角色必须具有该域的模式上的 CREATE特权（这些限制强制修改拥有者不能做一些通过删除和重 建该序列做不到的事情。不过，一个超级用户怎么都能更改任何序列的所有权。）。
参数
name要修改的序列的名称（可以是模式限定的）。
IF EXISTS在序列不存在时不要抛出一个错误。这种情况下会发出一个提示。
data_type可选子句
AS *data_type*改变序列的数据类型。有效类型是smallint、integer和bigint。当且仅当先前的最小值和最大值是旧数据类型的最小值或最大值时（换句话说， 如果序列是使用NO MINVALUE或NO MAXVALUE， 隐式或显式创建的），则更改数据类型会自动更改序列的最小值和最大值。 否则，将保留最小值和最大值，除非将新值作为同一命令的一部分给出。 如果最小值和最大值不符合新的数据类型，则会生成错误。increment子句
INCREMENT BY *increment*是可选的。 一个正值将产生一个上升序列，一个负值会产生一个下降序列。如果 没有指定，旧的增量值将被保持。minvalueNO MINVALUE可选的子句
MINVALUE *minvalue*决定一个序列 能产生的最小值。如果指定了NO MINVALUE，上升序列和下降序列的默认值分别是 1 和 数据类型的最小值。如果这些选项都没有被指定，将保持当前的 最小值。maxvalueNO MAXVALUE可选的子句
MAXVALUE *maxvalue*决定一个序列 能产生的最大值。如果指定了NO MAXVALUE，上升序列和下降序列的默认值分别是 数据类型的最大值和 -1。如果这些选项都没有被指定，将保持当前的 最大值。start可选的子句
START WITH *start*更改该序列被记录的开始值。 这对于当前\序列值没有影响，它会简单地设置 未来ALTER SEQUENCE RESTART命令将会使用的值。restart可选的子句
RESTART [ WITH *restart* ]更改该序列的 当前值。这类似于用is_called=false调用setval函数：被指定的值将会被 下一次\nextval调用返回。写上没有restart值的RESTART等效于提供被CREATE SEQUENCE记录的或者上一次被ALTER SEQUENCE START WITH设置的开始值。与setval调用相比，序列上的RESTART操作是事务性的并阻止并发事务从同一序列中获取数字。 如果这不是所需的操作模式，则应使用setval。cache子句
CACHE *cache*使得序列数字被预先 分配并且保存在内存中以便更快的访问。最小值是 1（每次只产生一个值，即 无缓存）。如果没有指定，旧的缓冲值将被保持。CYCLE可选的
CYCLE关键词可以被用来允许该序列在达到maxvalue（上升序列）或minvalue（下降序列）时 回卷。如果到达该限制，下一个被产生的数字将分别是minvalue或者maxvalue。NO CYCLE如果指定了可选的
NO CYCLE关键词，任何在该 序列到达其最大值后的nextval调用将会返回 一个错误。如果既没有指定CYCLE也没有指定NO CYCLE，旧的循环行为将被保持。OWNED BYtable_name.column_nameOWNED BY NONEOWNED BY选项导致该序列与一个特定的表列相关联， 这样如果该列（或者整个表）被删除，该序列也会被自动删除。如果指定， 这种关联会替代之前为该序列指定的任何关联。被指定的表必须具有相同的 拥有者并且与该序列在同一个模式中。指定OWNED BY NONE可以移除任何现有的关联，让该序列 “自立”。new_owner该序列的新拥有者的用户名。
new_name该序列的新名称。
new_schema该序列的新模式。
注解
ALTER SEQUENCE将不会立即影响除当前后端外 其他后端中的nextval结果，因为它们有预分配（缓存）的序列 值。在注意到序列生成参数被更改之前它们将用尽所有缓存的值。当前后端将被 立刻影响。
ALTER SEQUENCE不会影响该序列的 currval状态（在 PostgreSQL 8.3 之前有时会影响）。
ALTER SEQUENCE阻塞并发nextval、 currval、lastval和 setval调用。
由于历史原因，ALTER TABLE也可以被用于序列， 但是只有等效于上述形式的ALTER TABLE变体才被 允许用于序列。
示例
在 105 重启一个被称为serial的序列：
1 | ALTER SEQUENCE serial RESTART WITH 105; |
兼容性
ALTER SEQUENCE符合SQL 标准，不过AS、START WITH、 OWNED BY、OWNER TO、RENAME TO 以及SET SCHEMA子句是 PostgreSQL扩展。
DROP SEQUENCE
DROP SEQUENCE — 移除一个序列
大纲
1 | DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] |
描述
DROP SEQUENCE移除序数生成器。 一个序列只能被其拥有者或超级用户删除。
参数
IF EXISTS如果该序列不存在则不要抛出一个错误，而是发出一个提示。
name一个序列的名称（可以是模式限定的）。
CASCADE自动删除依赖于该序列的对象，然后删除所有 依赖于那些对象的对象（见第 5.14 节）。
RESTRICT如果有任何对象依赖于该序列，则拒绝删除它。这是默认值。
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
示例
要移除序列serial：
1 | DROP SEQUENCE serial; |
兼容性
DROP SEQUENCE符合 SQL标准，不过该标准只允许每个命令中删除一个 序列并且没有IF EXISTS选项。该选项是一个 PostgreSQL扩展。
依赖跟踪
当我们创建一个涉及到很多具有外键约束、视图、触发器、函数等的表的复杂数据库结构时，我们隐式地创建了一张对象之间的依赖关系网。例如，具有一个外键约束的表依赖于它所引用的表。
为了保证整个数据库结构的完整性，PostgreSQL确保我们无法删除仍然被其他对象依赖的对象。例如，尝试删除第 5.4.5 节中的产品表会导致一个如下的错误消息，因为有订单表依赖于产品表：
1 2 3 4 5 | DROP TABLE products; ERROR: cannot drop table products because other objects depend on it DETAIL: constraint orders_product_no_fkey on table orders depends on table products HINT: Use DROP ... CASCADE to drop the dependent objects too. |
该错误消息包含了一个有用的提示：如果我们不想一个一个去删除所有的依赖对象，我们可以执行：
1 | DROP TABLE products CASCADE; |
这样所有的依赖对象将被移除，同样依赖于它们的任何对象也会被递归删除。在这种情况下，订单表不会被移除，但是它的外键约束会被移除。之所以在这里会停下，是因为没有什么依赖着外键约束（如果希望检查DROP ... CASCADE会干什么，运行不带CASCADE的DROP并阅读DETAIL输出）。
PostgreSQL中的几乎所有DROP命令都支持CASCADE。当然，其本质的区别随着对象的类型而不同。我们也可以用RESTRICT代替CASCADE来获得默认行为，它将阻止删除任何被其他对象依赖的对象。
注意
根据SQL标准，在DROP命令中指定RESTRICT或CASCADE是被要求的。但没有哪个数据库系统真正强制了这个规则，但是不同的系统中两种默认行为都是可能的。
如果一个DROP命令列出了多个对象，只有在存在指定对象构成的组之外的依赖关系时才需要CASCADE。例如，如果发出命令DROP TABLE tab1, tab2且存在从tab2到tab1的外键引用，那么就不需要CASCADE即可成功执行。
对于用户定义的函数，PostgreSQL会追踪与函数外部可见性质相关的依赖性，例如它的参数和结果类型，但不\追踪检查函数体才能知道的依赖性。例如，考虑这种情况：
1 2 3 4 5 6 7 8 | CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); CREATE TABLE my_colors (color rainbow, note text); CREATE FUNCTION get_color_note (rainbow) RETURNS text AS 'SELECT note FROM my_colors WHERE color = $1' LANGUAGE SQL; |
（SQL语言函数的解释见第 37.5 节）。PostgreSQL将会注意到get_color_note函数依赖于rainbow类型：删掉该类型会强制删除该函数，因为该函数的参数类型就无法定义了。但是PostgreSQL不会认为get_color_note依赖于my_colors表，因此即使该表被删除也不会删除这个函数。虽然这种方法有缺点，但是也有好处。如果该表丢失，这个函数在某种程度上仍然是有效的，但是执行它会导致错误。创建一个同名的新表将允许该函数重新有效。
