本文主要是介绍PostgreSQL教程(四十二):参考命令(一)之SQL命令,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
1、ABORT
ABORT — 中止当前事务
大纲
ABORT [ WORK | TRANSACTION ]
描述
ABORT
回滚当前事务并且导致由该事务所作的所有更新被丢弃。这个命令的行为与标准SQL命令ROLLBACK的行为一样,并且只是为了历史原因存在。
参数
WORK
TRANSACTION
可选关键词。它们没有效果。
注解
使用COMMIT成功地终止一个事务。
在一个事务块之外发出ABORT
会发出一个警告消息并且不会产生效果。
例子
中止所有更改:
ABORT;
兼容性
这个命令是一个因为历史原因而存在的PostgreSQL扩展。ROLLBACK
是等效的标准 SQL 命令。
参见
BEGIN,COMMIT,ROLLBACK
2、ALTER AGGREGATE
ALTER AGGREGATE — 更改一个聚集函数的定义
大纲
ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name
ALTER AGGREGATE name ( aggregate_signature )OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema其中 aggregate_signature 是:* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
描述
ALTER AGGREGATE
更改一个聚集函数的定义。
要使用ALTER AGGREGATE
,你必须拥有该聚集函数。 要更改一个聚集函数的模式,你还必须具有新模式上的 CREATE
特权。要修改拥有者,你还必须是新拥有角色 的一个直接或者间接成员,并且那个角色必须在聚集函数的模式上拥有 CREATE
特权(这些限制强制要求拥有者不能通过丢弃并重建该聚集函数来做任何你不能做的事情。不过,一个超级用户可以更改任何聚集函数的所有权)。
参数
name
一个现有聚集函数的名称(可以是模式限定的)。
argmode
一个参数的模式:IN
或VARIADIC
。 如果省略,默认为IN
。
argname
一个参数的名称。注意ALTER AGGREGATE
并不真正关心参数名称,因为决定聚集函数的身份时只需要参数的数据类型。
argtype
聚集函数要在其上操作的输入数据类型。要引用一个零参数聚集函数,在参数 说明列表的位置写上*
。要引用一个有序集聚集函数,在直接参数 说明和聚集参数说明之间写上ORDER BY
。
new_name
聚集函数的新名称。
new_owner
聚集函数的新拥有者。
new_schema
聚集函数的新模式。
注解
引用有序集聚集的推荐语法是在直接参数说明和聚集参数说明之间写上 ORDER BY
,这和CREATE AGGEREGATE中的风格相同。不过,省略ORDER BY
并且只把直接和 聚集参数说明放到一个单一列表中也是可以的。在这种简写形式中,如果 在直接和聚集参数列表中都使用了VARIADIC "any"
,只用 写一次VARIADIC "any"
。
示例
要把用于类型integer
的聚集函数 myavg
重命名为my_average
:
ALTER AGGREGATE myavg(integer) RENAME TO my_average;
要把用于类型integer
的聚集函数 myavg
的拥有者改为joe
:
ALTER AGGREGATE myavg(integer) OWNER TO joe;
把带有float8
类型直接参数和integer
类型聚集参数的有序集聚集mypercentile
移动到 模式myschema
中:
ALTER AGGREGATE mypercentile(float8 ORDER BY integer) SET SCHEMA myschema;
这也能行:
ALTER AGGREGATE mypercentile(float8, integer) SET SCHEMA myschema;
兼容性
在 SQL 标准中没有ALTER AGGREGATE
语句。
参见
CREATE AGGREGATE,DROP AGGREGATE
3、ALTER COLLATION
ALTER COLLATION — 更改一个排序规则的定义
大纲
ALTER COLLATION name REFRESH VERSIONALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema
描述
ALTER COLLATION
更改一个排序规则的定义。
你必须拥有要对其使用ALTER COLLATION
的排序规则。要更改拥有者,你必须是新的拥有角色的直接或者间接成员,并且该角色必须在排序规则的模式上具有CREATE
特权(这些限制强制要求拥有者不能通过丢弃并重建该排序规则来做任何你不能做的事情。不过,一个超级用户可以更改任何排序规则的所有权)。
参数
name
一个现有排序规则的名称(可以是模式限定的)。
new_name
排序规则的新名称。
new_owner
排序规则的新拥有者。
new_schema
排序规则的新模式。
REFRESH VERSION
更新排序规则的版本。 参阅下面的注意。
注意
使用ICU库提供的排序规则时,创建排序规则对象时,系统目录中会记录排序规则的特定ICU版本。 使用排序规则时,将根据记录的版本检查当前版本,并在发生不匹配时发出警告,例如:
WARNING: collation "xx-x-icu" has version mismatch
DETAIL: The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.
排序规则定义的更改会导致索引损坏和其他问题,因为数据库系统依赖于具有特定排序顺序的存储对象。 通常,应该避免这种情况,但它可以在合法的情况下发生,例如使用pg_upgrade
升级到与更新版本的ICU链接的服务器二进制文件。发生这种情况时, 应该重建所有依赖于该排序规则的对象,例如,使用REINDEX
。 完成后,使用命令ALTER COLLATION ... REFRESH VERSION
可以刷新排序规则版本。 这将更新系统目录以记录当前的排序规则版本,并会使警告消失。请注意, 这实际上并不检查是否所有受影响的对象都已正确重建。
以下查询可用于识别当前数据库中需要刷新的所有排序规则以及依赖它们的对象:
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",pg_describe_object(classid, objid, objsubid) AS "Object"FROM pg_depend d JOIN pg_collation cON refclassid = 'pg_collation'::regclass AND refobjid = c.oidWHERE c.collversion <> pg_collation_actual_version(c.oid)ORDER BY 1, 2;
示例
要把排序规则de_DE
重命名为german
:
ALTER COLLATION "de_DE" RENAME TO german;
要把排序规则en_US
的拥有者改成joe
:
ALTER COLLATION "en_US" OWNER TO joe;
兼容性
在 SQL 标准中没有ALTER COLLATION
语句。
参见
CREATE COLLATION,DROP COLLATION
4、ALTER CONVERSION
ALTER CONVERSION — 改变一个转换的定义
大纲
ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER CONVERSION name SET SCHEMA new_schema
描述
ALTER CONVERSION
改变一个转换的定义。
你必须拥有要对其使用ALTER CONVERSION
的转换。要更改拥有者,你必须是新的拥有角色的直接或者间接成员,并且该角色必须在转换的模式上具有CREATE
特权(这些限制强制要求拥有者不能通过丢弃并重建该转换来做任何你不能做的事情。不过,一个超级用户可以更改任何转换的所有权)。
参数
name
一个现有转换的名称(可以是模式限定的)。
new_name
转换的新名称。
new_owner
转换的新拥有者。
new_schema
转换的新模式。
示例
要把转换iso_8859_1_to_utf8
重命名为latin1_to_unicode
:
ALTER CONVERSION iso_8859_1_to_utf8 RENAME TO latin1_to_unicode;
要把转换iso_8859_1_to_utf8
的拥有者改成joe
:
ALTER CONVERSION iso_8859_1_to_utf8 OWNER TO joe;
兼容性
在 SQL 标准中没有ALTER CONVERSION
语句。
参见
CREATE CONVERSION, DROP CONVERSION
5、ALTER DATABASE
ALTER DATABASE — 更改一个数据库
大纲
ALTER DATABASE name [ [ WITH ] option [ ... ] ]这里 option 可以是:ALLOW_CONNECTIONS allowconnCONNECTION LIMIT connlimitIS_TEMPLATE istemplateALTER DATABASE name RENAME TO new_nameALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER DATABASE name SET TABLESPACE new_tablespaceALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
描述
ALTER DATABASE
更改一个数据库的属性。
第一种形式更改某些针对每个数据库的设置(详见下文)。只有数据库拥有者或者超级用户可以更改这些设置。
第二种形式更改数据库的名称。只有数据库拥有者或者超级用户可以重命名一个数据库,非超级用户拥有者还必须拥有CREATEDB
特权。当前数据库不能被重命名(如果你需要这样做请连接到一个不同的数据库)。
第三种形式更改数据库的拥有者。要修改拥有者,你必须拥有该数据库并且也是新拥有角色的一个直接或间接成员,并且你必须具有CREATEDB
特权(注意超级用户自动拥有所有这些特权)。
第四种形式更改数据库的默认表空间。只有数据库拥有者或超级用户能够这样做,你还必须对新表空间具有创建特权。这个命令会在物理上移动位于该数据库旧的默认表空间中的任何表或索引到新的表空间中。新的默认表空间对于这个数据库必须是空的,并且不能有人可以连接到该数据库。在非默认表空间中的表和索引不受影响。
剩下的形式更改用于一个PostgreSQL数据库的运行时配置变量的会话默认值。接下来只要一个新的会话在该数据库中开始,指定的值就会成为该会话的默认值。数据库相关的默认值会覆盖出现在postgresql.conf
中或者从postgres
命令行接收到的设置。只有数据库拥有者或超级用户可以更改一个数据库的会话默认值。一些变量不能用这种方式设置或者只能由超级用户更改。
参数
name
要被修改属性的数据库名称。
allowconn
如果为假,则没有人能连接到这个数据库。
connlimit
与这个数据库可以建立多少个并发连接。-1 表示没有限制。
istemplate
如果为真,则任何具有CREATEDB
特权的用户都可以从这个数据库进行克隆。如果为假,则只有超级用户或者这个数据库的拥有者可以克隆它。
new_name
数据库的新名称。
new_owner
数据库的新拥有者。
new_tablespace
数据库的新默认表空间。
这种形式的命令不能在事务块内执行。
configuration_parameter
value
将这个数据库的指定配置参数的会话默认值设置为给定值。如果value
是DEFAULT
,或者等效地使用了RESET
,数据库相关的设置会被移除,因此系统范围的默认设置将会在新会话中继承。使用RESET ALL
可清除所有数据库相关的设置。SET FROM CURRENT
会保存该会话的当前参数值作为数据库相关的值。
注解
也可以把一个会话的默认值绑定到一个特定角色而不是一个数据库,见ALTER ROLE。如果有冲突,角色相关的设置会覆盖数据库相关的值。
例子
要在数据库test
中默认禁用索引扫描:
ALTER DATABASE test SET enable_indexscan TO off;
兼容性
ALTER DATABASE
语句是一个PostgreSQL扩展。
参见
CREATE DATABASE,DROP DATABASE,SET,CREATE TABLESPACE
6、ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES — 定义默认访问特权
大纲
ALTER DEFAULT PRIVILEGES[ FOR { ROLE | USER } target_role [, ...] ][ IN SCHEMA schema_name [, ...] ]abbreviated_grant_or_revoke其中abbreviated_grant_or_revoke是下列之一:GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON TABLESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON SEQUENCESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTIONS | ROUTINES }TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON TYPESTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMASTO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON TABLESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON SEQUENCESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTIONS | ROUTINES }FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON TYPESFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMASFROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]
描述
ALTER DEFAULT PRIVILEGES
允许你设置将被应用于未来要创建的对象的特权(它不会影响分配给已经存在的对象的特权)。当前,只能修改用于模式、表(包括视图和外部表)、序列、函数和类型(包括域)的特权。其中,可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时,单词FUNCTIONS
和ROUTINES
是等效的。(推荐使用ROUTINES
,因为它是用来囊括函数和过程的一个标准术语。在较早的PostgreSQL发行版中,只允许单词FUNCTIONS
。无法为函数或过程单独设置默认特权。)
你只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置(即对当前数据库中创建的所有对象),或者只对在指定模式中创建的对象设置。被指定的针对模式的默认特权会被增加到用于特定数据类型的全局默认特权中。
如GRANT中所述,用于任何对象类型的默认特权通常会把所有可授予的权限授予给对象拥有者,并且也可能授予一些特权给PUBLIC
。不过,这种行为可以通过使用ALTER DEFAULT PRIVILEGES
修改全局默认特权来改变。
参数
target_role
一个现有角色的名称,当前角色是它的一个成员。如果FOR ROLE
被忽略,将假定为当前角色。
schema_name
一个现有模式的名称。如果被指定,以后在那个模式中创建的对象的默认特权会被修改。如果IN SCHEMA
被忽略,全局默认特权会被修改。 在使用ON SCHEMAS
时,不能使用IN SCHEMA
, 因为模式不能嵌套。
role_name
要为其授予或者收回特权的一个现有角色的名称。这个参数以及所有abbreviated_grant_or_revoke
中的其他参数会按照GRANT或者REVOKE中描述的方式运作,不过这里是为一整类的对象而不是特别指定的对象设置权限。
注解
使用psql的\ddp
命令可以获得关于默认特权的现有分配信息。特权值的含义和GRANT下为\dp
命令描述的相同。
如果你希望删除一个默认特权被修改的角色,有必要撤销其默认特权上的改变或者使用DROP OWNED BY
来为该角色去除默认特权项。
示例
为你后续在模式myschema
中创建的所有表(和视图)授予 SELECT 特权,并且也允许角色webuser
向它们之中 INSERT 数据:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
撤销上面的动作,因此后续创建的表不会有任何不寻常的权限:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
为后续由角色admin
创建的所有函数移除通常在函数上会授予的公共 EXECUTE 权限:
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
兼容性
在 SQL 标准中没有ALTER DEFAULT PRIVILEGES
语句。
参见
GRANT,REVOKE
7、ALTER DOMAIN
ALTER DOMAIN — 更改一个域的定义
大纲
ALTER DOMAIN name{ SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name{ SET | DROP } NOT NULL
ALTER DOMAIN nameADD domain_constraint [ NOT VALID ]
ALTER DOMAIN nameDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN nameRENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER DOMAIN nameVALIDATE CONSTRAINT constraint_name
ALTER DOMAIN nameOWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DOMAIN nameRENAME TO new_name
ALTER DOMAIN nameSET SCHEMA new_schema
描述
ALTER DOMAIN
更改一个现有域的定义。有几种形式:
SET
/DROP DEFAULT
这些形式设置或者移除一个域的默认值。注意默认值只会应用到后续的 INSERT
命令,它们不影响使用该域的已经 存在于表中的行。
SET
/DROP NOT NULL
这些形式更改一个域是被标记为允许 NULL 值还是拒绝 NULL 值。只有当使用该 域的列不包含空值时才能SET NOT NULL
。
ADD
domain_constraint
[ NOT VALID ]
这种形式使用和CREATE DOMAIN相同的语法为一个 域增加一个新的约束。当一个新的约束被增加到一个域时,所有使用该域的列 都会被根据新加的约束进行检查。可以通过增加使用 NOT VALID
选项的新约束来抑制这类检查,而该约束则可 以在以后使用 ALTER DOMAIN ... VALIDATE CONSTRAINT
变为可用。新插入和更新的行总是会被根据所有约束进行检查(包括被标记为 NOT VALID
的约束)。只有CHECK
约束接受 NOT VALID
。
DROP CONSTRAINT [ IF EXISTS ]
这种形式删除一个域上的约束。如果指定了IF EXISTS
并且 约束不存在,不会抛出错误。在这种情况下会转而发出一个提示。
RENAME CONSTRAINT
这种形式更改一个域上的一个约束的名称。
VALIDATE CONSTRAINT
这种形式验证一个之前作为NOT VALID
增加的约束,也就是说 验证使用该域的列中所有数据满足指定的约束。
OWNER
这种形式更改域的拥有者为指定用户。
RENAME
这种形式更改域的名称。
SET SCHEMA
这种形式更改域的模式。任何与该域关联的约束也被会移动到新的模式中。
要使用ALTER DOMAIN
,你必须拥有该域。要更改一个域的模式, 你还必须具有新模式上的CREATE
特权。要更改拥有者,你还必须 是新拥有角色的一个直接或者间接成员,并且该角色必须具有该域的模式上的 CREATE
特权(这些限制强制修改拥有者不能做一些通过删除和重 建域做不到的事情。不过,一个超级用户怎么都能更改任何域的所有权。)。
参数
name
要修改的一个现有域的名称(可能是模式限定的)。
domain_constraint
用于该域的新域约束。
constraint_name
要删除或重命名的一个现有约束的名称。
NOT VALID
不为约束的合法性验证现有的列数据。
CASCADE
自动删除依赖于该约束的对象,并且接着删除依赖于那些对象的 所有对象。
RESTRICT
如果有任何依赖对象则拒绝删除该约束。这是默认行为。
new_name
域的新名称。
new_constraint_name
约束的新名称。
new_owner
域的新拥有者的用户名。
new_schema
域的新模式。
注解
当前,如果域或者任何衍生域被数据库中的任意表的一个容器类型 列(组合、数组、范围类型的列)使用,ALTER DOMAIN ADD CONSTRAINT
、ALTER DOMAIN VALIDATE CONSTRAINT
和 ALTER DOMAIN SET NOT NULL
将会失败。这些命令最终将 会被改进成能够对这类嵌套值进行约束验证。
示例
要把一个NOT NULL
约束加到一个域:
ALTER DOMAIN zipcode SET NOT NULL;
要从一个域中移除一个NOT NULL
约束:
ALTER DOMAIN zipcode DROP NOT NULL;
要把一个检查约束增加到一个域:
ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
要从一个域移除一个检查约束:
ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
要重命名一个域上的一个检查约束:
ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;
要把域移动到一个不同的模式:
ALTER DOMAIN zipcode SET SCHEMA customers;
兼容性
ALTER DOMAIN
conforms to the SQL standard, except for the 除OWNER
、RENAME
、SET SCHEMA
以及VALIDATE CONSTRAINT
变体之外(它们是 PostgreSQL的扩展), ALTER DOMAIN
符合SQL标准。 ADD CONSTRAINT
变体的NOT VALID
子句也是一个 PostgreSQL扩展。
参见
CREATE DOMAIN,DROP DOMAIN
8、ALTER EVENT TRIGGER
ALTER EVENT TRIGGER — 更改一个事件触发器的定义
大纲
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name
描述
ALTER EVENT TRIGGER
更改一个现有事件触发器 的属性。
必须作为一个超级用户才能修改一个时间触发器。
参数
name
要修改的现有触发器的名称。
new_owner
该事件触发器的新拥有者的用户名。
new_name
该事件触发器的新名称。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] TRIGGER
这些形式配置事件触发器的触发。一个被禁用的触发器对系统来说仍然是可知的, 但是当期触发事件发生时却不会执行它。另见 session_replication_role。
兼容性
在 SQL 标准中没有ALTER EVENT TRIGGER
语句。
参见
CREATE EVENT TRIGGER,DROP EVENT TRIGGER
9、ALTER EXTENSION
ALTER EXTENSION — 更改一个扩展的定义
大纲
ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object其中 member_object 是:ACCESS METHOD object_name |AGGREGATE aggregate_name ( aggregate_signature ) |CAST (source_type AS target_type) |COLLATION object_name |CONVERSION object_name |DOMAIN object_name |EVENT TRIGGER object_name |FOREIGN DATA WRAPPER object_name |FOREIGN TABLE object_name |FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |MATERIALIZED VIEW object_name |OPERATOR operator_name (left_type, right_type) |OPERATOR CLASS object_name USING index_method |OPERATOR FAMILY object_name USING index_method |[ PROCEDURAL ] LANGUAGE object_name |PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |SCHEMA object_name |SEQUENCE object_name |SERVER object_name |TABLE object_name |TEXT SEARCH CONFIGURATION object_name |TEXT SEARCH DICTIONARY object_name |TEXT SEARCH PARSER object_name |TEXT SEARCH TEMPLATE object_name |TRANSFORM FOR type_name LANGUAGE lang_name |TYPE object_name |VIEW object_name并且 aggregate_signature 是:* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
描述
ALTER EXTENSION
更改一个已安装扩展的定义。 有几种子形式:
UPDATE
这种形式把该扩展更新到一个新版本。该扩展必须提供一个适当的更新 脚本(或者一系列脚本)来把当前已安装的版本修改成所要求的版本。
SET SCHEMA
这种形式把该扩展的对象移动到另一个模式中。要使这个命令成功,该扩展 必须是可重定位的。
ADD
member_object
这种形式把一个现有的对象加入到该扩展中。这主要对扩展更新脚本有用。 该对象后续将被当作该扩展的一个成员。尤其是该对象只有通过删除扩展 才能删除。
DROP
member_object
这种形式从该扩展移除一个成员对象。这主要对扩展更新脚本有用。只有 撤销该对象与其扩展之间的关联后才能删除该对象。
要使用ALTER EXTENSION
,你必须拥有该扩展。 ADD
/DROP
形式还要求被增加/删除对象的所有权。
参数
name
一个已安装扩展的名称。
new_version
想要得到的该扩展的新版本。这可以写成一个标识符或者一个字符串。如果没有指定, ALTER EXTENSION UPDATE
会尝试更新到该扩展的控制文件中的 默认版本。
new_schema
该扩展的新模式。
object_name
aggregate_name
function_name
operator_name
procedure_name
routine_name
要从该扩展增加或者移除的对象的名称。表、聚集、域、外部表、函数、 操作符、操作符类、操作符族、过程、例程、序列、文本搜索对象、类型和视图的名称 可以被模式限定。
source_type
该转换的源数据类型的名称。
target_type
该转换的目标数据类型的名称。
argmode
一个函数、过程或者聚集参数的模式:IN
、OUT
、 INOUT
或者VARIADIC
。如果被忽略,默认值是 IN
。注意,ALTER EXTENSION
并不真正关心OUT
参数,因为决定该函数的身份时只需要输入 参数。因此列出IN
、INOUT
和 VARIADIC
参数足矣。
argname
一个函数、过程或者聚集参数的名称。注意, ALTER EXTENSION
并不真正关心参数名称,因为 决定该函数的身份时只需要参数的数据类型。
argtype
一个函数、过程或者或聚集参数的数据类型。
left_type
right_type
该操作符参数的数据类型(可以用模式限定)。对一个前缀或后缀操作符的缺失的 参数可以写NONE
。
PROCEDURAL
这是一个噪声词。
type_name
该转换的数据类型的名称。
lang_name
该转换的语言的名称。
示例
把hstore
扩展更新到版本 2.0:
ALTER EXTENSION hstore UPDATE TO '2.0';
把hstore
扩展的模式更改到utils
:
ALTER EXTENSION hstore SET SCHEMA utils;
要向hstore
扩展增加一个现有函数:
ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);
兼容性
ALTER EXTENSION
是一个PostgreSQL 扩展。
参见
CREATE EXTENSION, DROP EXTENSION
10、ALTER FOREIGN DATA WRAPPER
ALTER FOREIGN DATA WRAPPER — 更改一个外部数据包装器的定义
大纲
ALTER FOREIGN DATA WRAPPER name[ HANDLER handler_function | NO HANDLER ][ VALIDATOR validator_function | NO VALIDATOR ][ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ]
ALTER FOREIGN DATA WRAPPER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FOREIGN DATA WRAPPER name RENAME TO new_name
描述
ALTER FOREIGN DATA WRAPPER
更改一个 外部数据包装器的定义。该命令的第一种形式用于更改外部数据包装器的 支持函数或者一般选项(至少要求一个子句)。第二种形式更改外部数据包 装器的拥有者。
只有超级用户能修改外部数据包装器。此外,只有超级用户能够拥有外部数 据包装器。
参数
name
一个已有的外部数据包装器的名称。
HANDLER
handler_function
为外部数据包装器指定一个新的处理器函数。
NO HANDLER
用于指定该外部数据包装器不再具有一个处理器函数。
注意使用没有处理器的外部数据包装器的外部表不能被访问。
VALIDATOR
validator_function
为外部数据包装器指定一个新的验证器函数。
注意,新的验证器可能会认为该外部数据包装器或者依赖于它的独立服务器 的已有选项、用户映射、外部表无效。PostgreSQL 不会做这种检查。在使用修改过的外部数据包装器之前确认这些选项正确是 用户的责任。不过,在这个ALTER FOREIGN DATA WRAPPER
命令中指定的选项将会被使用新的验证器检查。
NO VALIDATOR
用于指定该外部数据包装器不再拥有一个验证器函数。
OPTIONS ( [ ADD | SET | DROP ]
option
['value
'] [, ... ] )
为该外部数据包装器更改选项。ADD
、SET
以及DROP
指定要被执行的动作。如果没有显式地指定操作, 将假定为ADD
。选项名称必须唯一,选项名称和值(如果有) 也会使用该外部数据包装器的验证器函数来验证。
new_owner
该外部数据包装器的新拥有者的用户名。
new_name
该外部数据包装器的新名称。
示例
更改一个外部数据包装器dbi
,增加选项 foo
并删除bar
:
ALTER FOREIGN DATA WRAPPER dbi OPTIONS (ADD foo '1', DROP 'bar');
把外部数据包装器dbi
的验证器改为 bob.myvalidator
:
ALTER FOREIGN DATA WRAPPER dbi VALIDATOR bob.myvalidator;
兼容性
ALTER FOREIGN DATA WRAPPER
符合 ISO/IEC 9075-9 (SQL/MED)。不过HANDLER
、 VALIDATOR
、OWNER TO
以及RENAME
子句是扩展。
参见
CREATE FOREIGN DATA WRAPPER,DROP FOREIGN DATA WRAPPER
11、ALTER FOREIGN TABLE
ALTER FOREIGN TABLE — 更改一个外部表的定义
大纲
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] nameRENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] nameSET SCHEMA new_schema其中 action 是以下之一:ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]ALTER [ COLUMN ] column_name SET DEFAULT expressionALTER [ COLUMN ] column_name DROP DEFAULTALTER [ COLUMN ] column_name { SET | DROP } NOT NULLALTER [ COLUMN ] column_name SET STATISTICS integerALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])ADD table_constraint [ NOT VALID ]VALIDATE CONSTRAINT constraint_nameDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]DISABLE TRIGGER [ trigger_name | ALL | USER ]ENABLE TRIGGER [ trigger_name | ALL | USER ]ENABLE REPLICA TRIGGER trigger_nameENABLE ALWAYS TRIGGER trigger_nameSET WITH OIDSSET WITHOUT OIDSINHERIT parent_tableNO INHERIT parent_tableOWNER TO { new_owner | CURRENT_USER | SESSION_USER }OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
描述
ALTER FOREIGN TABLE
更改一个现有外部表的定义。 有几种子形式:
ADD COLUMN
这种形式使用与CREATE FOREIGN TABLE相同的语法把 一个新的列增加到该外部表。和为常规表增加一列不同,这种形式并不影响底层 的存储:这个动作只是简单地声明通过该外部表可以访问某个新的列而已。
DROP COLUMN [ IF EXISTS ]
这种形式从一个外部表删掉一列。如果在该表外部有任何东西依赖于该列, 你将需要写上CASCADE
,典型的例子就是视图。如果指定了 IF EXISTS
并且该列不存在,将不会抛出错误。在这种 情况下会转而发出一个提示。
SET DATA TYPE
这种形式更改一个外部表的一列的类型。同样,这种形式并不影响底层 的存储:这个动作只是简单地更改PostgreSQL 相信该列所具有的类型。
SET
/DROP DEFAULT
这些形式设置或者移除一列的默认值。默认值只会应用于后续的 INSERT
或UPDATE
命令,它们 不会导致已经在表中的行被更改。
SET
/DROP NOT NULL
把一列标记为允许或者不允许空值。
SET STATISTICS
这种形式为后续的ANALYZE操作设置针对每列 的统计收集目标。详见ALTER TABLE的类似形式。
SET (
attribute_option
= value
[, ... ] ) RESET (
attribute_option
[, ... ] )
这种形式设置或重置针对每个属性的选项。详见ALTER TABLE 的类似形式。
SET STORAGE
这种形式设置一个列的存储模式。详见 ALTER TABLE中类似的模式。注意存储模式 不会产生效果,除非该表的外部数据包装器选择处理它。
ADD
table_constraint
[ NOT VALID ]
这种形式为外部表增加一个新的约束,使用的语法和 CREATE FOREIGN TABLE中相同。当前只 支持CHECK
约束。
和向常规表增加约束的情况不同,为外部表增加约束时不会做任何事情来 验证该约束是否正确。这个动作只是简单地声明了该外部表中所有的行都 应该满足的某种新的条件(见 CREATE FOREIGN TABLE中的讨论)。如果该 约束被标记为NOT VALID
,那么它不被假设为有效,而只是 被记录下来以备未来使用。
VALIDATE CONSTRAINT
这种形式把一个之前被标记为NOT VALID
的约束标 记为有效。不会做任何动作来验证该约束,但是未来的查询将会假定该 约束是保持的。
DROP CONSTRAINT [ IF EXISTS ]
这种形式删掉在一个外部表上的指定约束。如果指定了 IF EXISTS
但约束并不存在,则不会抛出错误。 在这种情况下会发出一个提示。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] TRIGGER
这些形式配置属于该外部表的触发器的触发情况。详见 ALTER TABLE的类似形式。
SET WITH OIDS
这种形式为表增加一个oid
系统列。如果该表已经有 OID,则 这种形式不会做任何事情。除非该表的外部数据包装器支持 OID,这个列 将被简单地读作零。
注意这和ADD COLUMN oid oid
并不等效,后者将增加 一个刚好名字为oid
的普通列而不是一个系统列。
SET WITHOUT OIDS
这种形式从表移除oid
系统列。这正好 等效于DROP COLUMN oid RESTRICT
, 不过如果表上已经没有oid
时它不会做出 提示或者报错。
INHERIT
parent_table
这种形式把目标外部表作为指定的父表的新后代。详见 ALTER TABLE的类似的形式。
NO INHERIT
parent_table
这种形式把目标外部表从指定的父表的子女列表中移除。
OWNER
这种形式把该外部表的拥有者改成指定的用户。
OPTIONS ( [ ADD | SET | DROP ]
option
['value
'] [, ... ] )
更改该外部表或者其中一个列的选项。ADD
、SET
以及DROP
指定要执行的动作。如果没有显式地指定操作,将假定 为ADD
。不允许重复的名称(不过一个表选项和一个列选项可以重 名)。选项名称和值也会用外部数据包装器库来验证。
RENAME
RENAME
形式更改外部表的名称或者外 部表中一个列的名称。
SET SCHEMA
这种形式把外部表移动到另一个模式中。
所有除了RENAME
和SET SCHEMA
的 动作都能被整合到一个多修改列表以便能被并行应用。例如,可以在一个 命令中增加几个列并且/或者修改几个列的类型。
如果该命令被写作ALTER FOREIGN TABLE IF EXISTS ...
并且 该外部表不存在,则不会抛出错误。这种情况下会发出一个提示。
你必须拥有该表以使用ALTER FOREIGN TABLE
。要更改一个 外部表的模式,你必须还拥有新模式上的CREATE
特权。要 更改拥有者,你还必须是新拥有角色的一个直接或者间接成员,并且该角色必须 具有在该表的模式上的CREATE
特权(这些限制强制修改拥有 者不能做一些通过删除和重建该表做不到的事情。不过,一个超级用户怎么都能 更改任何表的所有权)。要增加一列或者修改一个列的类型,你还必须具有该数 据类型上的USAGE
特权。
参数
name
一个要修改的现有外部表的名称(可以被模式限定)。如果在表名前指定了 ONLY
,则只有该表被修改。如果没有指定ONLY
, 该表和它所有的后代表(如果有)都会被修改。可选地,在表名后面指定 *
可以显式地表示将后代表包括在内。
column_name
一个新的或者现有列的名称。
new_column_name
一个现有列的新名称。
new_name
该表的新名称。
data_type
新列的数据类型或者一个现有列的新数据类型。
table_constraint
New table constraint for the foreign table.
constraint_name
Name of an existing constraint to drop.
CASCADE
自动删除依赖于被删除列或约束的对象(例如,引用该列的视图), 并且接着删除依赖于那些对象的 所有对象。
RESTRICT
如果有任何依赖对象就拒绝删除该列或约束。这是默认行为。
trigger_name
要禁用或启用的一个触发器的名称。
ALL
禁用或者启用所有属于该外部表的触发器(如果任何触发器是内部生成 的触发器,这都要求超级用户特权。核心系统不会向外部表增加这类触发 器,但是附加代码会这样做。)。
USER
禁用或者启用属于该外部表的除了内部生成的触发器之外的所有触发器。
parent_table
要与这个外部表关联或者解除关联的父表。
new_owner
该表的新拥有者的用户名。
new_schema
该表要被移动到其中的模式的名称。
注解
关键词COLUMN
是噪声词并且可以被忽略。
当使用ADD COLUMN
或 DROP COLUMN
增加或移除一列、增加一个NOT NULL
或者CHECK
约束或者用SET DATA TYPE
更改一个列类型时,不会检查与外部服务器的一 致性。确保该表定义匹配远端是用户的责任。
关于有效参数的进一步描述可参考CREATE FOREIGN TABLE。
示例
要把一列标记为非空:
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
要更改一个外部表的选项:
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
兼容性
形式ADD
、DROP
以及 SET DATA TYPE
符合 SQL 标准。其他形式是 SQL 标准的 PostgreSQL扩展。在一个 ALTER FOREIGN TABLE
命令中指定多于一个操作也是一种扩展。
ALTER FOREIGN TABLE DROP COLUMN
可以被用来删除 一个外部表的唯一一列,从而留下一个没有列的表。这是一种 SQL 的扩展,它 允许没有列的外部表。
参见
CREATE FOREIGN TABLE,DROP FOREIGN TABLE
12、ALTER FUNCTION
ALTER FUNCTION — 更改一个函数的定义
大纲
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]action [ ... ] [ RESTRICT ]
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]RENAME TO new_name
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]SET SCHEMA new_schema
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]DEPENDS ON EXTENSION extension_name其中 action 是以下之一:CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICTIMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINERPARALLEL { UNSAFE | RESTRICTED | SAFE }COST execution_costROWS result_rowsSET configuration_parameter { TO | = } { value | DEFAULT }SET configuration_parameter FROM CURRENTRESET configuration_parameterRESET ALL
描述
ALTER FUNCTION
更改一个函数的定义。
你必须拥有该函数以使用ALTER FUNCTION
。要更改一个函数 的模式,你还必须具有新模式上的CREATE
特权。要 更改拥有者,你还必须是新拥有角色的一个直接或者间接成员,并且该角色必须 具有在该函数的模式上的CREATE
特权(这些限制强制修改拥有 者不能做一些通过删除和重建该函数做不到的事情。不过,一个超级用户怎么都能 更改任何函数的所有权)。
参数
name
一个现有函数的名称(可以被模式限定)。如果没有指定参数列表, 则该名称必须在它的模式中唯一。
argmode
一个参数的模式:IN
、OUT
、 INOUT
或者VARIADIC
。如果被忽略,默认为 IN
。注意ALTER FUNCTION
并不真正关心OUT
参数,因为在决定函数的身份时只需要输 入参数。因此列出IN
、INOUT
以及 VARIADIC
参数即可。
argname
一个参数的名称。注意ALTER FUNCTION
并不真正参数名称,因为在确定函数的身份时只需要参数的数据类型即可。
argtype
该函数的参数(如果有)的数据类型(可以被模式限定)。
new_name
该函数的新名称。
new_owner
该函数的新拥有者。注意如果该函数被标记为 SECURITY DEFINER
,它的后续执行将会使用新拥有者。
new_schema
该函数的新模式。
extension_name
该函数所以来的扩展名。
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
CALLED ON NULL INPUT
将该函数改为在某些 或者全部参数为空值时可以被调用。 RETURNS NULL ON NULL INPUT
或者 STRICT
将该函数改为只要任一参数为空值就不被调用而 是自动假定一个空值结果。详见CREATE FUNCTION。
IMMUTABLE
STABLE
VOLATILE
把该函数的稳定性更改为指定的设置。详见 CREATE FUNCTION。
[ EXTERNAL ] SECURITY INVOKER
[ EXTERNAL ] SECURITY DEFINER
更改该函数是否为一个安全性定义者。关键词EXTERNAL
是为了符合 SQL,它会被忽略。关于这项能力的详情请见 CREATE FUNCTION。
PARALLEL
决定该函数对于并行是否安全。详见 CREATE FUNCTION。
LEAKPROOF
更改该函数是否被认为是防泄漏的。关于这项能力的详情请见 CREATE FUNCTION。
COST
execution_cost
更改该函数的估计执行代价。详见CREATE FUNCTION。
ROWS
result_rows
更改一个集合返回函数的估计行数。详见 CREATE FUNCTION。
configuration_parameter
value
当该函数被调用时,要对一个配置参数做出增加或者更改的赋值。如果 value
是DEFAULT
或者使用等价的RESET
,该函数本地的设置将会被 移除,这样该函数会使用其环境中存在的值执行。使用RESET ALL
可以清除所有函数本地的设置。 SET FROM CURRENT
把ALTER FUNCTION
执行时该参数的当前值保存为进入 该函数时要应用的值。
RESTRICT
为了符合 SQL 标准存在,被忽略。
示例
要把用于类型integer
的函数sqrt
重命名为square_root
:
ALTER FUNCTION sqrt(integer) RENAME TO square_root;
要把用于类型integer
的函数sqrt
的拥有者改为joe
:
ALTER FUNCTION sqrt(integer) OWNER TO joe;
要把用于类型integer
的函数sqrt
的模式改为maths
:
ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
要把类型integer
的函数sqrt
标记为依赖于扩展mathlib
:
ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;
要调整一个函数的自动搜索路径:
ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
要禁止一个函数的search_path
的自动设置:
ALTER FUNCTION check_password(text) RESET search_path;
这个函数将用其调用者使用的搜索路径来执行。
兼容性
这个语句部分兼容 SQL 标准中的ALTER FUNCTION
语句。该标准允许修改一个函数的更多属性,但是不提供 重命名一个函数、标记一个函数为安全性定义者、为一个函数附加配置参 数值或者更改一个函数的拥有者、模式或者稳定性等功能。该标准还要求 RESTRICT
关键字,它在PostgreSQL 中是可选的。
参见
CREATE FUNCTION, DROP FUNCTION , ALTER PROCEDURE, ALTER ROUTINE
13、ALTER GROUP
ALTER GROUP — 更改角色名称或者成员关系
大纲
ALTER GROUP role_specification ADD USER user_name [, ... ]
ALTER GROUP role_specification DROP USER user_name [, ... ]其中 role_specification 可以是:role_name| CURRENT_USER| SESSION_USERALTER GROUP group_name RENAME TO new_name
描述
ALTER GROUP
更改一个用户组的属性。 这是一个被废弃的命令,不过为了向后兼容还是会被接受,因为组(以及用户) 已经被更一般的角色概念替代了。
前两个变体向一个组增加用户或者从一个组中移除用户(为了这个目的, 任何角色都可以扮演“用户”或者“组”)。这些变体 实际上等效于在被称为“组”的角色中授予或者回收成员关系, 因此最好的方法是使用GRANT或者 REVOKE。
第三种变体会更改该组的名称。这恰好等效于用ALTER ROLE 重命名该角色。
参数
group_name
要修改的组(角色)的名称。
user_name
要被加入到该组或者从该组移除的用户(角色)。这些用户必须已经存在, ALTER GROUP
不会创建或者删除用户。
new_name
该组的新名称。
示例
向一个组增加用户:
ALTER GROUP staff ADD USER karl, john;
从一个组移除一个用户:
ALTER GROUP workers DROP USER beth;
兼容性
在 SQL 标准中没有ALTER GROUP
语句。
参见
GRANT,REVOKE,ALTER ROLE
14、ALTER INDEX
ALTER INDEX — 更改一个索引的定义
大纲
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_name
ALTER INDEX name DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_numberSET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]SET TABLESPACE new_tablespace [ NOWAIT ]
描述
ALTER INDEX
更改一个现有索引的定义。 它有几种形式:
RENAME
RENAME
形式更改该索引的名称。如果索引与一个表约束(UNIQUE
、PRIMARY KEY
或者EXCLUDE
)关联,该约束也会被重命名。这对已存储的数据没有影响。
SET TABLESPACE
这种形式更改该索引的表空间为指定的表空间,并且把与该索引相关联的数据文件 移动到新的表空间中。要更改一个索引的表空间,你必须拥有该索引并且具有新表 空间上的CREATE
特权。可以使用 ALL IN TABLESPACE
形式把当前数据库中在一个表空间内的 所有索引全部移动到另一个表空间中,这将会锁定所有要被移动的索引然后挨个移 动它们。这种形式也支持OWNED BY
,即只移动属于指定角色 的索引。如果指定了NOWAIT
选项,那么当该命令无法立刻获 得所有锁时将会失败。注意这个命令不会移动系统目录,如果想要移动系统目录, 应使用ALTER DATABASE
或者显式的 ALTER INDEX
调用。另见 CREATE TABLESPACE。
ATTACH PARTITION
导致提到的索引变成附着于被修改的索引。提及的索引必须在包含被修改索引的表的一个分区上,并且具有一种等效的定义。一个附着索引不能被单独删除,它会在其父索引被删除时自动连带删除。
DEPENDS ON EXTENSION
这种形式把该索引标记为依赖于扩展,这样如果该扩展被删除,该索引也将被 自动删除。
SET (
storage_parameter
= value
[, ... ] )
这种形式为该索引更改一个或者多个索引方法相关的存储参数。可用的参数详见 CREATE INDEX。注意这个命令不会立刻修改索引内容, 根据参数你可能需要用REINDEX重建索引来得到想要的 效果。
RESET (
storage_parameter
[, ... ] )
这种形式把一个或者多个索引方法相关的存储参数重置为其默认值。正如 SET
一样,可能需要一次REINDEX
来完全更新 该索引。
ALTER [ COLUMN ]
column_number
SET STATISTICS integer
这种形式为后续的ANALYZE操作设置针对每个列的统计信息收集目标,不过只能用在被定义为表达式的索引列上。由于表达式缺少唯一的名称,我们通过该索引列的顺序号来引用它们。收集目标可以被设置为范围0到10000之间的值。另外,把它设置为-1会恢复到使用系统的默认统计信息目标(default_statistics_target)。
参数
IF EXISTS
如果该索引不存在不要抛出错误。这种情况下将发出一个提示。
column_number
引用该索引列的顺序(从左往右)位置的顺序号。
name
要更改的一个现有索引的名称(可能被模式限定)。
new_name
该索引的新名称。
tablespace_name
该索引将被移动到的表空间。
extension_name
该索引所依赖的扩展的名称。
storage_parameter
一个索引方法相关的存储参数的名称。
value
一个索引方法相关的存储参数的新值。根据该参数,这可能是一个数字或者一个 词。
注解
也可以用ALTER TABLE来做这些操作。实际上, ALTER INDEX
只是ALTER TABLE
应用在索引 上的形式的别名而已。
以前有一种ALTER INDEX OWNER
变体,但现在已被忽略(会出现 一个警告)。一个索引的拥有者不能与其基表的拥有者不同。更改基表的拥有者 会自动地更改索引的拥有者。
不允许更改系统目录索引的任何部分。
示例
要重命名一个现有索引:
ALTER INDEX distributors RENAME TO suppliers;
把一个索引移动到一个不同的表空间:
ALTER INDEX distributors SET TABLESPACE fasttablespace;
更改一个索引的填充因子(假设该索引方法支持填充因子):
ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;
为一个表达式索引设置统计信息收集目标:
CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
兼容性
ALTER INDEX
是一种 PostgreSQL扩展。
参见
CREATE INDEX,REINDEX
15、ALTER LANGUAGE
ALTER LANGUAGE — 更改一种过程语言的定义
大纲
ALTER [ PROCEDURAL ] LANGUAGE name RENAME TO new_name
ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
描述
ALTER LANGUAGE
更改一种过程语言的定义。唯一 的功能是重命名该语言或者为它赋予一个新的拥有者。要使用 ALTER LANGUAGE
,你必须是一个超级用户或者该 语言的拥有者。
参数
name
语言的名称
new_name
该语言的新名称
new_owner
该语言的新拥有者
兼容性
在 SQL 标准中没有ALTER LANGUAGE
语句。
参见
CREATE LANGUAGE,DROP LANGUAGE
16、ALTER LARGE OBJECT
ALTER LARGE OBJECT — 更改一个大对象的定义
大纲
ALTER LARGE OBJECT large_object_oid OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
描述
ALTER LARGE OBJECT
更改一个大对象的定义。
您必须拥有大对象才能使用ALTER LARGE OBJECT
。要更改所有者,您还必须是新所有者的直接或间接成员。(不过,超级用户仍然可以更改任何大对象。)当前,唯一的功能是分配新所有者,因此两者的约束都始终适用。
参数
large_object_oid
要被修改的大对象的 OID
new_owner
该大对象的新拥有者
兼容性
在 SQL 标准中没有ALTER LARGE OBJECT
语句。
参见
前面章节“大对象”
17、ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW — 更改一个物化视图的定义
大纲
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameaction [, ... ]
ALTER MATERIALIZED VIEW nameDEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameRENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameRENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameSET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]SET TABLESPACE new_tablespace [ NOWAIT ]其中 action是下列之一:ALTER [ COLUMN ] column_name SET STATISTICS integerALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }CLUSTER ON index_nameSET WITHOUT CLUSTERSET ( storage_parameter = value [, ... ] )RESET ( storage_parameter [, ... ] )OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
描述
ALTER MATERIALIZED VIEW
更改一个现有物化视图的 多个辅助属性。
要使用ALTER MATERIALIZED VIEW
,你必须拥有该物化视图。要 更改一个物化视图的模式,你还必须拥有新模式上的CREATE
特权。要更 改拥有者,你还必须是新拥有角色的一个直接或者间接成员,并且该角色必须拥有该 物化视图所在模式上的CREATE
特权(这些限制强制修改拥有者不 能做一些通过删除和重建该物化视图做不到的事情。不过,一个超级用户怎么都能更改 任何视图的所有权。)。
DEPENDS ON EXTENSION
形式把该物化视图标记为依赖于一个 扩展,这样该扩展被删除时会自动地删除掉这个物化视图。
可用于ALTER MATERIALIZED VIEW
的语句形式和动作是 ALTER TABLE
的一个子集,并且在用于物化视图时具有相 同的含义。详见ALTER TABLE的描述。
参数
name
一个现有物化视图的名称(可以是模式限定的)。
column_name
一个新的或者现有的列的名称。
extension_name
该物化视图所依赖的扩展的名称。
new_column_name
一个现有列的新名称。
new_owner
该物化视图的新拥有者的用户名。
new_name
该物化视图的新名称。
new_schema
该物化视图的新模式。
示例
把物化视图foo
重命名为 bar
:
ALTER MATERIALIZED VIEW foo RENAME TO bar;
兼容性
ALTER MATERIALIZED VIEW
是一种 PostgreSQL扩展。
参见
CREATE MATERIALIZED VIEW,DROP MATERIALIZED VIEW,REFRESH MATERIALIZED VIEW
18、ALTER OPERATOR
ALTER OPERATOR — 更改一个操作符的定义
大纲
ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )SET SCHEMA new_schemaALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )SET ( { RESTRICT = { res_proc | NONE }| JOIN = { join_proc | NONE }} [, ... ] )
描述
ALTER OPERATOR
更改一个操作符的定义。
要使用ALTER OPERATOR
,你必须拥有该操作符。要更改拥有者, 你还必须是新拥有角色的一个直接或者间接成员,并且该角色必须具有该操作符所在 模式上的CREATE
特权(这些限制强制修改拥有者不能做一些通过 删除和重建操作符做不到的事情。不过,一个超级用户怎么都能更改任何操作符的所 有权。)。
参数
name
一个现有操作符的名称(可以是模式限定的)。
left_type
该操作符左操作数的数据类型,如果该操作符没有左操作数可以写成 NONE
。
right_type
该操作符右操作数的数据类型,如果该操作符没有右操作数可以写成 NONE
。
new_owner
该操作符的新拥有者。
new_schema
该操作符的新模式。
res_proc
这个操作符的约束选择度估算器函数,写成 NONE 可以移除现有的选择度估算器。
join_proc
这个操作符的连接选择度估算器函数,写成 NONE 可以移除现有的选择度估算器。
示例
更改类型text
的一个自定义操作符a @@ b
的拥有者:
ALTER OPERATOR @@ (text, text) OWNER TO joe;
更改类型int[]
的自定义操作符a && b
的 约束和连接选择度估算器函数:
ALTER OPERATOR && (_int4, _int4) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
兼容性
在 SQL 标准中没有ALTER OPERATOR
语句。
参见
CREATE OPERATOR,DROP OPERATOR
19、ALTER OPERATOR CLASS
ALTER OPERATOR CLASS — 更改一个操作符类的定义
大纲
ALTER OPERATOR CLASS name USING index_methodRENAME TO new_nameALTER OPERATOR CLASS name USING index_methodOWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER OPERATOR CLASS name USING index_methodSET SCHEMA new_schema
描述
ALTER OPERATOR CLASS
更改一个操作符类的定义。
要使用ALTER OPERATOR CLASS
,你必须拥有该操作符类。要 修改拥有者,你还必须是新拥有角色的一个直接或间接成员,并且该角色必须具有 该操作符类所在模式上的CREATE
特权(这些限制强制修改拥有 者不能做一些通过删除和重建操作符类做不到的事情。不过,一个超级用户怎么都 能更改任何操作符类的所有权。)。
参数
name
一个现有操作符类的名称(可以是模式限定的)。
index_method
这个操作符类所服务的索引方法的名称。
new_name
该操作符类的新名称。
new_owner
该操作符类的新拥有者。
new_schema
该操作符类的新模式。
兼容性
在 SQL 标准中没有ALTER OPERATOR CLASS
语句。
参见
CREATE OPERATOR CLASS, DROP OPERATOR CLASS, ALTER OPERATOR FAMILY
20、ALTER OPERATOR FAMILY
ALTER OPERATOR FAMILY — 更改一个操作符族的定义
大纲
ALTER OPERATOR FAMILY name USING index_method ADD{ OPERATOR strategy_number operator_name ( op_type, op_type )[ FOR SEARCH | FOR ORDER BY sort_family_name ]| FUNCTION support_number [ ( op_type [ , op_type ] ) ]function_name [ ( argument_type [, ...] ) ]} [, ... ]ALTER OPERATOR FAMILY name USING index_method DROP{ OPERATOR strategy_number ( op_type [ , op_type ] )| FUNCTION support_number ( op_type [ , op_type ] )} [, ... ]ALTER OPERATOR FAMILY name USING index_methodRENAME TO new_nameALTER OPERATOR FAMILY name USING index_methodOWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER OPERATOR FAMILY name USING index_methodSET SCHEMA new_schema
描述
ALTER OPERATOR FAMILY
更改一个操作符族 的定义。你能增加操作符以及支持函数到该家族、从该族中移除它们或者更改 该族的名称或者拥有者。
在用ALTER OPERATOR FAMILY
增加操作符和 支持函数到一个族中时,它们不是族内任何特定操作符类的组成部分,而只是 “松散”地存在于该族中。这表示这些操作符和函数与该族的语义兼 容,但是没有被任何特定索引的正确功能所要求(所要求的操作符和函数应该 被作为一个操作符类的一部分声明,见CREATE OPERATOR CLASS)。 PostgreSQL将允许一个族的松散成员在 任何时候被从该族中删除,但是在删除一个操作符类的成员之前,必须已经删 除整个类以及依赖于该成员的索引。具有代表性的是,单一数据类型操作符和 函数是操作符类的一部分,因为在特定数据类型上的索引需要它们的支持。而 多数据类型操作符和函数则被作为该族的松散成员。
要使用ALTER OPERATOR FAMILY
,你必须是超级用户( 做这样的限制是因为一个错误的操作符族定义可能会迷惑服务器甚至让它崩溃)。
ALTER OPERATOR FAMILY
目前不检测操作符族 定义是否包括该索引方法所要求的所有操作符和函数,也不检查操作符和函数是 否形成了一个有理的集合。定义一个合法的操作符族是用户的责任。
参数
name
一个现有操作符族的名称(可以是模式限定的)。
index_method
这个操作符族所应用的索引方法的名称。
strategy_number
与该操作符族相关的一个操作符的索引方法策略号。
operator_name
与该操作符族相关的一个操作符的名称(可以是模式限定的)。
op_type
在一个OPERATOR
子句中指定该操作符的操作数数据类型, 或者用NONE
来表示一个左一元或者右一元操作符。不同于 CREATE OPERATOR CLASS
中类似的语法,操作数数据 类型总是必须被指定。
在一个ADD FUNCTION
子句中指定该函数意图支持的操作数 数据类型(如果不同于该函数的输入数据类型)。对于 B-树比较函数和哈希 函数,有必要指定op_type
,因为该函数的输入数据类型 总是正确的。对于 B-树排序支持函数和 GiST、SP-GiST 和 GIN 操作符类中 的所有函数,有必要指定该函数要使用的操作数数据类型。
在一个DROP FUNCTION
子句中,必须指定该函数要支持的操 作数数据类型。
sort_family_name
一个现有btree
操作符族的名称(可能是模式限定的), 它描述与一个排序操作符相关的排序顺序。
如果既没有指定FOR SEARCH
也没有指定FOR ORDER BY
, 默认值是FOR SEARCH
。
support_number
一个与该操作符族相关的函数的索引方法支持过程编号。
function_name
作为该操作符族的一种索引方法支持函数的函数名称(可以是模式限定的)。 如果没有指定参数列表,则该名称必须在其模式中唯一。
argument_type
该函数的参数数据类型。
new_name
该操作符族的新名称。
new_owner
该操作符族的新拥有者。
new_schema
该操作符族的新模式。
OPERATOR
和FUNCTION
子句可以以任何顺序出现。
注解
注意DROP
语法只通过策略或者支持号以及输入数据类型指定该 操作符族中的“slot”。占用这个槽的操作符或函数的名称不会被提及。 还有,对于DROP FUNCTION
,要指定的类型是该函数意图支持 的输入数据类型。对于 GiST、SP-GiST 以及 GIN 索引,可能无需对该函数的 实际输入参数类型做任何事情。
因为索引机制在使用函数之前不会检查其上的访问权限,包括一个操作符族中的 函数或操作符都等同于授予了其上的公共执行权限。这对于操作符族中很有用的 这类函数来说,这通常不成问题。
操作符应该由 SQL 函数定义。一个 SQL 函数很可能被内联到调用查询中,这将 阻止优化器识别出该查询匹配一个索引。
在PostgreSQL 8.4 之前, OPERATOR
子句可以包括一个RECHECK
选项。这不再 被支持,因为一个索引操作符是否为“lossy”现在会在运行时即时决定。 这允许高效地处理一个操作符可能或者不可能为有损的情况。
示例
下列示例命令为一个操作符族增加跨数据类型的操作符和支持函数,该操 作符族已经包含用于数据类型int4
以及int2
的 B-树 操作符类。
ALTER OPERATOR FAMILY integer_ops USING btree ADD-- int4 vs int2OPERATOR 1 < (int4, int2) ,OPERATOR 2 <= (int4, int2) ,OPERATOR 3 = (int4, int2) ,OPERATOR 4 >= (int4, int2) ,OPERATOR 5 > (int4, int2) ,FUNCTION 1 btint42cmp(int4, int2) ,-- int2 vs int4OPERATOR 1 < (int2, int4) ,OPERATOR 2 <= (int2, int4) ,OPERATOR 3 = (int2, int4) ,OPERATOR 4 >= (int2, int4) ,OPERATOR 5 > (int2, int4) ,FUNCTION 1 btint24cmp(int2, int4) ;
再次移除这些项:
ALTER OPERATOR FAMILY integer_ops USING btree DROP-- int4 vs int2OPERATOR 1 (int4, int2) ,OPERATOR 2 (int4, int2) ,OPERATOR 3 (int4, int2) ,OPERATOR 4 (int4, int2) ,OPERATOR 5 (int4, int2) ,FUNCTION 1 (int4, int2) ,-- int2 vs int4OPERATOR 1 (int2, int4) ,OPERATOR 2 (int2, int4) ,OPERATOR 3 (int2, int4) ,OPERATOR 4 (int2, int4) ,OPERATOR 5 (int2, int4) ,FUNCTION 1 (int2, int4) ;
兼容性
在 SQL 标准中没有 ALTER OPERATOR FAMILY
语句。
参见
CREATE OPERATOR FAMILY, DROP OPERATOR FAMILY, CREATE OPERATOR CLASS,DROP OPERATOR CLASS,ALTER OPERATOR CLASS
21、ALTER POLICY
ALTER POLICY — 更改一条行级安全性策略的定义
大纲
ALTER POLICY name ON table_name RENAME TO new_nameALTER POLICY name ON table_name[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ][ USING ( using_expression ) ][ WITH CHECK ( check_expression ) ]
描述
ALTER POLICY
更改一条现有行级安全性策略的定义。 请注意,ALTER POLICY
只允许修改策略所应用的角色集合, 和要修改的USING
和WITH CHECK
表达式。 要更改策略的其他属性,例如其应用的命令,或者是允许还是限制, 则必须删除并重新创建策略。
要使用ALTER POLICY
,你必须拥有该策略所适用的 表。
在ALTER POLICY
的第二种形式中,如果指定了角色列表、 using_expression
以及 check_expression
, 它们会被独立地替换。当这些子句之一被省略时,策略的对应部分不会被更改。
参数
name
要更改的现有策略的名称。
table_name
该策略所在的表的名称(可以被模式限定)。
new_name
该策略的新名称。
role_name
该策略适用的角色。可以一次指定多个角色。要把该策略 应用于所有角色,可使用PUBLIC
。
using_expression
该策略的USING
表达式。详见 CREATE POLICY。
check_expression
该策略的WITH CHECK
表达式。详见 CREATE POLICY。
兼容性
ALTER POLICY
是一种PostgreSQL扩展。
参见
CREATE POLICY,DROP POLICY
22、ALTER PROCEDURE
ALTER PROCEDURE — change the definition of a procedure
大纲
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]action [ ... ] [ RESTRICT ]
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]RENAME TO new_name
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]SET SCHEMA new_schema
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]DEPENDS ON EXTENSION extension_name其中action是下列之一:[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINERSET configuration_parameter { TO | = } { value | DEFAULT }SET configuration_parameter FROM CURRENTRESET configuration_parameterRESET ALL
描述
ALTER PROCEDURE
更改一个过程的定义。
要使用ALTER PROCEDURE
,你必须拥有该过程。要更改一个过程的方案,你还必须有新方案上的CREATE
特权。要更改拥有者,你还必须是新拥有角色的直接或间接成员,并且那个角色在该过程的方案上拥有CREATE
特权(这些限制强制更新拥有者无法做到通过删除和重建该过程无法做到的事情。不过,超级用户总是能够更改任何过程的拥有关系)。
参数
name
一个现有的过程的名字(可以被方案限定)。如果没有指定参数列表,这个名字必须在其方案中唯一。
argmode
参数的模式:IN
或VARIADIC
。如果被省略,默认是IN
。
argname
参数的名字。注意ALTER PROCEDURE
实际上并不关心参数名,因为只需要参数的数据类型来确定过程的身份。
argtype
如果该过程有参数,这是参数的数据类型(可以被方案限定)。
new_name
该过程的新名字。
new_owner
该过程的新拥有者。注意,如果这个过程被标记为SECURITY DEFINER
,接下来它将被作为新拥有者执行。
new_schema
该过程的新方案。
extension_name
该过程所依赖的扩展的名称。
[ EXTERNAL ] SECURITY INVOKER
[ EXTERNAL ] SECURITY DEFINER
更改该过程是否为一个安全性定义器。关键词EXTERNAL
由于SQL符合性的原因被忽略。更多有关这个能力的信息请见CREATE PROCEDURE。
configuration_parameter
value
增加或者更改在调用该过程时,要对一个配置参数做的赋值。如果value
是DEFAULT
或者等效的值,则会使用RESET
,过程本地的设置会被移除,这样该过程的执行就会使用其所处环境中的值。使用RESET ALL
可以清除所有的过程本地设置。SET FROM CURRENT
会把ALTER PROCEDURE
执行时该参数的当前值保存为进入该过程时要被应用的值。
RESTRICT
为了符合SQL标准会被忽略。
示例
要重命名具有两个integer类型参数的过程insert_data为insert_record:
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;要把具有两个integer类型参数的过程insert_data的拥有者改为joe:
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;要重把具有两个integer类型参数的过程insert_data的方案改为accounting:
ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;把过程insert_data(integer, integer)标记为依赖于扩展myext:
ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;要调整一个过程自动设置的搜索路径:
ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;要为一个过程禁用search_path的自动设置:
ALTER PROCEDURE check_password(text) RESET search_path;
现在这个过程将用其调用者所使用的任何搜索路径执行。
兼容性
这个语句与SQL标准中的ALTER PROCEDURE
语句部分兼容。标注你允许修改一个过程的更多性质,但是不提供重命名过程、让过程成为安全性定义器、为过程附加配置参数值或者更改过程的拥有者、方案或者可变性的能力。标准还要求RESTRICT
关键字,而它在PostgreSQL中是可选的。
23、ALTER PUBLICATION
ALTER PUBLICATION — 修改发布的定义
大纲
ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name
描述
命令ALTER PUBLICATION
可以更改发布的属性。
前三个语句更改哪些表是该发布的一部分。SET TABLE
子句用指定的表替换发布中的表的列表。ADD TABLE
和 DROP TABLE
子句将从发布中添加和删除一个或多个表。 请注意,将表添加到已订阅的发布中将需要在订阅端执行ALTER SUBSCRIPTION ... REFRESH PUBLICATION
操作才能生效。
第四条语句可以改变在CREATE PUBLICATION中指定的所有发布属性。 该命令中未提及的属性保留其先前的设置。
其余语句更改所有者和发布的名称。
你必须拥有该发布才能使用ALTER PUBLICATION
。要改变所有者, 你也必须是新所有者角色的直接或间接成员。新的所有者必须在数据库上拥有 CREATE
权限。此外,FOR ALL TABLES
发布的新所有者必须是超级用户。但是, 超级用户可以在避开这些限制的情况下更改发布的所有权。
参数
name
要修改定义的现有发布的名称。
table_name
现有表的名称。如果在表名之前指定了ONLY
,则只有该表受到影响。 如果没有指定ONLY
,则该表及其所有后代表(如果有的话)都会受到影响。 可选地,可以在表名之后指定*
以明确指示包含后代表。
SET (
publication_parameter
[= value
] [, ... ] )
该子句修改最初由CREATE PUBLICATION设置的发布参数。
new_owner
发布的新所有者的用户名。
new_name
发布的新名称。
示例
将发布修改为只发布删除和更新: Change the publication to publish only deletes and updates:
ALTER PUBLICATION noinsert SET (publish = 'update, delete');给发布添加一些表:
ALTER PUBLICATION mypublication ADD TABLE users, departments;
24、ALTER ROLE
ALTER ROLE — 更改一个数据库角色
大纲
ALTER ROLE role_specification [ WITH ] option [ ... ]其中option可以是:SUPERUSER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE| INHERIT | NOINHERIT| LOGIN | NOLOGIN| REPLICATION | NOREPLICATION| BYPASSRLS | NOBYPASSRLS| CONNECTION LIMIT connlimit| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL| VALID UNTIL 'timestamp'ALTER ROLE name RENAME TO new_nameALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL其中role_specification可以是:role_name| CURRENT_USER| SESSION_USER
描述
ALTER ROLE
更改一个 PostgreSQL角色的属性。
前面列出的这个命令的第一种变体能够更改CREATE ROLE中 指定的很多角色属性(覆盖了所有可能的属性,不过没有增加和移除成员关系的选项, 如果要增加和移除成员关系可使用GRANT和 REVOKE)。该命令中没有提到的属性保持它们之前的设置。 数据库超级用户能够更改任何角色的任何这些设置。具有CREATEROLE
特权的角色能够更改任何这些设置,但是只能为非超级用户和非复制角色修改。普通 角色只能更改它们自己的口令。
第二种变体更改该角色的名称。数据库超级用户能重命名任何角色。具有 CREATEROLE
特权的角色能够重命名任何非超级用户角色。当前的会话 用户不能被重命名(如果需要这样做,请以一个不同的用户连接)。由于 MD5
加密的口令使用角色名作为 salt,因此如果一个角色的口令是 MD5
加密的,重命名该角色会清空其口令。
其余的变体用于更改一个角色的配置变量的会话默认值,可以为所有数据库设置,或者 只为IN DATABASE
中指定的数据库设置。如果指定的是 ALL
而不是一个角色名,将会为所有角色更改该设置。把 ALL
和IN DATABASE
一起使用实际上和使用命 令ALTER DATABASE ... SET ...
相同。
只要改角色后续开始一个新会话,指定的值将会成为该会话的默认值,并且会覆盖 postgresql.conf
中存在的值或者从 postgres
命令行收到的值。这只在登录时发生,执行 SET ROLE或者 SET SESSION AUTHORIZATION不会导致新的配置值被设置。 对于所有数据库设置的值会被附加到一个角色的数据库相关的设置所覆盖。特定数 据库或角色的设置会覆盖为所有角色所作的设置。
超级用户能够更改任何人的会话默认值。具有CREATEROLE
特权的角色 能够更改非超级用户的默认值。普通角色只能为它们自己设置默认值。某些配置变量 不能以这种方式设置,或者只能由一个超级用户发出的命令设置。只有超级用户能够 更改所有角色在所有数据库中的设置。
参数
name
要对其属性进行修改的角色的名称。
CURRENT_USER
修改当前用户而不是一个显式标识的角色。
SESSION_USER
修改当前会话用户而不是一个显式标识的角色。
SUPERUSER
NOSUPERUSER
CREATEDB
NOCREATEDB
CREATEROLE
NOCREATEROLE
INHERIT
NOINHERIT
LOGIN
NOLOGIN
REPLICATION
NOREPLICATION
BYPASSRLS
NOBYPASSRLS
CONNECTION LIMIT
connlimit
[ ENCRYPTED
] PASSWORD
'password
' PASSWORD NULL
VALID UNTIL
'timestamp
'
这些子句修改原来有CREATE ROLE 设置的属性。更多信息请见 CREATE ROLE
参考页。
new_name
该角色的新名称。
database_name
要在其中设置该配置变量的数据库名称。
configuration_parameter
value
把这个角色的指定配置参数的会话默认值设置为给定值。如果 value
为DEFAULT
或者等效地使用了RESET
,角色相关的变量 设置会被移除,这样该角色将会在新会话中继承系统范围的默认 设置。使用RESET ALL
可清除所有角色相关的 设置。SET FROM CURRENT
可以把会话中该参数的 当前值保存为角色相关的值。如果指定了 IN DATABASE
,只会为给定的角色和数据库 设置或者移除该配置参数。
角色相关的变量设置只在登录时生效, SET ROLE以及 SET SESSION AUTHORIZATION不会处理角色 相关的变量设置。
注解
使用CREATE ROLE增加新角色,使用 DROP ROLE移除一个角色。
ALTER ROLE
无法更改一个角色成员关系。 可以使用GRANT和 REVOKE来实现。
在使用这个命令指定一个未加密口令时要多加小心。该口令将会以明文 传送到服务器,并且它还可能会被记录在客户端的命令历史或者服务器 日志中。psql包含了一个命令 \password
,它可以被用来更改一个角色 的口令而不暴露明文口令。
也可以把一个会话默认值绑定到一个指定的数据库而不是一个角色,详见 ALTER DATABASE。如果出现冲突,数据库角色相关 的设置会覆盖角色相关的设置,角色相关的又会覆盖数据库相关的设置。
示例
更改一个角色的口令:
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';移除一个角色的口令:
ALTER ROLE davide WITH PASSWORD NULL;更改一个口令的失效日期,指定该口令应该在 2015 年 5 月 4 日中午 (在一个比UTC快 1 小时的时区)过期:
ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';让一个口令永远有效:
ALTER ROLE fred VALID UNTIL 'infinity';让一个角色能够创建其他角色和新的数据库:
ALTER ROLE miriam CREATEROLE CREATEDB;为一个角色指定 maintenance_work_mem参数的非默认设置:
ALTER ROLE worker_bee SET maintenance_work_mem = 100000;为一个角色指定 client_min_messages参数的数据库相关的非 默认设置:
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
25、ALTER ROUTINE
ALTER ROUTINE — 更改一个例程的定义
大纲
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]action [ ... ] [ RESTRICT ]
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]RENAME TO new_name
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]SET SCHEMA new_schema
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]DEPENDS ON EXTENSION extension_name其中action是下列之一:IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINERPARALLEL { UNSAFE | RESTRICTED | SAFE }COST execution_costROWS result_rowsSET configuration_parameter { TO | = } { value | DEFAULT }SET configuration_parameter FROM CURRENTRESET configuration_parameterRESET ALL
描述
ALTER ROUTINE
更改一个例程的定义,它可以是聚集函数、普通函数或者过程。参数的描述、更多的例子以及进一步的细节请参考ALTER AGGREGARE,ALTER FUNCTION以及ALTER PROCEDURE。
示例
将类型integer的例程foo重命名为foobar:
ALTER ROUTINE foo(integer) RENAME TO foobar;
不管foo
是聚集、函数还是过程,这个命令都能使用。
26、ALTER RULE
ALTER RULE — 更改一个规则定义
大纲
ALTER RULE name ON table_name RENAME TO new_name
描述
ALTER RULE
更改一条现有规则的定义。当前,唯一可用的 动作是更改规则的名称。
要使用ALTER RULE
,你必须拥有该规则适用的表或者视图。
参数
name
要修改的一条现有规则的名称。
table_name
该规则适用的表或视图的名称(可以是模式限定的)。
new_name
该规则的新名称。
示例
要重命名一条现有的规则:
ALTER RULE notify_all ON emp RENAME TO notify_me;
27、ALTER SCHEMA
ALTER SCHEMA — 更改一个模式的定义
大纲
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
描述
ALTER SCHEMA
更改一个模式的定义。
要使用ALTER SCHEMA
,你必须拥有该模式。要重命名一个模式, 你还必须拥有该数据库的CREATE
特权。要更改拥有者,你还必须 是新拥有角色的一个直接或者间接成员,并且该角色必须具有该数据库上的 CREATE
特权(注意超级用户自动拥有所有这些特权)。
参数
name
一个现有模式的名称。
new_name
该模式的新名称。新名称不能以pg_
开始,因为这些名称被 保留用于系统模式。
new_owner
该模式的新拥有者。
28、ALTER SEQUENCE
ALTER SEQUENCE — 更改一个序列发生器的定义
大纲
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
minvalue
NO MINVALUE
可选的子句MINVALUE
决定一个序列 能产生的最小值。如果指定了minvalue
NO MINVALUE
,上升序列和下降序列的默认值分别是 1 和 数据类型的最小值。如果这些选项都没有被指定,将保持当前的 最小值。
maxvalue
NO 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
使得序列数字被预先 分配并且保存在内存中以便更快的访问。最小值是 1(每次只产生一个值,即 无缓存)。如果没有指定,旧的缓冲值将被保持。cache
CYCLE
可选的CYCLE
关键词可以被用来允许该序列在达到 maxvalue
(上升序列)或 minvalue
(下降序列)时 回卷。如果到达该限制,下一个被产生的数字将分别是 minvalue
或者 maxvalue
。
NO CYCLE
如果指定了可选的NO CYCLE
关键词,任何在该 序列到达其最大值后的nextval
调用将会返回 一个错误。如果既没有指定CYCLE
也没有指定 NO CYCLE
,旧的循环行为将被保持。
OWNED BY
table_name
.column_name
OWNED BY NONE
OWNED 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的序列:ALTER SEQUENCE serial RESTART WITH 105;
29、ALTER SERVER
ALTER SERVER — 更改一个外部服务器的定义
大纲
ALTER SERVER name [ VERSION 'new_version' ][ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
ALTER SERVER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SERVER name RENAME TO new_name
描述
ALTER SERVER
更改一个外部服务器的定义。 第一种形式更改该服务器的版本字符串或者该服务器的一般选项(至少要求 一个子句)。第二种形式更改该服务器的拥有者。
要修改该服务器,你必须是它的拥有者。此外为了修改拥有者,你必须拥有 该服务器并且是新拥有角色的一个直接或者间接成员,并且你必须具有该服 务器的外部数据包装器上的USAGE
特权(注意超级用户自动 满足所有这些政策)。
参数
name
一个现有服务器的名称。
new_version
新的服务器版本。
OPTIONS ( [ ADD | SET | DROP ]
option
['value
'] [, ... ] )
更改该服务器的选项。ADD
、SET
和 DROP
指定要执行的动作。如果没有显式地指定操作, 将会假定为ADD
。选项名称必须唯一,名称和值也会 使用该服务器的外部数据包装器库进行验证。
new_owner
该外部服务器的新拥有者的用户名。
new_name
该外部服务器的新名称。
示例
修改服务器foo,增加连接选项:
ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');修改服务器foo,更改版本、更改host选项:
ALTER SERVER foo VERSION '8.4' OPTIONS (SET host 'baz');
ALTER SERVER
符合 ISO/IEC 9075-9 (SQL/MED)。 OWNER TO
和RENAME
形式是 PostgreSQL 扩展。
30、ALTER STATISTICS
ALTER STATISTICS — 更改扩展统计对象的定义
大纲
ALTER STATISTICS name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER STATISTICS name RENAME TO new_name
ALTER STATISTICS name SET SCHEMA new_schema
描述
ALTER STATISTICS
更改现有扩展统计对象的参数。 任何在ALTER STATISTICS
命令中没有明确设定的参数保持它们之前的设置。
您必须拥有统计对象才能使用ALTER STATISTICS
。 要更改统计对象的模式,还必须在新模式上具有CREATE
权限。 要更改所有者,还必须是新所有者角色的直接或间接成员, 且该角色在统计对象的模式上必须具有CREATE
权限。 (这些限制强制了通过删除和重新创建统计对象来改变所有者不会做任何你不能做的事情, 但是超级用户可以改变任何统计对象的所有权。)
参数
name
要修改的统计对象的名称(可能有模式修饰)。
new_owner
统计对象的新所有者的用户名。
new_name
统计对象的新名称。
new_schema
统计对象的新模式。
SQL标准中没有ALTER STATISTICS
命令。
31、ALTER SUBSCRIPTION
ALTER SUBSCRIPTION — 修改订阅的定义
大纲
ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name
描述
ALTER SUBSCRIPTION
可以修改大部分可以在 CREATE SUBSCRIPTION中指定的订阅属性。
要使用ALTER SUBSCRIPTION
,你必须拥有该订阅。要改变所有者, 你也必须是新所有者的直接或间接成员。新所有者必须是超级用户。(目前, 所有的订阅所有者必须是超级用户,所以所有者的检查将在实践中被绕过, 但这可能在未来发生变化。)
参数
name
要修改属性的订阅的名称。
CONNECTION '
conninfo
'
该子句修改最初由CREATE SUBSCRIPTION设置的连接属性。
SET PUBLICATION
publication_name
更改订阅发布的列表。参阅CREATE SUBSCRIPTION 获取更多信息。默认情况下,此命令也将像REFRESH PUBLICATION
一样工作。
set_publication_option
指定了这个操作的附加选项。 支持的选项是:
refresh
(boolean
)
如果为false,则该命令将不会尝试刷新表信息。然后应单独执行 REFRESH PUBLICATION
。默认值是true
。
此外,可以指定REFRESH PUBLICATION
下描述的刷新选项。
REFRESH PUBLICATION
从发布者获取缺少的表信息。这将开始复制自上次调用REFRESH PUBLICATION
或从CREATE SUBSCRIPTION
以来添加到订阅发布中的表。
refresh_option
指定了刷新操作的附加选项。 支持的选项有:
copy_data
(boolean
)
指定在复制启动后是否应复制正在订阅的发布中的现有数据。 默认值是true
。
ENABLE
启用先前禁用的订阅,在事务结束时启动逻辑复制工作。
DISABLE
禁用正在运行的订阅,在事务结束时停止逻辑复制工作。
SET (
subscription_parameter
[= value
] [, ... ] )
该子句修改原先由CREATE SUBSCRIPTION设置的参数。 允许的选项是slot_name
和synchronous_commit
。
new_owner
订阅的新所有者的用户名。
new_name
订阅的新名称。
示例
将订阅的发布更改为insert_only:
ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;禁用(停止)订阅:
ALTER SUBSCRIPTION mysub DISABLE;
32、ALTER SYSTEM
ALTER SYSTEM — 更改一个服务器配置参数
大纲
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
描述
ALTER SYSTEM
被用来在整个数据库集簇范围内更改 服务器配置参数。它比传统的手动编辑postgresql.conf
文件的方法更方便。ALTER SYSTEM
会把给出的参数 设置写入到postgresql.auto.conf
文件中,该文件会随着 postgresql.conf
一起被读入。把一个参数设置为 DEFAULT
或者使用RESET
变体可以 把该配置项从postgresql.auto.conf
文件中移除。使用 RESET ALL
可以移除所有这类配置项。
用ALTER SYSTEM
设置的值将在下一次重载服务器 配置后生效,那些只能在服务器启动时更改的参数则会在下一次服务器重启后生效。 重载服务器配置可以通过以下做法实现:调用 SQL 函数pg_reload_conf()
, 运行pg_ctl reload
或者向主服务器进程发送一个SIGHUP信号。
只有超级用户能够使用ALTER SYSTEM
。还有,由于 这个命令直接作用于文件系统并且不能被回滚,不允许在一个事务块或者函数中使用它。
参数
configuration_parameter
一个可设置配置参数的名称。
value
该参数的新值。值可以被指定为字符串常量、标识符、数字或者以上这些构成的 逗号分隔的列表,值的具体形式取决于特定的参数。写上 DEFAULT
可以用来把该参数及其值从 postgresql.auto.conf
中移除。
注解
不能用这个命令来设置data_directory以及 postgresql.conf
中不被允许的参数(例如 preset options)。
示例
设置wal_level:
ALTER SYSTEM SET wal_level = replica;撤销以上的设置,恢复postgresql.conf中有效的设置:
ALTER SYSTEM RESET wal_level;
33、ALTER TABLE
ALTER TABLE — 更改一个表的定义
大纲
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] nameRENAME TO new_name
ALTER TABLE [ IF EXISTS ] nameSET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] nameATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] nameDETACH PARTITION partition_name其中action 是以下之一:ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]ALTER [ COLUMN ] column_name SET DEFAULT expressionALTER [ COLUMN ] column_name DROP DEFAULTALTER [ COLUMN ] column_name { SET | DROP } NOT NULLALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]ALTER [ COLUMN ] column_name SET STATISTICS integerALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }ADD table_constraint [ NOT VALID ]ADD table_constraint_using_indexALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]VALIDATE CONSTRAINT constraint_nameDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]DISABLE TRIGGER [ trigger_name | ALL | USER ]ENABLE TRIGGER [ trigger_name | ALL | USER ]ENABLE REPLICA TRIGGER trigger_nameENABLE ALWAYS TRIGGER trigger_nameDISABLE RULE rewrite_rule_nameENABLE RULE rewrite_rule_nameENABLE REPLICA RULE rewrite_rule_nameENABLE ALWAYS RULE rewrite_rule_nameDISABLE ROW LEVEL SECURITYENABLE ROW LEVEL SECURITYFORCE ROW LEVEL SECURITYNO FORCE ROW LEVEL SECURITYCLUSTER ON index_nameSET WITHOUT CLUSTERSET WITH OIDSSET WITHOUT OIDSSET TABLESPACE new_tablespaceSET { LOGGED | UNLOGGED }SET ( storage_parameter = value [, ... ] )RESET ( storage_parameter [, ... ] )INHERIT parent_tableNO INHERIT parent_tableOF type_nameNOT OFOWNER TO { new_owner | CURRENT_USER | SESSION_USER }REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }and partition_bound_spec is:IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )and column_constraint is:[ CONSTRAINT constraint_name ]
{ NOT NULL |NULL |CHECK ( expression ) [ NO INHERIT ] |DEFAULT default_expr |GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |UNIQUE index_parameters |PRIMARY KEY index_parameters |REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ][ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]而table_constraint是:[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |UNIQUE ( column_name [, ... ] ) index_parameters |PRIMARY KEY ( column_name [, ... ] ) index_parameters |EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ][ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]并且 table_constraint_using_index 是:[ CONSTRAINT constraint_name ]{ UNIQUE | PRIMARY KEY } USING INDEX index_name[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]UNIQUE、PRIMARY KEY以及EXCLUDE约束中的index_parameters是:[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]exclude_element in an EXCLUDE constraint is:{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
描述
ALTER TABLE
更改一个现有表的定义。下文描述了 几种形式。注意每一种形式所要求的锁级别可能不同。如果没有明确说明,将会 持有一个ACCESS EXCLUSIVE
所。当列出多个子命令时,所 持有的锁将是子命令所要求的最严格的那一个。
ADD COLUMN [ IF NOT EXISTS ]
这种形式向该表增加一个新列,使用与 CREATE TABLE相同的语法。如果指定了 IF NOT EXISTS
并且使用这个名字的列已经存在,则 不会抛出错误。
DROP COLUMN [ IF EXISTS ]
这种形式从表删除一列。涉及到该列的索引和表约束也将会被自动 删除。如果该列的移除会导致引用它的多元统计信息仅包含单一列的数据,则该多元统计信息也将被移除。如果在该表之外有任何东西(例如外键引用或者视图)依赖 于该列,你将需要用到CASCADE
。如果指定了 IF EXISTS
但该列不存在,则不会抛出错误。 这种情况中会发出一个提示。
SET DATA TYPE
这种形式更改表中一列的类型。涉及到该列的索引和简单表约束将通过 重新解析最初提供的表达式被自动转换为使用新的列类型。可选的 COLLATE
子句为新列指定一种排序规则,如果被省略, 排序规则会是新列类型的默认排序规则。可选的USING
子句指定如何从旧的列值计算新列值,如果被省略,默认的转换和从旧类型 到新类型的赋值造型一样。如果没有从旧类型到新类型的隐式或者赋值造型, 则必须提供一个USING
子句。
SET
/DROP DEFAULT
这些形式为一列设置或者移除默认值。默认值只在后续的 INSERT
或UPDATE
命令中生效, 它们不会导致已经在表中的行改变。
SET
/DROP NOT NULL
这些形式更改一列是否被标记为允许空值或者拒绝空值。只有当该列 不包含空值时,你才能使用SET NOT NULL
。
如果这个表是一个分区,对于在父表中被标记为NOT NULL
的列,不能在其上执行DROP NOT NULL
。要从所有的分区中删除NOT NULL
约束,可以在父表上执行DROP NOT NULL
。即使在父表上没有NOT NULL
约束,这样的约束还是能被增加到分区上。也就是说,即便父表允许空值,子表也可以不允许空值,但反过来不行。
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]
这些形式更改一列是否是一个标识列,或者是更改一个已有的标识列的产生属性。详情请参考CREATE TABLE。
如果DROP IDENTITY IF EXISTS
被指定并且该列不是一个标识列,则不会有错误被抛出。在这种情况下会发出一个提示。
SET
sequence_option
RESTART
这些形式修改位于一个现有标识列之下的序列。sequence_option
是一个ALTER SEQUENCE所支持的选项,例如INCREMENT BY
。
SET STATISTICS
这种形式为后续的ANALYZE操作设置针对每列 的统计收集目标。目标可以被设置在范围 0 到 10000 之间,还可以 把它设置为 -1 来恢复到使用系统默认的统计目标( default_statistics_target)。
SET STATISTICS
要求一个SHARE UPDATE EXCLUSIVE
锁。
SET (
attribute_option
= value
[, ... ] ) RESET (
attribute_option
[, ... ] )
这种形式设置或者重置每个属性的选项。当前,已定义的针对每个属性的 选项只有n_distinct
和n_distinct_inherited
, 它们会覆盖后续ANALYZE操作所得到的可区分值数量 估计。n_distinct
影响该表本身的统计信息,而 n_distinct_inherited
影响为该表外加其继承子女收集的统计信息。 当被设置为一个正值时,ANALYZE
将假定该列刚好包含指定 数量的可区分非空值。当被设置为一个负值(必须大于等于 -1)时, ANALYZE
将假定可区分非空值的数量与表的尺寸成线性比例, 确切的计数由估计的表尺寸乘以给定数字的绝对值计算得到。例如,值 -1 表示 该列中所有的值都是可区分的,而值 -0.5 则表示每一个值平均出现两次。当表 的尺寸随时间变化时,这会有所帮助,因为这种计算只有在查询规划时才会被 执行。指定值为 0 将回到正常的估计可区分值数量的做法。
更改针对每个属性的选项要求一个 SHARE UPDATE EXCLUSIVE
锁。
这种形式为一列设置存储模式。这会控制这列是会被保持在线内还是放在一个 二级TOAST表中,以及数据是否应被压缩。对于 integer
之类的定长、线内、未压缩值必须使用 PLAIN
。MAIN
用于线内、可压缩的 数据。EXTERNAL
用于外部的、未压缩数据。而 EXTENDED
用于外部的、压缩数据。对于大部分支持 非-PLAIN
存储的数据类型,EXTENDED
是默认值。使用EXTERNAL
将会让很大的 text
和bytea
之上的子串操作运行得更快, 但是代价是存储空间会增加。注意SET STORAGE
本身并不改变 表中的任何东西,它只是设置在未来的表更新时要追求的策略。
ADD
table_constraint
[ NOT VALID ]
这种形式使用和CREATE TABLE相同的语法外加 NOT VALID
选项为一个表增加一个新的约束,该选项 当前只被允许用于外键和 CHECK 约束。如果约束被标记为 NOT VALID
,将会跳过验证表中所有行满足该约束的 初检,这种检查可能会很漫长。该约束仍将被强制到后续的插入和删除上 (也就是说,在外键的情况下如果在被引用表中没有一个匹配的行,操作 会失败;如果新行不匹配指定的检查约束,操作也会失败)。但是数据库 不会假定约束对该表中的所有行都成立,直到通过使用VALIDATE CONSTRAINT
选项对它进行验证。当前,分区表上的外键约束不可以被声明为NOT VALID
。
外键约束的增加要求在被引用表上的一个SHARE ROW EXCLUSIVE
锁。
当唯一或者主键约束被添加到分区表时,会有额外的限制,请参考CREATE TABLE。
ADD
table_constraint_using_index
这种形式基于一个已有的唯一索引为一个表增加新的 PRIMARY KEY
或UNIQUE
约束。该索引中的 所有列将被包括在约束中。
该索引不能有表达式列或者是一个部分索引。还有,它必须是一个带有 默认排序顺序的 B-树索引。这些限制确保该索引等效于使用常规 ADD PRIMARY KEY
或者ADD UNIQUE
命令 时创建的索引。
如果PRIMARY KEY
被指定,并且该索引的列没有被标记 NOT NULL
,那么这个命令将尝试对每一个这样的列做 ALTER COLUMN SET NOT NULL
。这需要一次全表扫描 来验证这些列不包含空值。在所有其他情况中,这都是一种很快的操作。
如果提供了一个约束名,那么该索引将被重命名以匹配该约束名。否则 该约束将被命名成索引的名称。
这个命令被执行后,该索引被增加的约束“拥有”,这和用常规 ADD PRIMARY KEY
或ADD UNIQUE
命令 创建的索引一样。特别地,删掉该约束将会导致该索引也消失。
当前在分区表上不支持这种形式。
注意:
如果需要增加一个新的约束但是不希望长时间阻塞表更新,那么使用现有 索引增加约束会有所帮助。要这样做,用
CREATE INDEX CONCURRENTLY
创建该索引,并且 接着使用这种语法把它安装为一个正式的约束。例子见下文。
ALTER CONSTRAINT
这种形式修改之前创建的一个约束的属性。当前只能修改外键约束。
VALIDATE CONSTRAINT
这种形式验证之前创建为NOT VALID
的外键或检查约束, 它会扫描表来确保对于该约束没有行不满足约束。如果约束已经被标记为合法, 则什么也不会发生。
在大型表上的验证可能是一个长时间的处理。把约束的验证和创建分离开来让 我们可以把验证过程推迟到系统闲时进行,或者可以得到额外的时间来更正已 经存在的错误从而避免新的错误。还要注意验证本身并不会在运行时阻止对表 的写命令。
验证只要求被修改表上的一个SHARE UPDATE EXCLUSIVE
锁。如果该约束是一个外键,则还会在被该约束引用的表上要求一个 ROW SHARE
锁。
DROP CONSTRAINT [ IF EXISTS ]
这种形式在一个表上删除指定的约束,还有位于该约束之下的任何索引。如果IF EXISTS
被指定并且该约束不存在,不会抛出错误。在这种情况下会发出一个提示。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] TRIGGER
这些形式配置属于该表的触发器的触发设置。系统仍然知道被禁用触发器 的存在,但是即使它的触发事件发生也不会执行它。对于一个延迟触发器, 会在事件发生时而不是触发器函数真正被执行时检查其启用状态。可以禁 用或者启用用名称指定的单个触发器、表上的所有触发器、用户拥有的触 发器(这个选项会排除内部生成的约束触发器,例如用来实现外键约束或 可延迟唯一和排除约束)。禁用或者启用内部生成的约束触发器要求超级 用户特权,这样做要小心因为如果这类触发器不被执行,约束的完整性当 然无法保证。
触发器引发机制也受到配置变量 session_replication_role的影响。当复制角色是 “origin”(默认)或者“local”时,被简单启用的 触发器将被触发。被配置为ENABLE REPLICA
的触发 器只有在会话处于“replica”模式时才将被触发。被配置为 ENABLE ALWAYS
的触发器的触发不会考虑当前复制 角色。
这种机制的效果就是,在默认配置中,触发器不会在复制体上引发。这种效果很有用,因为如果一个触发器在源头上被用来在表之间传播数据,那么复制系统也将复制被传播的数据,并且触发器不应该在复制体上引发第二次,因为那会导致重复。不过,如果一个触发器被用于另一种目的(例如创建外部告警),那么将它设置为ENABLE ALWAYS
可能更加合适,这样它在复制体上也会被引发。
这个命令要求一个SHARE ROW EXCLUSIVE
锁。
DISABLE
/ENABLE [ REPLICA | ALWAYS ] RULE
这些形式配置属于表的重写规则的触发设置。系统仍然知道一个被禁用规则的 存在,但在查询重写时不会应用它。其语义与禁用的/启用的触发器的一样。 对于ON SELECT
规则会忽略这个配置,即使当前会话处于 一种非默认的复制角色,这类规则总是会被应用以保持视图工作正常。
规则引发机制也受到配置变量session_replication_role的影响,这和上述的触发器类似。
DISABLE
/ENABLE ROW LEVEL SECURITY
这些形式控制属于该表的行安全性策略的应用。如果被启用并且该表上 不存在策略,则将应用一个默认否定的策略。注意即使行级安全性被禁 用,在表上还是可以存在策略。在这种情况下,这些策略将 不 会被应用 并且会被忽略。另见CREATE POLICY。
NO FORCE
/FORCE ROW LEVEL SECURITY
这些形式控制当用户是表拥有者时表上的行安全性策略的应用。如果被启用, 当用户是表拥有者时,行级安全性策略将被应用。如果被禁用(默认),则 当用户是表拥有者时,行级安全性将不会被应用。另见 CREATE POLICY。
CLUSTER ON
这种形式为未来的CLUSTER
操作选择默认的索引。 它不会真正地对表进行聚簇。
改变聚簇选项要求一个SHARE UPDATE EXCLUSIVE
锁。
SET WITHOUT CLUSTER
这种形式从表中移除最近使用的 CLUSTER
索引说明。这会影响未来的不指定索引 的聚簇操作。
改变聚簇选项要求一个SHARE UPDATE EXCLUSIVE
锁。
SET WITH OIDS
这种形式为表增加一个oid
系统列。如果该表已经有 OID,则它 什么也不会做。
注意这不等效于ADD COLUMN oid oid
,后者只是会增加一个 恰好名为oid
的普通列而不是系统列。
SET WITHOUT OIDS
这种形式从该表移除oid
系统列。这完全等效于 DROP COLUMN oid RESTRICT
,不过如果没有 oid
列它不会抱怨。
SET TABLESPACE
这种形式把该表的表空间更改为指定的表空间并且把该表相关联的数据文件 移动到新的表空间中。表上的索引(如果有)不会被移动,但是它们可以用 额外的SET TABLESPACE
命令单独移动。当前数据库在 一个表空间中的所有表可以用ALL IN TABLESPACE
形式 移动,这将会首先锁住所有将被移动的表然后逐个移动。这种形式也支持 OWNED BY
,它将只移动指定角色所拥有的表。如果指 定了NOWAIT
选项,则命令将在无法立刻获得所有所需 要的锁时失败。注意这个命令不移动系统目录,如果想要移动系统目录,应 该用ALTER DATABASE
或者显式的 ALTER TABLE
调用。对于这种形式来说, information_schema
关系不被认为是系统目录的一部分, 因此它们将会被移动。另见CREATE TABLESPACE。
SET { LOGGED | UNLOGGED }
This form changes the table from unlogged to logged or vice-versa (see UNLOGGED). It cannot be applied to a temporary table.
SET (
storage_parameter
= value
[, ... ] )
这种形式为该表更改一个或者更多存储参数。可用的参数请见 存储参数。注意这个 命令将不会立刻修改表内容,这取决于重写表以得到想要的结果可能需要的 参数。可以用VACUUM FULL、CLUSTER或者 ALTER TABLE
的一种形式来强制一次表重写。对于规划器相关的参数,更改将从该表下一次被锁定开始生效,因此当前执行的查询不会受到影响。
对fillfactor、toast以及autovacuum存储参数,还有下面的规划器相关参数,将会拿取SHARE UPDATE EXCLUSIVE
锁: effective_io_concurrency
、parallel_workers
、seq_page_cost
、random_page_cost
、n_distinct
以及n_distinct_inherited
。
注意:
虽然
CREATE TABLE
允许在WITH (
语法中指定storage_parameter
)OIDS
,但是ALTER TABLE
没有把OIDS
当作一个存储 参数,而是使用SET WITH OIDS
和SET WITHOUT OIDS
形式来更改 OID 状态。
RESET (
storage_parameter
[, ... ] )
这种形式把一个或者更多存储参数重置到它们的默认值。和 SET
一样,可能需要一次表重写来更新整个表。
INHERIT
parent_table
这种形式把目标表增加为指定父表的一个新子女。随后,针对父亲的查询将 包括目标表中的记录。要被增加为一个子女,目标表必须已经包含和父表完 全相同的列(也可以有额外的列)。这些列必须具有匹配的数据类型,并且 如果它们在父表中具有NOT NULL
约束,它们在子表中 也必须有NOT NULL
约束。
也必须把子表约束与所有父表的CHECK
约束进行匹配, 不过父表中那些被标记为非可继承(也就是用ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT
创建的)除外,它们会被忽略。所有匹配得上的子表约束不能被标记为不可 继承。当前,UNIQUE
、PRIMARY KEY
以及FOREIGN KEY
约束没有被考虑,但是这种情况可能 会在未来发生变化。
NO INHERIT
parent_table
这种形式把目标表从指定父表的子女列表中移除。针对父表的查询将不再包括 来自目标表的记录。
OF
type_name
这种形式把该表链接到一种组合类型,就好像CREATE TABLE OF
所做的那样。该表的列名和类型列表必须精确地匹配 该组合类型。oid
系统列的存在情况可以不同。该表必须 不从任何其他表继承。这些限制确保 CREATE TABLE OF
能允许一个等价的表定义。
NOT OF
这种形式解除一个有类型的表和其类型之间的关联。
OWNER TO
这种形式把表、序列、视图、物化视图或外部表的拥有者改为指定用户。
REPLICA IDENTITY
这种形式更改被写入到预写式日志来标识被更新或删除行的信息。除非使用逻辑复制, 这个选项将不会产生效果。DEFAULT
(非系统表的默认值)记录主键列 (如果有)的旧值。USING INDEX
记录被所提到的索引所覆盖的列的 旧值,该索引必须是唯一索引、不是部分索引、不是可延迟索引并且只包括被标记成 NOT NULL
的列。FULL
记录行中所有列的旧值。 NOTHING
不记录有关旧行的任何信息(这是系统表的默认值)。在所 有情况下,除非至少有一个要被记录的列在新旧行版本之间发生变化,将不记录旧值。
RENAME
RENAME
形式更改一个表(或者一个索引、序列、视图、物化视图 或者外部表)的名称、表中一个列的名称或者表的一个约束的名称。在重命名一个具有底层索引的约束时,该索引也会被重命名。它对已存储的数据 没有影响。
SET SCHEMA
这种形式把该表移动到另一个模式中。相关的该表列拥有的索引、约束和序列也会被 移动。
ATTACH PARTITION
partition_name
{ FOR VALUES partition_bound_spec
| DEFAULT }
这种形式把一个已有表(自身也可能被分区)作为一个分区挂接到目标表。该表可以为特定的值使用FOR VALUES
挂接为分区,或者用DEFAULT
挂接为一个默认分区。对于目标表中的每一个索引,在被挂接的表上都将创建一个响应的索引,如果已经存在等效的索引,该索引将被挂接到目标表的索引,就像执行了ALTER INDEX ATTACH PARTITION
一样。
一个使用FOR VALUES
的分区使用与CREATE TABLE中partition_bound_spec
相同的语法。分区边界说明必须对应于目标表的分区策略以及分区键。要被挂接的表必须具有和目标表完全相同的所有列,并且不能有多出来的列,而且列的类型也必须匹配。此外,它必须有目标表上所有的NOT NULL
以及CHECK
约束。当前不考虑FOREIGN KEY
约束。来自于父表的UNIQUE
和PRIMARY KEY
约束将被创建在分区上(如果它们还不存在)。如果被挂接的表上的任何CHECK
约束被标记为NO INHERIT
,则命令将失败,这类约束必须被重建且重建时不能有NO INHERIT
子句。
如果新分区是一个常规表,会执行一次全表扫描来检查表中没有现有行违背分区约束。可以通过对表增加一个有效的CHECK
约束来避免这种扫描,该约束可以在运行这个命令之前仅允许满足所需分区约束的行。 使用这样一个约束,就可以让表无需被扫描就能验证分区约束。但是,如果任一分区键是一个表达式并且该分区不接受NULL
值,这种方式就无效了。如果挂接一个不接受NULL
值的列表分区,还应该为分区键列增加NOT NULL
约束,除非它是一个表达式。
如果新分区是一个外部表,则不需要验证该外部表中的所有行遵守分区约束(有关外部表上的约束请参考CREATE FOREIGN TABLE中的讨论)。
当一个表有默认分区时,定义新分区会更改默认分区的分区约束。默认分区不能包含任何需要被移动到新分区中的行,并且将被扫描以验证不存在那样的行。如果一个合适的CHECK
约束存在,这种扫描(和新分区的扫描一样)可以被避免。还是和新分区的扫描一样,当默认分区是外部表时这种扫描总是会被跳过。
DETACH PARTITION
partition_name
这种形式会分离目标表的指定分区。被分离的分区继续作为独立的表存在,但是与它之前挂接的表不再有任何联系。任何被挂接到目标表索引的索引也会被分离。
除了RENAME
、SET SCHEMA
、ATTACH PARTITION
和DETACH PARTITION
之外,所有形式的ALTER TABLE都作用在单个表上,前面这些形式可以被组合成一个多修改的列表被一起应用。例如,可以在一个命令中增加多个列并且/或者修改多个列的类型。对于大型表来说这会特别有用,因为只需要对表做一趟操作。
要使用ALTER TABLE
,你必须拥有该表。要更改一个表的 模式或者表空间,你还必须拥有新模式或表空间上的 CREATE
特权。要把一个表作为一个父表的新子表加入, 你必须也拥有该父表。此外,要把一个表挂接为另一个表的新分区,你必须拥有被挂接的表。要更改拥有者,你还必须 是新拥有角色的一个直接或者间接成员,并且该角色必须具有该表的模式上的 CREATE
特权(这些限制强制修改拥有者不能做一些通过删除和重 建表做不到的事情。不过,一个超级用户怎么都能更改任何表的所有权。)。 要增加一个列、修改一列的类型或者使用OF
子句,你还必 须具有该数据类型上的USAGE
特权。
参数
IF EXISTS
如果表不存在则不要抛出一个错误。这种情况下会发出一个提示。
name
要修改的一个现有表的名称(可以是模式限定的)。如果在表名前指定了 ONLY
,则只会修改该表。如果没有指定ONLY
, 该表及其所有后代表(如果有)都会被修改。可选地,在表名后面可以指定 *
用来显式地指示包括后代表。
column_name
一个新列或者现有列的名称。
new_column_name
一个现有列的新名称。
new_name
该表的新名称。
data_type
一个新列的数据类型或者一个现有列的新数据类型。
table_constraint
该表的新的表约束。
constraint_name
一个新约束或者现有约束的名称。
CASCADE
自动删除依赖于被删除列或约束的对象(例如引用该列的视图), 并且接着删除依赖于那些对象的 所有对象。
RESTRICT
如果有任何依赖对象时拒绝删除列或者约束。这是默认行为。
trigger_name
一个要禁用或启用的触发器的名称。
ALL
禁用或者启用属于该表的所有触发器(如果有任何触发器是内部产生的约 束触发器则需要超级用户特权,例如那些被用来实现外键约束或者可延迟 一致性和排他约束的触发器)。
USER
禁用或者启用属于该表的所有触发器,内部产生的约束触发器(例如那些 被用来实现外键约束或者可延迟一致性和排他约束的触发器)除外。
index_name
一个现有索引的名称。
storage_parameter
一个表存储参数的名称。
value
一个表存储参数的新值。根据该参数,该值可能是一个数字或者一个词。
parent_table
要与这个表关联或者解除关联的父表。
new_owner
该表的新拥有者的用户名。
new_tablespace
要把该表移入其中的表空间的名称。
new_schema
要把该表移入其中的模式的名称。
partition_name
要被作为新分区附着到这个表或者从这个表上分离的表的名称。
partition_bound_spec
新分区的分区边界说明。更多细节请参考CREATE TABLE中相同的语法。
注解
关键词COLUMN
是噪声,可以被省略。
在使用ADD COLUMN
增加一列并且指定了一个非易失性DEFAULT
时,默认值会在该语句执行时计算并且结果会被保存在表的元数据中。这个值将被用于所有现有行的该列。如果没有指定DEFAULT
,则使用NULL。在两种情况下都不需要重写表。
增加一个带有非易失性DEFAULT
子句的列或者更改一个现有列的类型将 要求重写整个表及其索引。在更改一个现有列的类型时有一种例外:如果 USING
子句不更改列的内容并且旧类型在二进制上与新类型可 强制转换或者是新类型上的一个未约束域,则不需要重写表。但是受影响列上 的任何索引仍必须被重建。增加或者移除一个系统oid
列也要求 重写整个表。对于一个大型表,表和/或索引重建可能会消耗相当多的时间, 并且会临时要求差不多两倍的磁盘空间。
增加一个CHECK
或者NOT NULL
约束要求扫描 表以验证现有行符合该约束,但是不要求一次表重写。
类似地,在挂接一个新分区时,它需要被扫描以验证现有行满足该分区约束。
提供在一个ALTER TABLE
中指定多个更改的选项的主要 原因就是多次表扫描或者重写可以因此被整合成一次。
DROP COLUMN
形式不会在物理上移除列,而只是简 单地让它对 SQL 操作不可见。后续该表中的插入和更新操作将为该列存储 一个空值。因此,删除一个列很快,但是它不会立刻减少表所占的磁盘空间, 因为被删除列所占用的空间还没有被回收。随着现有列被更新,空间将被逐渐 回收(这些说法不适用于删除系统oid
列的情况,那时会立刻 使用重写来完成)。
要强制立即回收被已删除列占据的空间,你可以执行一种能导致全表重写的 ALTER TABLE
形式。这种形式会导致重新构造每一个把被 删除列替换为空值的行。
ALTER TABLE
的重写形式对于 MVCC 是不安全的。 在一次表重写之后,如果并发事务使用的是一个在重写发生前取得的 快照,该表将对这些并发事务呈现出空表的形态。详见 第 13.5 节。
SET DATA TYPE
的USING
选项能实际指定 涉及该列旧值的任何表达式。也就是说,它可以不但可以引用要被转换的列, 还可以引用其他列。这允许使用SET DATA TYPE
语法完成十分 普遍的转换。由于这种灵活性,USING
表达式不适合于列 的默认值(如果有),结果可能不是一个默认值所需的常量表达式。这意味着 在没有从旧类型到新类型的隐式或者赋值造型时,即便提供了一个 USING
子句,SET DATA TYPE
还是可能无法 转换默认值。在这种情况下,用DROP DEFAULT
删除该默认值, 执行ALTER TYPE
并且接着使用SET DEFAULT
增加 一个合适的新默认值。类似的考虑也适用于涉及该列的索引和约束。
如果一个表有任何后代表,在不对后代表做相同操作的情况下,不允许在父表中增加列、重命名列或者更改列的类型。这确保了后代总是具有和父表匹配的列。类似地,如果不对所有后代上的CHECK
约束进行重命名,就不能在父表中重命名该CHECK
约束,这样CHECK
约束也能在父表及其后代之间保持匹配(不过,这个限制不适用于基于索引的约束)。此外,因为从父表中选择也会从其后代中选择,父表上的约束不能被标记为有效,除非它在那些后代上也被标记为有效。在所有这些情况下,ALTER TABLE ONLY
都将被拒绝。
只有当一个后代表的列不是从任何其他父表继承而来并且没有该列的独立定义时, 一次递归的DROP COLUMN
操作才会移除该列。一次非递归 的DROP COLUMN
(即 ALTER TABLE ONLY ... DROP COLUMN
)不会移除 任何后代列,而是会把它们标记成独立定义的列。对于一个分区表,一个非递归的DROP COLUMN
命令将会失败,因为一个表的所有分区都必须有和分区根节点相同的列。
标识列的动作(ADD GENERATED
、SET
等、DROP IDENTITY
)以及动作TRIGGER
、CLUSTER
、OWNER
和TABLESPACE
不会递归到后代表上,也就是说它们执行时总是好像指定了ONLY
一样。增加约束的动作仅对没有标记为NO INHERIT
的CHECK
约束递归。
不允许更改一个系统目录表的任何部分。
示例
要向一个表增加一个类型为varchar的列:
ALTER TABLE distributors ADD COLUMN address varchar(30);要从表中删除一列:
ALTER TABLE distributors DROP COLUMN address RESTRICT;要在一个操作中更改两个现有列的类型:
ALTER TABLE distributorsALTER COLUMN address TYPE varchar(80),ALTER COLUMN name TYPE varchar(100);通过一个USING子句更改一个包含 Unix 时间戳的整数列为 timestamp with time zone:
ALTER TABLE fooALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zoneUSINGtimestamp with time zone 'epoch' + foo_timestamp * interval '1 second';同样的,当该列具有一个不能自动造型成新数据类型的默认值表达式时:
ALTER TABLE fooALTER COLUMN foo_timestamp DROP DEFAULT,ALTER COLUMN foo_timestamp TYPE timestamp with time zoneUSINGtimestamp with time zone 'epoch' + foo_timestamp * interval '1 second',ALTER COLUMN foo_timestamp SET DEFAULT now();To rename an existing column:
ALTER TABLE distributors RENAME COLUMN address TO city;重命名一个现有的表:
ALTER TABLE distributors RENAME TO suppliers;重命名一个现有的约束:
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;为一列增加一个非空约束:
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;从一列移除一个非空约束:
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;向一个表及其所有子女增加一个检查约束:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);只向一个表增加一个检查约束(不为其子女增加):
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
(该检查约束也不会被未来的子女继承)。从一个表及其子女移除一个检查约束:
ALTER TABLE distributors DROP CONSTRAINT zipchk;只从一个表移除一个检查约束:
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
(该检查约束仍为子女表保留在某个地方)。为一个表增加一个外键约束:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);为一个表增加一个外键约束,并且尽量不要影响其他工作:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;为一个表增加一个(多列)唯一约束:
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);为一个表增加一个自动命名的主键约束,注意一个表只能拥有一个主键:
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);把一个表移动到一个不同的表空间:
ALTER TABLE distributors SET TABLESPACE fasttablespace;把一个表移动到一个不同的模式:
ALTER TABLE myschema.distributors SET SCHEMA yourschema;重建一个主键约束,并且在重建索引期间不阻塞更新:
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;要把一个分区挂接到一个范围分区表上:
ALTER TABLE measurementATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');要把一个分区挂接到一个列表分区表上:
ALTER TABLE citiesATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');要把一个分区挂接到一个哈希分区表上:
ALTER TABLE ordersATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);要把一个默认分区挂接到一个分区表上:
ALTER TABLE citiesATTACH PARTITION cities_partdef DEFAULT;从一个分区表分离一个分区:
ALTER TABLE measurementDETACH PARTITION measurement_y2015m12;
形式ADD
(没有USING INDEX
)、 DROP [COLUMN]
、DROP IDENTITY
、RESTART
、 SET DEFAULT
、SET DATA TYPE
(没有USING
)、 SET GENERATED
以及SET
服从SQL标准。其他形式都是PostgreSQL对SQL标准的扩展。此外,在单个sequence_option
ALTER TABLE
命令中指定多个操作的能力是一种扩展。
ALTER TABLE DROP COLUMN
可以被用来删除一个表的唯一的 列,从而留下一个零列的表。这是一种 SQL 的扩展,SQL 中不允许零列的表。
34、ALTER TABLESPACE
ALTER TABLESPACE — 更改一个表空间的定义
大纲
ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )
描述
ALTER TABLESPACE
可以被用于更改一个 表空间的定义。
要更改一个表空间的定义,你必须拥有它。要修改拥有者,你还必须是 新拥有角色的一个直接或间接成员(注意超级用户自动拥有这些特权)。
参数
name
一个现有表空间的名称。
new_name
该表空间的新名称。新名称不能以pg_
开始,因为这类名称被 保留用于系统表空间。
new_owner
该表空间的新拥有者。
tablespace_option
要设置或者重置的一个表空间参数。当前,唯一可用的参数是 seq_page_cost
、random_page_cost
和effective_io_concurrency
。 为一个特定表空间设定这两个参数值将覆盖规划器对从该表空间中的表读取 页面代价的估计值,这些估计值由具有相同名称配置参数建立(见 seq_page_cost
、 random_page_cost
、effective_io_concurrency)。如果一个表空间位于一个比 其余 I/O 子系统更快或者更慢的磁盘上时,这些参数就能派上用场。
示例
将表空间index_space重命名为fast_raid:
ALTER TABLESPACE index_space RENAME TO fast_raid;更改表空间index_space的拥有者:
ALTER TABLESPACE index_space OWNER TO mary;
35、ALTER TEXT SEARCH CONFIGURATION
ALTER TEXT SEARCH CONFIGURATION — 更改一个文本搜索配置的定义
大纲
ALTER TEXT SEARCH CONFIGURATION nameADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
ALTER TEXT SEARCH CONFIGURATION nameALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]
ALTER TEXT SEARCH CONFIGURATION nameALTER MAPPING REPLACE old_dictionary WITH new_dictionary
ALTER TEXT SEARCH CONFIGURATION nameALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary
ALTER TEXT SEARCH CONFIGURATION nameDROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name
ALTER TEXT SEARCH CONFIGURATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema
描述
ALTER TEXT SEARCH CONFIGURATION
更改一个文本搜索配置的定义。你可以修改其从记号类型到词典的映射 或者更改该配置的名称或者拥有者。
要使用ALTER TEXT SEARCH CONFIGURATION
, 你必须是该配置的拥有者。
参数
name
一个现有文本搜索配置的名称(可以是模式限定的)。
token_type
由该配置的解析器发出的记号类型的名称。
dictionary_name
在其中查阅指定记号类型的文本搜索字典的名称。如果列出了 多个字典,会按照指定的顺序查阅它们。
old_dictionary
在映射中要替换的文本搜索字典的名称。
new_dictionary
被用来替代old_dictionary
的文本搜索字典的名称。
new_name
该文本搜索配置的新名称。
new_owner
该文本搜索配置的新拥有者。
new_schema
该文本搜索配置的新模式。
ADD MAPPING FOR
形式会安装一些词典(用列表列出)用于在其中 查阅指定的记号类型。如果对任一记号类型已经有一个映射,则会发生错误。 ALTER MAPPING FOR
形式做同样的事情,但是首先会移除这些记号 类型的任何现有映射。ALTER MAPPING REPLACE
形式用 new_dictionary
来替换任何位 置上的old_dictionary
。当出 现FOR
时,只会为指定的记号类型做这样的事情。如果不出现 FOR
,则会为该配置中所有的映射都这样做。 DROP MAPPING
形式会移除指定记号类型的所有字典,导致该文本 搜索配置忽略这些类型。除非出现IF EXISTS
,在那些记号类型没有 任何映射时会发生错误。
示例
下面的例子把my_config中任何位置上的english字典 替换为swedish字典。ALTER TEXT SEARCH CONFIGURATION my_configALTER MAPPING REPLACE english WITH swedish;
36、ALTER TEXT SEARCH DICTIONARY
ALTER TEXT SEARCH DICTIONARY — 更改一个文本搜索字典的定义
大纲
ALTER TEXT SEARCH DICTIONARY name (option [ = value ] [, ... ]
)
ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name
ALTER TEXT SEARCH DICTIONARY name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema
描述
ALTER TEXT SEARCH DICTIONARY
更改一个 文本搜索字典的定义。你可以更改该字典的与模板相关的选项,或者更改该 字典的名称或者拥有者。
要使用 ALTER TEXT SEARCH DICTIONARY
,你必须是超级用户。
参数
name
一个现有的文本搜索字典的名称(可以是模式限定的)。
option
要为这个字典设置的与模板相关的选项的名称。
value
用于一个模板相关选项的新值。如果等号和值被忽略,则会从该字典 中移除该选项之前的设置而允许使用默认值。
new_name
该文本搜索字典的新名称。
new_owner
该文本搜索字典的新拥有者。
new_schema
该文本搜索字典的新模式。
模板相关的选项可以以任何顺序出现。
示例
下面的命令更改一个基于 Snowball 的字典的停用词列表。其他参数保持不变。
ALTER TEXT SEARCH DICTIONARY my_dict ( StopWords = newrussian );下面的命令更改语言选项为dutch,并且完全移除停用词选项。
ALTER TEXT SEARCH DICTIONARY my_dict ( language = dutch, StopWords );下面的命令“更新”该字典的定义,但是实际没有做任何更改。
ALTER TEXT SEARCH DICTIONARY my_dict ( dummy );
(之所以能这样做是因为选项移除代码在选项不存在时也不会抱怨)。 这种技巧在为该字典更改配置文件时有用:ALTER
将强制现有的数据库会话重读配置文件,否则如果会话之前已经读取过 就不会再次读取。
37、ALTER TEXT SEARCH PARSER
ALTER TEXT SEARCH PARSER — 更改一个文本搜索解析器的定义
大纲
ALTER TEXT SEARCH PARSER name RENAME TO new_name
ALTER TEXT SEARCH PARSER name SET SCHEMA new_schema
描述
ALTER TEXT SEARCH PARSER
更改一个文本 搜索解析器的定义。当前,唯一支持的功能是更改该解析器的名称。
要使用ALTER TEXT SEARCH PARSER
,你必须是超级用户。
参数
name
一个现有文本搜索解析器的名称(可以是模式限定的)。
new_name
该文本搜索解析器的新名称。
new_schema
该文本搜索解析器的新模式。
38、ALTER TEXT SEARCH TEMPLATE
ALTER TEXT SEARCH TEMPLATE — 更改一个文本搜索模板的定义
大纲
ALTER TEXT SEARCH TEMPLATE name RENAME TO new_name
ALTER TEXT SEARCH TEMPLATE name SET SCHEMA new_schema
描述
ALTER TEXT SEARCH TEMPLATE
更改一个 文本搜索模板的定义。当前唯一支持的功能是更改该模板的名称。
要使用ALTER TEXT SEARCH TEMPLATE
,你必须是超级用户。
参数
name
一个现有文本搜索模板的名称(可以是模式限定的)。
new_name
该文本搜索模板的新名称。
new_schema
该文本搜索模板的新模式。
39、ALTER TRIGGER
ALTER TRIGGER — 更改一个触发器的定义
大纲
ALTER TRIGGER name ON table_name RENAME TO new_name
ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name
描述
ALTER TRIGGER
更改一个现有触发器的属性。 RENAME
子句更改给定触发器的名称而不更改其定义。 DEPENDS ON EXTENSION
子句把该触发器标记为依赖于 一个扩展,这样如果扩展被删除,该触发器也会被自动删除。
要更改一个触发器的属性,你必须拥有该触发器所作用的表。
参数
name
要修改的一个现有触发器的名称。
table_name
这个触发器所作用的表的名称。
new_name
该触发器的新名称。
extension_name
该触发器所依赖的扩展的名称。
注解
临时启用或者禁用一个触发器的功能由ALTER TABLE而不是 ALTER TRIGGER
提供,因为ALTER TRIGGER
无法表示一次性启用或者禁用一个表上所有触发器的选项。
示例
要重命名一个现有的触发器:
ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;要把一个触发器标记为依赖于一个扩展:
ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib;
40、ALTER TYPE
ALTER TYPE — 更改一个类型的定义
大纲
ALTER TYPE name action [, ... ]
ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value这里action 是以下之一:ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
描述
ALTER TYPE
更改一种现有类型的定义。 它有几种形式:
ADD ATTRIBUTE
这种形式为一种组合类型增加一个新属性,使用的语法和 CREATE TYPE相同。
DROP ATTRIBUTE [ IF EXISTS ]
这种形式从一种组合类型删除一个属性。如果指定了 IF EXISTS
并且该属性不存在,则不会抛出错误。 这种情况下会发出一个提示。
SET DATA TYPE
这种形式更改一种组合类型的一个属性类型。
OWNER
这种形式更改该类型的拥有者。
RENAME
这种形式更改该类型的名称或者一种组合类型的一个属性的名称。
SET SCHEMA
这种形式把该类型移动到另一个模式中。
ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]
这种形式为一种枚举类型增加一个新值。可以用BEFORE
或者 AFTER
一个现有值来指定新值在枚举顺序中的位置。 否则,新项会被增加在值列表的最后。
如果指定了IF NOT EXISTS
,该类型已经包含新值时不会发生 错误:会发出一个提示但是不采取其他行动。否则,如果新值已经存在会发生错误。
RENAME VALUE
该形式重命名枚举类型的值。该值在枚举排序中的位置不受影响。 如果指定的值不存在或新名称已存在,则会发生错误。
ADD ATTRIBUTE
、DROP ATTRIBUTE
和ALTER ATTRIBUTE
动作 可以被整合到一个多个修改组成的列表中,以便被平行应用。例如, 可以在一个命令中增加多个属性并且/或者修改多个属性的类型。
要使用ALTER TYPE
,你必须拥有该类型。要更改 一个类型的模式,你还必须拥有新模式上的 CREATE
特权。要更改拥有者,你还必须 是新拥有角色的一个直接或者间接成员,并且该角色必须具有该类型的模式上的 CREATE
特权(这些限制强制修改拥有者不能做一些通过删除和重 建该类型做不到的事情。不过,一个超级用户怎么都能更改任何类型的所有权。)。 要增加一个属性或者修改一个属性类型,你还必须具有该数据类型上的 USAGE
特权。
参数
name
要修改的一个现有类型的名称(可能被模式限定)。
new_name
该类型的新名称。
new_owner
该类型新拥有者的用户名。
new_schema
该类型的新模式。
attribute_name
要增加、修改或者删除的属性名称。
new_attribute_name
要被重命名的属性的新名称。
data_type
要增加的属性的数据类型,或者是要修改的属性的新类型。
new_enum_value
要被增加到一个枚举类型的值列表的新值,或将赋予现有值的新名称。 和所有枚举文本一样,它需要被引号引用。
neighbor_enum_value
一个现有枚举值,新值应该被增加在紧接着该枚举值之前或者 之后的位置上。和所有枚举文本一样,它需要被引号引用。
existing_enum_value
现有的应该重命名的枚举值。和所有的枚举文本一样,它需要被引号引用。
CASCADE
自动将操作传播到被更改类型的类型表及其后代。
RESTRICT
如果被更改的类型是类型表的类型,则拒绝该操作。这是默认设置。
注解
ALTER TYPE ... ADD VALUE
(增加一个新值到枚举类型的形式) 不能在一个事务块中执行。
涉及到一个新增加枚举值的比较有时会被只涉及原始枚举值的比较更慢。这通常 只会在利用BEFORE
或者AFTER
来把新值的 排序位置设置为非列表结尾的地方时发生。不过,有时候即使把新值增加到最后时 也会发生这种情况(如果创建了该枚举类型之后出现过 OID 计数器 “回卷”,就会发生这种情况)。这种减速通常不明显,但是如果它确实 带来了麻烦,通过删除并且重建该枚举类型或者转储并且重载整个数据库可以重新 得到最优性能。
示例
要重命名一个数据类型:
ALTER TYPE electronic_mail RENAME TO email;把类型email的拥有者改为 joe:
ALTER TYPE email OWNER TO joe;把类型email的模式改为 customers:
ALTER TYPE email SET SCHEMA customers;增加一个新属性到一个类型:
ALTER TYPE compfoo ADD ATTRIBUTE f3 int;在一个特定的排序位置上为一个枚举类型增加一个新值:
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';重命名一个枚举值:
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
41、ALTER USER
ALTER USER — 更改一个数据库角色
大纲
ALTER USER role_specification [ WITH ] option [ ... ]其中 option 可以是:SUPERUSER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE| INHERIT | NOINHERIT| LOGIN | NOLOGIN| REPLICATION | NOREPLICATION| BYPASSRLS | NOBYPASSRLS| CONNECTION LIMIT connlimit| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL| VALID UNTIL 'timestamp'ALTER USER name RENAME TO new_nameALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL其中 role_specification 可以是:role_name| CURRENT_USER| SESSION_USER
描述
ALTER USER
现在是 ALTER ROLE的一种别名。
42、ALTER USER MAPPING
ALTER USER MAPPING — 更改一个用户映射的定义
大纲
ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC }SERVER server_nameOPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
描述
ALTER USER MAPPING
更改一个用户映射的定义。
一个外部服务器的拥有者可以为任何用户修改用于该服务器的用户映射。还有, 如果一个用户被授予了外部服务器上的USAGE
特权,它就能为它们自 己的用户名修改一个用户映射。
参数
user_name
该映射的用户名。CURRENT_USER
和USER
匹配当前 用户的名称。PUBLIC
被用来匹配系统中所有当前以及未来的用户名。
server_name
该用户映射的服务器名。
OPTIONS ( [ ADD | SET | DROP ]
option
['value
'] [, ... ] )
为该用户映射更改选项。新选项会覆盖任何之前指定的选项。ADD
、 SET
和DROP
指定要被执行的动作。如果没有显式地指定 操作,将假定为ADD
。选项名称必须为唯一,该服务器的外部数据包装 器也会验证选项。
示例
为服务器 foo的用户映射bob更改口令:ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');
43、ALTER VIEW
ALTER VIEW — 更改一个视图的定义
大纲
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )
描述
ALTER VIEW
更改一个视图的多种辅助属性(如果想要 修改视图的查询定义,应使用CREATE OR REPLACE VIEW
)。
要使用ALTER VIEW
,你必须拥有该视图。要更改一个视图的模式, 你还必须具有新模式上的CREATE
特权。要更改拥有者,你还必须 是新拥有角色的一个直接或者间接成员,并且该角色必须具有该视图的模式上的 CREATE
特权(这些限制强制修改拥有者不能做一些通过删除和重 建视图做不到的事情。不过,一个超级用户怎么都能更改任何视图的所有权。)。
参数
name
一个现有视图的名称(可以是模式限定的)。
IF EXISTS
该视图不存在时不要抛出一个错误。这种情况下会发出一个提示。
SET
/DROP DEFAULT
这些形式为一个列设置或者移除默认值。对于任何在该视图上的 INSERT
或者UPDATE
命令,一个视图列的默认值 会在引用该视图的任何规则或触发器之前被替换进来。因此,该视图的默认值将会 优先于来自底层关系的任何默认值。
new_owner
该视图的新拥有者的用户名。
new_name
该视图的新名称。
new_schema
该视图的新模式。
SET (
view_option_name
[= view_option_value
] [, ... ] ) RESET (
view_option_name
[, ... ] )
设置或者重置一个视图选项。当前支持的选项有:
check_option
(string
)
更改该视图的检查选项。值必须是local
或者cascaded
。
security_barrier
(boolean
)
更改该视图的安全屏障属性。值必须是一个布尔值,如 true
或者false
。
注解
由于历史原因,ALTER TABLE
也可以用于视图,但是 只允许等效于以上形式的ALTER TABLE
变体用于视图。
示例
把视图foo重命名为 bar:
ALTER VIEW foo RENAME TO bar;要为一个可更新视图附加一个默认列值:
CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
44、ANALYZE
ANALYZE — 收集有关一个数据库的统计信息
大纲
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]其中option可以是:VERBOSEtable_and_columns是:table_name [ ( column_name [, ...] ) ]
描述
ANALYZE
收集一个数据库中的表的内容的统计信息,并且将结果存储在pg_statistic系统目录中。接下来,查询规划器会使用这些统计信息来帮助确定查询最有效的执行计划。
如果没有table_and_columns
列表,则ANALYZE
处理当前用户有权分析的当前数据库中的每个表和物化视图。使用列表,ANALYZE
仅处理那些表。还可以给出表的列名列表,在这种情况下,仅收集这些列的统计信息。
当选项列表用括号括起来时,选项可以按任何顺序来写。 带括号的语法是在PostgreSQL 11中添加的;不带括号的语法已弃用。
参数
VERBOSE
允许显示进度消息。
table_name
要分析的一个指定表的名称(可以是模式限定的)。如果省略,则分析当前数据库中的所有常规表、分区表和物化视图(但不包含外部表)。 如果指定的表是分区表,则整个分区表的继承统计信息和各个分区的统计信息都将更新。
column_name
要分析的一个指定列的名称。默认是所有列。
输出
当指定了VERBOSE
时,ANALYZE
会发出进度消息来指示当前正在处理哪个表。还会打印有关那些表的多种统计信息。
注解
只有被显式选中时才会分析外部表。并非所有外部数据包装器都支持ANALYZE
。如果表的包装器不支持ANALYZE
,该命令会打印一个警告并且什么也不做。
在默认的PostgreSQL配置中,自动清理守护进程会在表第一次载入数据或者用常规操作改变时负责表的自动分析。当启用自动清理时,定期运行ANALYZE
是个好主意,或者可以在表内容做了大的修改后运行ANALYZE
。准确的统计信息将帮助规划器选择最合适的查询计划,从而提升查询处理的速度。主读数据库的一般策略是在一天中使用量最低时运行一次VACUUM和ANALYZE
(如果有大量的更新动作则是不够的)。
ANALYZE
只要求目标表上的一个读锁,因此它可以和表上的其他动作并行。
ANALYZE
收集的统计信息通畅包括每列中最常见值的列表以及展示每列中近似数据分布的一个直方图。如果ANALYZE
认为这些东西无趣(例如在一个唯一键列中,没有共同值)或者该列的数据类型不支持合适的操作符,以上工作都会被省略。
对于大型的表,ANALYZE
会对表内容做随机采样而不是检查每一行。这允许在很少的时间内完成对大型表的分析。不过要注意,这些统计信息只是近似值,并且即使实际表内容没有改变,每次运行ANALYZE
时统计信息都会有微小地改变。这可能会导致EXPAIN显示的规划器估算代价有小的改变。在很少的情况下,这会非决定性地导致规划器的查询计划选择在ANALYZE
运行后改变。为了避免这种情况,可以按照下文所述提高ANALYZE
所收集的统计信息量。
通过调整default_statistics_target配置变量可以控制分析量,对每个列可以用ALTER TABLE ... ALTER COLUMN ... SET STATISTICS
设置每列的统计信息目标(见ALTER TABLE)。目标值会设置最常用值列表中的最大项数以及直方图中的最大容器数。默认目标值是 100,可以把它调大或者调小在规划器估计值精度和ANALYZE
花费的时间以及pg_statistic
所占空间之间做出平衡。特别地,将统计信息目标设置为零会禁用该列的统计信息收集。在查询的WHERE
、GROUP BY
或者ORDER BY
子句中从不出现的列上这样做会有所帮助,因为规划器用不上这些列上的统计信息。
被分析的列中最大的统计信息目标决定了为准备统计信息要采样的表行数。增加该目标会导致做ANALYZE
所需的时间和空间成比例增加。
ANALYZE
所估算的值之一是出现在每个列中的可区分值。因为只会检查行的一个子集,即便使用最大的统计信息目标,这种估计有时也可能很不精确。如果这种不精确导致不好的查询计划,可以手工确定一个更精确的值并且用ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)
设置该值(见ALTER TABLE)。
如果被分析的表有一个或者更多子女,ANALYZE
将会收集两次统计信息:一次只对父表的行收集,第二次则在父表及其所有子女表的行上收集。在规划需要遍历整个继承树的查询时需要第二个统计信息集。不过,在决定是否触发表上的自动分析时,自动清理后台进程将只考虑父表本身上的插入和更新。如果该表很少被插入或者更新,只有手工运行ANALYZE
时才会把继承统计信息更新到最新。
如果任何子表是外部表并且其外部数据包装器不支持ANALYZE
,在收集继承统计信息时会忽略那些子表。
如果被分析的表不完全为空,ANALYZE
将不会为该表记录新统计信息。任何现有统计信息将会被保留。
45、BEGIN
BEGIN — 开始一个事务块
大纲
BEGIN [ WORK | TRANSACTION ] [
transaction_mode[, ...] ]其中
transaction_mode是以下之一:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ WRITE | READ ONLY[ NOT ] DEFERRABLE
描述
BEGIN
开始一个事务块,也就是说所有 BEGIN
命令之后的所有语句将被在一个 事务中执行,直到给出一个显式的COMMIT或 者ROLLBACK。默认情况下(没有 BEGIN
), PostgreSQL在 “自动提交”模式中执行事务,也就是说每个语句都 在自己的事务中执行并且在语句结束时隐式地执行一次提交(如果执 行成功,否则会完成一次回滚)。
在一个事务块内的语句会执行得更快,因为事务的开始/提交也要求可观 的 CPU 和磁盘活动。在进行多个相关更改时,在一个事务内执行多个语 句也有助于保证一致性:在所有相关更新还没有完成之前,其他会话将不 能看到中间状态。
如果指定了隔离级别、读/写模式或者延迟模式,新事务也会有那些特性, 就像执行了SET TRANSACTION一样。
参数
WORK
TRANSACTION
可选的关键词。它们没有效果。
这个语句其他参数的含义请参考 SET TRANSACTION。
注解
START TRANSACTION具有和BEGIN
相同的功能。
使用COMMIT或者 ROLLBACK来终止一个事务块。
在已经在一个事务块中时发出BEGIN
将惹出一个警告 消息。事务状态不会被影响。要在一个事务块中嵌套事务,可以使用保 存点(见SAVEPOINT)。
由于向后兼容的原因,连续的 transaction_modes
之间的逗号可以被省略。
示例
开始一个事务块:BEGIN;
CALL — 调用一个过程
CHECKPOINT — 强制一个WAL日志检查点
CLOSE — 关闭一个游标
CLUSTER — 根据一个索引聚簇一个表
COMMENT — 定义或者更改一个对象的注释
COMMIT — 提交当前事务
COMMIT PREPARED — 提交一个早前为两阶段提交预备的事务
COPY — 在一个文件和一个表之间复制数据
CREATE ACCESS METHOD — 定义一种新的访问方法
CREATE AGGREGATE — 定义一个新的聚集函数
CREATE CAST — 定义一种新的造型
CREATE COLLATION — 定义一种新排序规则
CREATE CONVERSION — 定义一种新的编码转换
CREATE DATABASE — 创建一个新数据库
CREATE DOMAIN — 定义一个新的域
CREATE EVENT TRIGGER — 定义一个新的事件触发器
CREATE EXTENSION — 安装一个扩展
CREATE FOREIGN DATA WRAPPER — 定义一个新的外部数据包装器
CREATE FOREIGN TABLE — 定义一个新的外部表
CREATE FUNCTION — 定义一个新函数
CREATE GROUP — 定义一个新的数据库角色
CREATE INDEX — 定义一个新索引
CREATE LANGUAGE — 定义一种新的过程语言
CREATE MATERIALIZED VIEW — 定义一个新的物化视图
CREATE OPERATOR — 定义一个新的操作符
CREATE OPERATOR CLASS — 定义一个新的操作符类
CREATE OPERATOR FAMILY — 定义一个新的操作符族
CREATE POLICY — 为一个表定义一条新的行级安全性策略
CREATE PROCEDURE — 定义一个新的过程
CREATE PUBLICATION — 定义一个新的发布
CREATE ROLE — 定义一个新的数据库角色
CREATE RULE — 定义一条新的重写规则
CREATE SCHEMA — 定义一个新模式
CREATE SEQUENCE — 定义一个新的序列发生器
CREATE SERVER — 定义一个新的外部服务器
CREATE STATISTICS — 定义扩展统计
CREATE SUBSCRIPTION — 定义一个新的订阅
CREATE TABLE — 定义一个新表
CREATE TABLE AS — 从一个查询的结果创建一个新表
CREATE TABLESPACE — 定义一个新的表空间
CREATE TEXT SEARCH CONFIGURATION — 定义一个新的文本搜索配置
CREATE TEXT SEARCH DICTIONARY — 定义一个新的文本搜索字典
CREATE TEXT SEARCH PARSER — 定义一个新的文本搜索解析器
CREATE TEXT SEARCH TEMPLATE — 定义一种新的文本搜索模板
CREATE TRANSFORM — 定义一个新的转换
CREATE TRIGGER — 定义一个新触发器
CREATE TYPE — 定义一种新的数据类型
CREATE USER — 定义一个新的数据库角色
CREATE USER MAPPING — 定义一个用户到一个外部服务器的新映射
CREATE VIEW — 定义一个新视图
DEALLOCATE — 释放一个预备语句
DECLARE — 定义一个游标
DELETE — 删除一个表的行
DISCARD — 抛弃会话状态
DO — 执行一个匿名代码块
DROP ACCESS METHOD — 移除一种访问方法
DROP AGGREGATE — 移除一个聚集函数
DROP CAST — 移除一个造型
DROP COLLATION — 移除一个排序规则
DROP CONVERSION — 移除一个转换
DROP DATABASE — 移除一个数据库
DROP DOMAIN — 移除一个域
DROP EVENT TRIGGER — 移除一个事件触发器
DROP EXTENSION — 移除一个扩展
DROP FOREIGN DATA WRAPPER — 移除一个外部数据包装器
DROP FOREIGN TABLE — 移除一个外部表
DROP FUNCTION — 移除一个函数
DROP GROUP — 移除一个数据库角色
DROP INDEX — 移除一个索引
DROP LANGUAGE — 移除一个过程语言
DROP MATERIALIZED VIEW — 移除一个物化视图
DROP OPERATOR — 移除一个操作符
DROP OPERATOR CLASS — 移除一个操作符类
DROP OPERATOR FAMILY — 移除一个操作符族
DROP OWNED — 移除一个数据库角色拥有的数据库对象
DROP POLICY — 从一个表移除一条行级安全性策略
DROP PROCEDURE — 移除一个过程
DROP PUBLICATION — 删除一个发布
DROP ROLE — 移除一个数据库角色
DROP ROUTINE — 删除一个例程
DROP RULE — 移除一个重写规则
DROP SCHEMA — 移除一个模式
DROP SEQUENCE — 移除一个序列
DROP SERVER — 移除一个外部服务器描述符
DROP STATISTICS — 删除扩展统计
DROP SUBSCRIPTION — 删除一个订阅
DROP TABLE — 移除一个表
DROP TABLESPACE — 移除一个表空间
DROP TEXT SEARCH CONFIGURATION — 移除一个文本搜索配置
DROP TEXT SEARCH DICTIONARY — 移除一个文本搜索字典
DROP TEXT SEARCH PARSER — 移除一个文本搜索解析器
DROP TEXT SEARCH TEMPLATE — 移除一个文本搜索模板
DROP TRANSFORM — 移除转换
DROP TRIGGER — 移除一个触发器
DROP TYPE — 移除一个数据类型
DROP USER — 移除一个数据库角色
DROP USER MAPPING — 移除一个用于外部服务器的用户映射
DROP VIEW — 移除一个视图
END — 提交当前事务
EXECUTE — 执行一个预备语句
EXPLAIN — 显示一个语句的执行计划
FETCH — 使用游标从查询中检索行
GRANT — 定义访问特权
IMPORT FOREIGN SCHEMA — 从一个外部服务器导入表定义
INSERT — 在一个表中创建新行
LISTEN — 监听一个通知
LOAD — 载入一个共享库文件
LOCK — 锁定一个表
MOVE — 定位一个游标
NOTIFY — 生成一个通知
PREPARE — 为执行准备一个语句
PREPARE TRANSACTION — 为两阶段提交准备当前事务
REASSIGN OWNED — 更改一个数据库角色拥有的数据库对象的拥有关系
REFRESH MATERIALIZED VIEW — 替换一个物化视图的内容
REINDEX — 重建索引
RELEASE SAVEPOINT — 销毁一个之前定义的保存点
RESET — 把一个运行时参数的值恢复到默认值
REVOKE — 移除访问特权
ROLLBACK — 中止当前事务
ROLLBACK PREPARED — 取消一个之前为两阶段提交准备好的事务
ROLLBACK TO SAVEPOINT — 回滚到一个保存点
SAVEPOINT — 在当前事务中定义一个新的保存点
SECURITY LABEL — 定义或更改应用到一个对象的安全标签
SELECT — 从一个表或视图检索行
SELECT INTO — 从一个查询的结果定义一个新表
SET — 更改一个运行时参数
SET CONSTRAINTS — 为当前事务设置约束检查时机
SET ROLE — 设置当前会话的当前用户标识符
SET SESSION AUTHORIZATION — 设置当前会话的会话用户标识符和当前用户标识符
SET TRANSACTION — 设置当前事务的特性
SHOW — 显示一个运行时参数的值
START TRANSACTION — 开始一个事务块
TRUNCATE — 清空一个表或者一组表
UNLISTEN — 停止监听一个通知
UPDATE — 更新一个表的行
VACUUM — 垃圾收集并根据需要分析一个数据库
VALUES — 计算一个行集合
这篇关于PostgreSQL教程(四十二):参考命令(一)之SQL命令的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!