ALTER DATABASE — change a database

Synopsis

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER 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

Description

ALTER DATABASE changes the attributes of a database.

The first form changes certain per-database settings. (See below for details.) Only the database owner or a superuser can change these settings.

The second form changes the name of the database. Only the database owner or a superuser can rename a database; non-superuser owners must also have the CREATEDB privilege. The current database cannot be renamed. (Connect to a different database if you need to do that.)

The third form changes the owner of the database. To alter the owner, you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.)

The fourth form changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. The new default tablespace must be empty for this database, and no one can be connected to the database. Tables and indexes in non-default tablespaces are unaffected.

The remaining forms change the session default for a run-time configuration variable for a PostgreSQL database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value. The database-specific default overrides whatever setting is present in postgresql.conf or has been received from the postgres command line. Only the database owner or a superuser can change the session defaults for a database. Certain variables cannot be set this way, or can only be set by a superuser.

Parameters

name

The name of the database whose attributes are to be altered.allowconn

If false then no one can connect to this database.connlimit

How many concurrent connections can be made to this database. -1 means no limit.istemplate

If true, then this database can be cloned by any user with CREATEDB privileges; if false, then only superusers or the owner of the database can clone it.new_name

The new name of the database.new_owner

The new owner of the database.new_tablespace

The new default tablespace of the database.

This form of the command cannot be executed inside a transaction block.configuration_parameter value

Set this database's session default for the specified configuration parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the database-specific setting is removed, so the system-wide default setting will be inherited in new sessions. Use RESET ALL to clear all database-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the database-specific value.

See SET and Chapter 19 for more information about allowed parameter names and values.

Notes

It is also possible to tie a session default to a specific role rather than to a database; see ALTER ROLE. Role-specific settings override database-specific ones if there is a conflict.

Examples

To disable index scans by default in the database test:

ALTER DATABASE test SET enable_indexscan TO off;

Compatibility

The ALTER DATABASE statement is a PostgreSQL extension.

See Also

CREATE DATABASE, DROP DATABASE, SET, CREATE TABLESPACE

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

where member_object is:

  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

and aggregate_signature is:

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

說明

ALTER EXTENSION 用來變更已安裝的延伸功能的定義。包括有幾個子形態：

UPDATE

此形態將該延伸功能更新為新的版本。該延伸功能必須提供合適的更新腳本（或一系列的腳本），可以將目前安裝的版本升級到要求更新的版本。

SET SCHEMA

這種形態將延伸功能的物件移動到另一個綱要中。該延伸功能必須是可以重新定位的，此命令才能成功。

ADD member_object

此形態將現有物件新增至延伸功能之中。這主要在延伸功能更新腳本時有用處。該物件隨後將被視為延伸功能的成員之一；值得注意的是，接下來也只能透過移除延伸功能來移除它。

DROP member_object

此形態從延伸功能中移除成員物件。這主要用於延伸功能的更新腳本之中。該物件並不會被實體移除，僅與該延伸功能脫離關連。

有關這些操作的更多資訊，請參閱第 37.17 節。

您必須擁有該延伸功能才能使用 ALTER EXTENSION。ADD/DROP 形態還需要所要新增/移除物件的所有權。

Parameters

name

The name of an installed extension.

new_version

The desired new version of the extension. This can be written as either an identifier or a string literal. If not specified, ALTER EXTENSION UPDATE attempts to update to whatever is shown as the default version in the extension's control file.

new_schema

The new schema for the extension.

object_name aggregate_name function_name operator_name procedure_name routine_name

The name of an object to be added to or removed from the extension. Names of tables, aggregates, domains, foreign tables, functions, operators, operator classes, operator families, procedures, routines, sequences, text search objects, types, and views can be schema-qualified.

source_type

The name of the source data type of the cast.

target_type

The name of the target data type of the cast.

argmode

The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that ALTER EXTENSION does not actually pay any attention to OUTarguments, since only the input arguments are needed to determine the function's identity. So it is sufficient to list the IN, INOUT, and VARIADIC arguments.

argname

The name of a function, procedure, or aggregate argument. Note that ALTER EXTENSION does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity.

argtype

The data type of a function, procedure, or aggregate argument.

left_type right_type

The data type(s) of the operator's arguments (optionally schema-qualified). Write NONE for the missing argument of a prefix or postfix operator.

PROCEDURAL

This is a noise word.

type_name

The name of the data type of the transform.

lang_name

The name of the language of the transform.

範例

要將 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

ALTER INDEX — change the definition of an index

Synopsis

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_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 ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

Description

ALTER INDEX changes the definition of an existing index. There are several subforms:RENAME

The RENAME form changes the name of the index. There is no effect on the stored data.SET TABLESPACE

This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which will only move indexes owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE or explicit ALTER INDEX invocations instead if desired. See also CREATE TABLESPACE.DEPENDS ON EXTENSION

This form marks the index as dependent on the extension, such that if the extension is dropped, the index will automatically be dropped as well.SET ( storage_parameter = value [, ... ] )

This form changes one or more index-method-specific storage parameters for the index. See CREATE INDEX for details on the available parameters. Note that the index contents will not be modified immediately by this command; depending on the parameter you might need to rebuild the index with REINDEX to get the desired effects.RESET ( storage_parameter [, ... ] )

This form resets one or more index-method-specific storage parameters to their defaults. As with SET, a REINDEX might be needed to update the index entirely.

Parameters

IF EXISTS

Do not throw an error if the index does not exist. A notice is issued in this case.

name

The name (possibly schema-qualified) of an existing index to alter.

new_name

The new name for the index.

tablespace_name

The tablespace to which the index will be moved.

extension_name

The name of the extension that the index is to depend on.

storage_parameter

The name of an index-method-specific storage parameter.

value

The new value for an index-method-specific storage parameter. This might be a number or a word depending on the parameter.

Notes

These operations are also possible using ALTER TABLE. ALTER INDEX is in fact just an alias for the forms of ALTER TABLE that apply to indexes.

There was formerly an ALTER INDEX OWNER variant, but this is now ignored (with a warning). An index cannot have an owner different from its table's owner. Changing the table's owner automatically changes the index as well.

Changing any part of a system catalog index is not permitted.

Examples

To rename an existing index:

ALTER INDEX distributors RENAME TO suppliers;

To move an index to a different tablespace:

ALTER INDEX distributors SET TABLESPACE fasttablespace;

To change an index's fill factor (assuming that the index method supports it):

ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;

Compatibility

ALTER INDEX is a PostgreSQL extension.

See Also

CREATE INDEX, REINDEX

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

語言的新所有者e

相容性

SQL 標準中沒有 ALTER LANGUAGE 語句。

參閱

CREATE LANGUAGE, DROP LANGUAGE\

ALTER PUBLICATION — change the definition of a publication

Synopsis

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

Description

The command ALTER PUBLICATION can change the attributes of a publication.

The first three variants change which tables are part of the publication. The SET TABLE clause will replace the list of tables in the publication with the specified one. The ADD TABLE and DROP TABLE clauses will add and remove one or more tables from the publication. Note that adding tables to a publication that is already subscribed to will require a ALTER SUBSCRIPTION ... REFRESH PUBLICATION action on the subscribing side in order to become effective.

The fourth variant of this command listed in the synopsis can change all of the publication properties specified in CREATE PUBLICATION. Properties not mentioned in the command retain their previous settings.

The remaining variants change the owner and the name of the publication.

You must own the publication to use ALTER PUBLICATION. To alter the owner, you must also be a direct or indirect member of the new owning role. The new owner must have CREATE privilege on the database. Also, the new owner of a FOR ALL TABLES publication must be a superuser. However, a superuser can change the ownership of a publication while circumventing these restrictions.

Parameters

name

The name of an existing publication whose definition is to be altered.

table_name

Name of an existing table. If ONLY is specified before the table name, only that table is affected. If ONLY is not specified, the table and all its descendant tables (if any) are affected. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

SET ( publication_parameter [= value] [, ... ] )

This clause alters publication parameters originally set by CREATE PUBLICATION. See there for more information.

new_owner

The user name of the new owner of the publication.

new_name

The new name for the publication.

Examples

Change the publication to publish only deletes and updates:

ALTER PUBLICATION noinsert SET (publish = 'update, delete');

Add some tables to the publication:

ALTER PUBLICATION mypublication ADD TABLE users, departments;

Compatibility

ALTER PUBLICATION is a PostgreSQL extension.

See Also

CREATE PUBLICATION, DROP PUBLICATION, CREATE SUBSCRIPTION, ALTER SUBSCRIPTION

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;

相容性

ALTER RULE 是一個 PostgreSQL 語言延伸功能，整個查詢覆寫系統也都是延伸功能。

參閱

CREATE RULE, DROP RULE

ALTER SCHEMA — change the definition of a schema

Synopsis

Description

ALTER SCHEMA changes the definition of a schema.

You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database. (Note that superusers have all these privileges automatically.)

Parameters

name

The name of an existing schema.

new_name

The new name of the schema. The new name cannot begin with pg_, as such names are reserved for system schemas.

new_owner

The new owner of the schema.

Compatibility

There is no ALTER SCHEMA statement in the SQL standard.

See Also

ALTER FUNCTION — 變更一個函數的定義

語法

說明

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

IMMUTABLE STABLE VOLATILE

[ EXTERNAL ] SECURITY INVOKER [ EXTERNAL ] SECURITY DEFINER

PARALLEL

LEAKPROOF

COST execution_cost

ROWS result_rows

configuration_parameter value

呼叫函數時，增加或變更要對配置參數進行的指定方式。 如果值為 DEFAULT，或者等價地使用 RESET，那麼函數本地配置將被刪除，以便該函數執行其環境中存在的值。使用 RESET ALL 清除所有功能本地配置。SET FROM CURRENT 將執行 ALTER FUNCTION 時當下參數的值保存為輸入函數時所要應用的值。

RESTRICT

此語法會被忽略，它只為了符合 SQL 標準。

範例

要將 integer 型別的函數 sqrt 重新命名為 square_root：

要將整數型別函數 sqrt 的擁有者變更為 joe，請執行以下操作：

要將整數型別函數 sqrt 的綱要變更為 maths，請執行以下操作：

要將 integer 型別的函數標記為相依於延伸套件 mathlib：

自動以該函數調整搜尋路徑：

要停用某個函數的 search_path 自動設定，請執行以下操作：

該函數現在將執行其呼叫者使用的任何搜尋路徑。

相容性

此語法與 SQL 標準中的 ALTER FUNCTION 語法部分相容。標準可以允許修改一個函數的更多屬性，但不能提供重新命名函數、使函數成為 security definer、將配置參數值附加到函數或變更函數的擁有者、綱要或易變性的設定。標準還需要 RESTRICT 關鍵字，這在 PostgreSQL 中是選用的。

參閱

ALTER DEFAULT PRIVILEGES — 設定預設的存取權限

語法

說明

ALTER DEFAULT PRIVILEGES 使您可以設定將套用於未來建立物件的權限。（它不會影響指派給已存在物件的權限。）目前，只能變更改綱要、資料表（包括檢視表和外部資料表）、序列、函數和型別（包括 domain）的權限。對於此命令，函數包括彙總函數和程序函數。在這個命令中，單詞 FUNCTIONS 和 ROUTINES 是等效的。（ROUTINES 優先作為函數和程序的標準術語。在早期的 PostgreSQL 版本中，只允許使用單詞 FUNCTIONS。不可能單獨為函數和程序設定預設權限。）

您只能為將由您自己或您所屬角色建立的物件變更預設權限。可以全域設定權限（意即，對於在目前資料庫中建立的所有物件），或僅為在指定綱要中建立的物件設定權限。每個綱要指定的預設權限將指派到特定物件類型的全域預設權限。

參數

target_role

目前角色所屬的現有角色的名稱。 如果省略 FOR ROLE，則假設為目前角色。

schema_name

現有綱要的名稱。如果有指定的話，則會為稍後在該綱要中建立的物件變更預設權限。如果省略 IN SCHEMA，則變更全域的預設權限。使用 ON SCHEMAS 時不允許使用 IN SCHEMA，因為無法嵌套綱要。

role_name

注意

如果您希望移除已變更預設權限的角色，則必須撤消其預設權限的變更，或使用 DROP OWNED BY 刪除該角色的預設權限項目。

範例

為隨後在綱要 myschema 中所建立的所有資料表（和檢視表）授予每個人 SELECT 權限，並使角色 webuser 具備 INSERT 權限：

撤消上述內容，以便後續建立的資料表不會有更大於正常的權限：

對於角色 admin 隨後建立的所有函數，移除一般會在函數上授予給 PUBLIC 的 EXECUTE 權限：

相容性

SQL標準中沒有 ALTER DEFAULT PRIVILEGES 語句。

參閱

ALTER SUBSCRIPTION — change the definition of a subscription

語法

說明

ALTER SUBSCRIPTION 可以變更 中大部分可指定的訂閱屬性。

您必須是該訂閱的擁有者才能使用 ALTER SUBSCRIPTION。要變更擁有者，您必須是新角色的直接或間接成員，而新所有者必須是超級使用者。（目前，訂閱擁有者都必須是超級使用者，因此擁有者檢查將在實作中繞過，但未來這個部份有可能會發生變化。）

參數

name

屬性將被變更的訂閱名稱。

CONNECTION 'conninfo'

SET PUBLICATION publication_name

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] [, ... ] )

new_owner

訂閱的新擁有者的使用者名稱。

new_name

訂閱的新名稱。

範例

將訂閱的發佈對象變更為 insert_only：

停用（停止）訂閱：

相容性

ALTER SUBSCRIPTION 是 PostgreSQL 的延伸功能。

參閱

ALTER SEQUENCE — change the definition of a sequence generator

語法

說明

ALTER SEQUENCE 變更現有序列産生器的參數。ALTER SEQUENCE 指令中未特別設定的任何參數都會保留其先前的設定。

您必須擁有該序列才能使用 ALTER SEQUENCE。要變更序列的綱要，您還必須對新綱要具有 CREATE 權限。要變更擁有者，您還必須是新擁有角色直接或間接成員，並且該角色必須對序列的綱要具有 CREATE 權限。（這些限制強制要求變更擁有者不會透過刪除和重新建立序列來執行任何操作。但是，超級使用者無論如何都可以變更任何序列的所有權。）

參數

name

要變更的序列名稱（選擇性加上綱要）。

IF EXISTS

如果序列不存在，請不要拋出錯誤。 在這種情況下發出 NOTICE。

data_type

可選擇性子句 AS data_type 變更序列的資料型別。有效型別為 smallint，integer 和 bigint。

僅當先前的最小值和最大值是舊資料型別的最小值或最大值時，變更資料型別會自動變更序列的最小值和最大值（換句話說，如果序列是使用 NO MINVALUE 或 NO MAXVALUE，不論明確或隱含）。否則，將保留最小值和最大值，除非新值作為同一指令的一部分發出。如果最小值和最大值不適合新資料型別，則會産生錯誤。

increment

INCREMENT BY 增量子句是可選的。正值將產生遞增序列，負值將產生遞減序列。如果未指定，將保留舊的增量值。

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 相當於提供由 CREATE SEQUENCE 記錄的起始值或 ALTER SEQUENCE START WITH 最後設定的起始值。

與 setval 使用相反，序列上的 RESTART 操作是交易事務的，並阻止平行事務從同一序列中取得數字。如果這不是需要的操作模式，則應使用 setval。

cache

此子句 CACHE 高速快取使序列號碼能夠預先分配並儲存在記憶體中，以便更快地存取。最小值為 1（一次只能産生一個值，即沒有快取）。如果未指定，將保留舊的快取值。

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 的語法就只有上面列出的形式。

範例

將序列 serial 從 105 重新啟動：

相容性

ALTER SEQUENCE 符合 SQL 標準，除了 AS，START WITH，OWNED BY，OWNER TO，RENAME TO 和 SET SCHEMA 子句之外，它們是 PostgreSQL 的延伸功能。

參閱

ALTER ROLE — 變更資料庫角色

語法

說明

ALTER ROLE 變更 PostgreSQL 角色的屬性。

第二種語法樣式用於變更角色的名稱。資料庫超級使用者可以重新命名任何角色。具有 CREATEROLE 權限的角色可以重新命名非超級使用者角色。無法重新命名目前連線使用者。（如果需要，請以其他使用者身份進行連線。）由於 MD5 加密的密碼使用角色名稱作為加密 salt，因此如果密碼是 MD5 加密的，則重新命名角色會重置其加密密碼。

其餘語法樣式則是變更組態變數的角色連線預設值，或者為所有資料庫更改，或者在指定 IN DATABASE 子句時，僅針對指定名稱資料庫中的連線。如果指定了 ALL 而不是角色名稱，則會變更所有角色的設定。使用 ALL 與 IN DATABASE 實際上與使用指令 ALTER DATABASE ... SET .... 相同。

超級使用者可以變更任何人的連線預設值。具有 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'

new_name

角色的新名稱。

database_name

應在其中設定組態變數的資料庫名稱。

configuration_parameter value

將使指定組態參數覆寫此角色的連線預設值。如果 value 為 DEFAULT，或者等效地使用 RESET，則會移除特定於角色的組態參數，因此該角色將在新連線中繼承系統範圍的預設設定。使用 RESET ALL 清除所有特定於角色的設定。SET FROM CURRENT 將連線當下參數值保存為特定於角色的值。如果指定了 IN DATABASE，則僅為給定角色和資料庫設定或移除組態參數。

注意

使用此指令指定未加密的密碼時必須小心。密碼將以明文形式傳輸到伺服器，也可能記錄在用戶端的指令歷史記錄或伺服器日誌中。psql 包含一個指令 \password，可用於變更角色的密碼而不暴露明文密碼。

也可以將連線預設值綁定到特定資料庫而不是角色；請參閱 ALTER DATABASE。 如果存在衝突，則特定於資料庫角色的設定會覆蓋特定於角色的設定，而這些設定又會覆蓋特定於資料庫的設定。

範例

變更角色的密碼：

移除角色的密碼：

變更密碼到期日期，指定密碼將於 2015 年 5 月 4 日中午到期，使用比 UTC 提前一小時的時區：

使密碼永久有效：

賦予角色建立其他角色和新資料庫的能力：

相容性

ALTER ROLE 語句是 PostgreSQL 的延伸功能。

參閱

ALTER STATISTICS — change the definition of an extended statistics object

Synopsis

Description

ALTER STATISTICS changes the parameters of an existing extended statistics object. Any parameters not specifically set in the ALTER STATISTICS command retain their prior settings.

You must own the statistics object to use ALTER STATISTICS. To change a statistics object's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the statistics object's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the statistics object. However, a superuser can alter ownership of any statistics object anyway.)

Parameters

name

The name (optionally schema-qualified) of the statistics object to be altered.

new_owner

The user name of the new owner of the statistics object.

new_name

The new name for the statistics object.

new_schema

The new schema for the statistics object.

new_target

Compatibility

There is no ALTER STATISTICS command in the SQL standard.

See Also

ALTER POLICY — 變更資料列等級的安全原則定義

語法

描述

ALTER POLICY 用於變更現有資料列層級安全原則的定義。請注意，ALTER POLICY 只允許修改安全原則所適用的使用者們以及調整 USING 和 WITH CHECK 表示式。要更改安全原則的其他屬性，例如原則適用的指令，或者允許及限制原則，則必須刪除並重新建立安全原則。

要使用 ALTER POLICY 的話，你必須擁有該安全原則適用的資料表。

在 ALTER POLICY 的第二種形式中，指定的使用者角色列表、表示式和檢查表示式，將會被獨立替換。當其中一個子句被省略時，其原則相對應部分就不會改變。

參數

name

變更現有原則的名稱。

table_name

該原則所在的資料表名稱（可以加上 schema ）。

new_name

原則的新名稱。

role_name

原則所適用的使用者角色。可以同時指定多個角色。要將原則應用於所有角色，請使用 PUBLIC。

using_expression

check_expression

相容性

ALTER POLICY 是 PostgreSQL 所延伸支援的指令。

相關資訊

ALTER MATERIALIZED VIEW — change the definition of a materialized view

Synopsis

Description

ALTER MATERIALIZED VIEW changes various auxiliary properties of an existing materialized view.

You must own the materialized view to use ALTER MATERIALIZED VIEW. To change a materialized view's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the materialized view's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the materialized view. However, a superuser can alter ownership of any view anyway.)

The DEPENDS ON EXTENSION form marks the materialized view as dependent on an extension, such that the materialized view will automatically be dropped if the extension is dropped.

Parameters

name

The name (optionally schema-qualified) of an existing materialized view.

column_name

Name of a new or existing column.

extension_name

The name of the extension that the materialized view is to depend on.

new_column_name

New name for an existing column.

new_owner

The user name of the new owner of the materialized view.

new_name

The new name for the materialized view.

new_schema

The new schema for the materialized view.

Examples

To rename the materialized view foo to bar:

Compatibility

ALTER MATERIALIZED VIEW is a PostgreSQL extension.

See Also

本部分包含 PostgreSQL 支援的 SQL 指令的參考訊息。一般而言，「SQL」是指語言；內容包含了有關各標準的一致性和相容性。

連結將會連結至 PostgreSQL 官方使用手冊，本手冊連結請使用左側目錄。

Table of Contents

— abort the current transaction

— change the definition of an aggregate function

— change the definition of a collation

— change the definition of a conversion

— change a database

— define default access privileges

— change the definition of a domain

— change the definition of an event trigger

— change the definition of an extension

— change the definition of a foreign-data wrapper

— change the definition of a foreign table

— change the definition of a function

— change role name or membership

— change the definition of an index

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

可設定的組態參數的名稱。可用參數的說明在第 19 章之中。

value

參數的新值。可以將值指定為字串常數、指標、數字或以逗號分隔的列表，配合參數的要求。可以使用 DEFAULT 以從 postgresql.auto.conf 中刪除參數及其值。

注意

此命令不能用於設定 data_directory，也不能用於設定 postgresql.conf 中不允許的參數（例如，preset options）。

有關其他設定參數的方法，請參閱第 19.1 節。

範例

設定 wal_level：

ALTER SYSTEM SET wal_level = replica;

取消該設定，恢復在 postgresql.conf 中的設定：

ALTER SYSTEM RESET wal_level;

相容性

ALTER SYSTEM 語句是 PostgreSQL 的延伸功能。

參閱

SET, SHOW

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;

相容性

SQL 標準中沒有 ALTER TABLESPACE 語句。

參閱

CREATE TABLESPACE, DROP TABLESPACE

ALTER TRIGGER — change the definition of a trigger

Synopsis

ALTER TRIGGER name ON table_name RENAME TO new_name
ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name

Description

ALTER TRIGGER changes properties of an existing trigger. The RENAME clause changes the name of the given trigger without otherwise changing the trigger definition. The DEPENDS ON EXTENSION clause marks the trigger as dependent on an extension, such that if the extension is dropped, the trigger will automatically be dropped as well.

You must own the table on which the trigger acts to be allowed to change its properties.

Parameters

name

The name of an existing trigger to alter.table_name

The name of the table on which this trigger acts.new_name

The new name for the trigger.extension_name

The name of the extension that the trigger is to depend on.

Notes

The ability to temporarily enable or disable a trigger is provided by ALTER TABLE, not by ALTER TRIGGER, because ALTER TRIGGER has no convenient way to express the option of enabling or disabling all of a table's triggers at once.

Examples

To rename an existing trigger:

ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;

To mark a trigger as being dependent on an extension:

ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib;

Compatibility

ALTER TRIGGER is a PostgreSQL extension of the SQL standard.

See Also

ALTER TABLE

ALTER TYPE — change the definition of a type

Synopsis

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

where action is one of:

    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 ]

Description

ALTER TYPE changes the definition of an existing type. There are several subforms:ADD ATTRIBUTE

This form adds a new attribute to a composite type, using the same syntax as CREATE TYPE.DROP ATTRIBUTE [ IF EXISTS ]

This form drops an attribute from a composite type. If IF EXISTS is specified and the attribute does not exist, no error is thrown. In this case a notice is issued instead.SET DATA TYPE

This form changes the type of an attribute of a composite type.OWNER

This form changes the owner of the type.RENAME

This form changes the name of the type or the name of an individual attribute of a composite type.SET SCHEMA

This form moves the type into another schema.ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]

This form adds a new value to an enum type. The new value's place in the enum's ordering can be specified as being BEFORE or AFTER one of the existing values. Otherwise, the new item is added at the end of the list of values.

If IF NOT EXISTS is specified, it is not an error if the type already contains the new value: a notice is issued but no other action is taken. Otherwise, an error will occur if the new value is already present.RENAME VALUE

This form renames a value of an enum type. The value's place in the enum's ordering is not affected. An error will occur if the specified value is not present or the new name is already present.

The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several attributes and/or alter the type of several attributes in a single command.

You must own the type to use ALTER TYPE. To change the schema of a type, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the type's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the type. However, a superuser can alter ownership of any type anyway.) To add an attribute or alter an attribute type, you must also have USAGE privilege on the data type.

Parameters

name

The name (possibly schema-qualified) of an existing type to alter.

new_name

The new name for the type.

new_owner

The user name of the new owner of the type.

new_schema

The new schema for the type.

attribute_name

The name of the attribute to add, alter, or drop.

new_attribute_name

The new name of the attribute to be renamed.

data_type

The data type of the attribute to add, or the new type of the attribute to alter.

new_enum_value

The new value to be added to an enum type's list of values, or the new name to be given to an existing value. Like all enum literals, it needs to be quoted.

neighbor_enum_value

The existing enum value that the new value should be added immediately before or after in the enum type's sort ordering. Like all enum literals, it needs to be quoted.

existing_enum_value

The existing enum value that should be renamed. Like all enum literals, it needs to be quoted.

CASCADE

Automatically propagate the operation to typed tables of the type being altered, and their descendants.

RESTRICT

Refuse the operation if the type being altered is the type of a typed table. This is the default.

Notes

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.

Comparisons involving an added enum value will sometimes be slower than comparisons involving only original members of the enum type. This will usually only occur if BEFORE or AFTER is used to set the new value's sort position somewhere other than at the end of the list. However, sometimes it will happen even though the new value is added at the end (this occurs if the OID counter “wrapped around” since the original creation of the enum type). The slowdown is usually insignificant; but if it matters, optimal performance can be regained by dropping and recreating the enum type, or by dumping and reloading the database.

Examples

To rename a data type:

ALTER TYPE electronic_mail RENAME TO email;

To change the owner of the type email to joe:

ALTER TYPE email OWNER TO joe;

To change the schema of the type email to customers:

ALTER TYPE email SET SCHEMA customers;

To add a new attribute to a type:

ALTER TYPE compfoo ADD ATTRIBUTE f3 int;

To add a new value to an enum type in a particular sort position:

ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';

To rename an enum value:

ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';

Compatibility

The variants to add and drop attributes are part of the SQL standard; the other variants are PostgreSQL extensions.

See Also

CREATE TYPE, DROP TYPE

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 ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name

where action is one of:

    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 expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
    ALTER [ 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 integer
    ALTER [ 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_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET TABLESPACE new_tablespace
    SET { LOGGED | UNLOGGED }
    SET ( storage_parameter [= value] [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | 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 AS ( generation_expr ) STORED |
  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 referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ 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 referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint_using_index is:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ 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 ]

從該資料表中刪除一個欄位。涉及該欄位的索引和資料表限制條件也將自動刪除。如果刪除的欄位會導致統計信息僅包含單個欄位的資料的話，那麼引用刪除欄位的多變量統計數據也將被刪除。如果資料表外的任何內容取決於該欄位，例如外部鍵引用或 view，則需要使用 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 ]

這個語法會變更欄位是否為標識欄位(identity column)或變更現有標識欄位的生成屬性。有關詳細訊息，請參閱 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）。有關 PostgreSQL 查詢規劃器使用統計訊息的更多資訊，請參閱第 14.2 節。

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 以恢復到一般性估計不同值的數量。有關 PostgreSQL 查詢規劃器使用統計資訊的更多訊息，請參閱第 14.2 節。

變更每個屬性選項會要求取得一個 SHARE UPDATE EXCLUSIVE 鎖定。

SET STORAGE

此語法設定欄位的儲存模式。 這將控制此欄位是以內建方式保存還是以輔助 TOAST 方式保存，以及是否應該壓縮資料。PLAIN 必須用於固定長度值（如整數），並且是內建的，未壓縮的。MAIN 用於內建可壓縮資料。EXTERNAL 用於外部未壓縮資料，EXTENDED 用於外部壓縮資料。EXTENDED 是非 PLAIN 儲存的大多數資料型別的預設值。 使用 EXTERNAL 將使得對非常大的字串和 bytea 值進行子字串處理的速度更快，從而增加儲存空間。請注意，SET STORAGE 本身並不會改變資料表中的任何內容，它只是設定在將來的資料表更新期間追求的策略。有關更多訊息，請參閱第 68.2 節。

ADD table_constraint [ NOT VALID ]

此語法用於與 CREATE TABLE 相同的語法為資料表加上一個新的限制條件，並可以加上選項 NOT VALID，該選項目前只允許用於外部鍵和 CHECK 限制條件。如果限制條件被標記為 NOT VALID，則跳過用於驗證資料表中的所有資料列滿足限制條件的冗長初始檢查。對於後續的插入或更新，這個檢查仍然會被執行（也就是說，除非在被引用的資料表中存在有匹配的資料，否則在外部鍵的情況下它們將會失敗；並且除非新的資料列匹配指定的檢查，否則它們將會失敗）。但是，資料庫不會假定該限制條件適用於資料表中的所有的資料，直到透過使用 VALIDATE CONSTRAINT 選項進行驗證。

ADD table_constraint_using_index

此語法根據現有的唯一索引向資料表中增加新的 PRIMARY KEY 或 UNIQUE 限制條件。索引中的所有欄位都將包含在限制條件裡。

索引不能有表示式欄位，也不能是部分索引。此外，它必須是具有隱含排序順序的 b-tree 索引。這些限制可確保索引等同於由常態的 ADD PRIMARY KEY 或 ADD UNIQUE 指令建立的索引。

如果指定了 PRIMARY KEY，並且索引的欄位尚未標記為 NOT NULL，那麼此命令將嘗試對每個此類的欄位執行 ALTER COLUMN SET NOT NULL。這需要全資料表掃描來驗證列不包含空值。在所有其他情況下，這是一項快速的操作。

如果提供限制條件名稱，那麼索引將被重新命名以匹配限制條件名稱。否則，限制條件將被命名為與索引相同。

執行此命令後，索引由該限制條件「擁有」，就像索引由一般的 ADD PRIMARY KEY 或 ADD UNIQUE 命令建立的一樣。特別要注意是，刪除限制條件會使索引消失。

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 的觸發器將觸發，不論目前的複複模式為何。

此指令會取得一個 SHARE ROW EXCLUSIVE 鎖定。

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE

這個語法設定屬於資料表的覆寫規則的觸發。禁用的規則仍為系統所知的，但在查詢覆寫期間不適用。其語法義意和禁用/啟用觸發器一樣。對於 ON SELECT 規則，將忽略此設定，即使目前連線處於非預設的複製角色，也始終使用此設定以保持 view 能正常工作。

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 系統欄位（參閱第 5.4 節）。 如果資料表已經有 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 }

此子句會將資料表從無日誌資料表變更為有日誌資料表或反之亦然（請參閱 UNLOGGED）。它不能用於臨時資料表。

SET ( storage_parameter = value [, ... ] )

此子句變更資料表的一個或多個儲存參數。有關可用參數的詳細訊息，請參閱儲存參數選項。請注意，這個指令不會立即修改資料表內容；根據參數，您可能需要重填資料表以獲得所需的效果。這可以透過 VACUUM FULL、CLUSTER或強制重填資料表的 ALTER TABLE 形式來完成。對於與規劃器相關的參數，更改將在下次資料表鎖定時生效，因此目前執行的查詢不會受到影響。

SHARE UPDATE EXCLUSIVE 會針對 fillfactor 和 autovacuum 儲存參數以及以下計劃程序的相關參數進行鎖定：effective_io_concurrency，parallel_workers，seq_page_cost，random_page_cost，n_distinct 和 n_distinct_inherited。

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

該子句將資料表、序列、檢視表、具體化檢視表或外部資料表的擁有者變更為指定的使用者。

REPLICA IDENTITY

此子句變更寫入 WAL 的訊息，以識別更新或刪除的資料列。如果正在使用邏輯複製的話，則此子句不起作用。DEFAULT（非系統資料表的預設值）記錄主鍵欄位的舊值（如果有的話）。USING INDEX 記錄指定索引覆蓋欄位的舊值，它必須是唯一的，不能是部分的，也不可是延遲的，並且只能包含標記為 NOT NULL 的欄位。FULL 記錄行中所有欄位的舊值。 沒有記錄關於舊資料列的訊息。（這是系統資料表的預設值。）在任何情況下，都不記錄舊值，除非至少有一個將記錄的欄位在新舊版本的資料列之間不同。

RENAME

給資料表一個新的名稱。RENAME 子句變更資料表的名稱（或索引、序列、檢視表、具體化檢視表或外部資料表）、資料表中各別的欄位名稱、及資料表的限制條件名稱。對於儲存的資料沒有任何影響。

SET SCHEMA

此子句將資料表移動到另一個綱要之中。資料表的關聯索引、約束和序列也將被移動。

ATTACH PARTITION partition_name FOR VALUES partition_bound_spec

此子句使用與 CREATE TABLE 相同的 partition_bound_spec 語法，將現有資料表（可能本身已為分區割資料表）作為目標資料表的分割區。 分割區綁定規範必須對應於目標資料表的分割區限制條件和分割區主鍵。要附加的資料表必須與目標資料表具有相同的欄位，並且不得再更多；此外，欄位型別也必須匹配。而且，它必須具有目標資料表的所有 NOT NULL 和 CHECK 限制條件。目前暫不考慮UNIQUE，PRIMARY KEY 和 FOREIGN KEY 限制條件。如果附加資料表中的任何 CHECK 限制條件被標記為 NO INHERIT，則此指令將會失敗；這種限制條件必須在沒有 NO INHERIT 子句的情況下重新建立。

如果新的分割區是一般資料表，則執行全資料表掃描以檢查資料表中的現有資料列是否違反分割區的限制條件。透過在資料表中加入一個有效的 CHECK 限制條件來避免這種掃描，在執行此命令之前，只允許滿足所需分割區限制條件的資料列。資料庫將使用這樣的限制條件來確定，即不需要掃描資料表來驗證分割區的合法性。但是，如果任何分割區鍵是表示式並且分割區不接受 NULL 值，則這個語法不起作用。 如果附加一個不接受 NULL 值的列表分割區，除非它是一個表示式，否則請將 NOT NULL 限制條件加到到分割區鍵欄位。

如果新的分割區是外部資料表，則不會執行任何操作來驗證外部資料表中的所有資料都遵守分割區限制條件。（請參閱 CREATE FOREIGN TABLE 中有關外部資料表上限制條件的說明。）

DETACH PARTITION partition_name

此子句會分離目標資料表的指定分割區。 分離的分割區作為獨立資料表繼續存在，只是不再與原來的資料表相關聯。

除了 RENAME，SET SCHEMA，ATTACH PARTITION 和 DETACH PARTITION 之外，所有在單個資料表上作用的 ALTER TABLE 子句可以組合成一個或多個變更的列表一起使用。例如，可以在單個命令中加入多個欄位（及/或）變更多個欄位的型別。這對於大型資料表尤其有用，因為只需要在資料表上進行一次操作。

您必須擁有該資料表才能使用 ALTER TABLE。要變更資料表的綱要或資料表空間，還必須對新的綱要或資料表空間具有 CREATE 權限。要將資料表加上為父資料表的新子資料表，您也必須擁有父資料表。另外，要將資料表附加為另一個資料表的新分割區，您必須擁有附加的資料表。要變更擁有者，您還必須是新擁有角色的直接或間接成員，並且該角色必須對資料表具有 CREATE 權限。（這些限制強制改變擁有者不會做任何刪除和重新建立資料表的操作，但超級用戶可以改變任何資料表的所有權。）要加入欄位或更改欄位型別或使用 OF 子句中，您還必須具有資料型別的 USAGE 權限。

參數

IF EXISTS

如果資料表不存在，請不要拋出錯誤。在這種情況下發布 NOTICE。

name

要變更的現有資料表名稱（可以加上綱要指定）。如果在資料表名稱之前指定了 ONLY，則只變更改該資料表。如果沒有指定 ONLY，則資料表及其所有繼承的資料表（如果有的話）都進行變更。或者，可以在資料表名稱之後指定 * 以明確指示包含繼承資料表。

column_name

新的欄位或現有欄位的名稱。

new_column_name

現有欄位的新名稱。

new_name

資料表的新名稱。

data_type

新欄位的資料型別或現有欄位的新資料型別。

table_constraint

資料表新的限制條件。

constraint_name

新的或現有限制條件的名稱。

CASCADE

自動刪除相依於刪除欄位或限制條件的物件（例如，引用欄位的檢視表），並依次刪除相依於這些物件的所有物件（請參閱第 5.13 節）。

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 子句，則為 NULL）。如果沒有 DEFAULT 子句的話，這只是一個結構的變更，而不需要立即更新資料表的資料，只是在讀出時加上 NULL 值。

使用 DEFAULT 子句增加欄位或變更現有欄位的型別會需要重寫整個資料表及其索引。變更現有欄位型別時的例外情況，如果 USING 子句不變更欄位內容，並且舊型別可以是新型別的二進制強製或新類型的不受限制的 domain，則不需要重寫資料表；但受影響欄位上的任何索引仍必須重建。增加或刪除系統 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 加上合適的新預設值。類似的考量適用於涉及該欄位的索引和限制條件。

如果資料表有任何後代資料表，則不允許在父資料表中增加、重新命名或變更欄位的型別，卻不對後代資料進行相同操作。這確保了後代資料總是有與父代資料匹配的欄位。同樣，如果不在所有後代資料表中重新命名限制條件，則不能在父級資料表中重新命名該限制條件，以便限制條件在父代資料及其後代資料表之間也匹配。此外，因為從父代資料中查詢也會從其後代資料中進行查詢，所以對父代資料表的限制條件不能被標記為有效，除非它對於那些後代資料表也被標記為有效。在所有這些情況下，ALTER TABLE ONLY 將會被拒絕。

遞迴的 DROP COLUMN 操作只有在後代資料表不從其他父代繼承該欄位並且從未擁有該欄位的獨立定義的情況下才會刪除後代資料表欄位。非遞迴 DROP COLUMN（即，ALTER TABLE ONLY ... DROP COLUMN）永遠不會刪除任何後代欄位，而是將它們標記為獨立定義而非繼承。對於分割區資料表，非遞迴 DROP COLUMN 命令將會失敗，因為資料表的所有分割區必須與分割區源頭具有相同的欄位。

識別欄位（ADD GENERATED，SET 等，DROP IDENTITY）的行為以及TRIGGER，CLUSTER，OWNER 和 TABLESPACE 行為絕不會遞迴到後代資料表；也就是說，他們總是像只有被指定的那樣行事。增加限制條件僅針對未標記為 NO INHERIT 的 CHECK constraints 遞迴。

變更系統目錄資料表的任何部分都不會允許。

有關有效參數的更多描述，請參閱 CREATE TABLE。第 5 章則有關於繼承的更多訊息。

範例

要將一個 varchar 型別的欄位加到資料表中，請執行以下操作指令：

ALTER TABLE distributors ADD COLUMN address varchar(30);

從資料表中刪除一個欄位：

ALTER TABLE distributors DROP COLUMN address RESTRICT;

在一個操作指令中變更兩個現有欄位的型別：

ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);

透過 USING 子句將包含 Unix 時間戳記的整數欄位變更為帶有時區的時間戳記：

ALTER TABLE foo
    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

同樣，當有一個欄位沒有自動轉換為新資料型別的預設表示式時：

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

重新命名現有的欄位：

ALTER TABLE distributors RENAME COLUMN address TO city;

重新命名現有的資料表：

ALTER TABLE distributors RENAME TO suppliers;

重新命名現有的限制條件：

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

要將欄位加上 not null 的限制條件：

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

從欄位中刪除 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;

將資料表移動到不同的 schema：

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 measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

將資料表分割區附加到列表型的分割資料表中：

ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

從分割資料表中分離資料表分割區：

ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;

相容性

ADD（沒有 USING INDEX）、DROP [COLUMN]、DROP IDENTITY、RESTART、SET DEFAULT、SET DATA TYPE（沒有 USING）、SET GENERATED 和 SET sequence_option 的語法是符合 SQL 標準的。其他語法則是 SQL 標準的 PostgreSQL 延伸語法。此外，在單個 ALTER TABLE 指令中進行多個操作的功能也是延伸語法。

ALTER TABLE DROP COLUMN 可用於刪除資料表的單一欄位，而留下一個沒有欄位的資料表。這是 SQL 的延伸，SQL 標準禁止使用無欄位的資料表。

參閱

CREATE TABLE

ALTER USER — 變更資料庫角色屬性

語法

ALTER USER role_specification [ WITH ] option [ ... ]

where option can be:

      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_name

ALTER 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

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

說明

ALTER USER 現在是 ALTER ROLE 的別名。

相容性

ALTER USER 語句是 PostgreSQL 的延伸功能。SQL 標準將使用者的定義留給實作決定。

參閱

ALTER ROLE

COMMENT — 定義或變更物件的註解

語法

COMMENT ON
{
  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  COLUMN relation_name.column_name |
  CONSTRAINT constraint_name ON table_name |
  CONSTRAINT constraint_name ON DOMAIN domain_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  EXTENSION object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  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 |
  POLICY policy_name ON table_name |
  [ PROCEDURAL ] LANGUAGE object_name |
  PUBLICATION object_name |
  ROLE object_name |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  STATISTICS object_name |
  SUBSCRIPTION object_name |
  TABLE object_name |
  TABLESPACE 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 |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name
} IS 'text'

where aggregate_signature is:

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

說明

COMMENT 儲存有關資料庫物件的註解。

每個物件僅能儲存一個註解字串，因此要修改註解，請為同一物件發出新的 COMMENT 指令。 要刪除註解，請寫入 NULL 代替文字字串。當物件被移除時，註解也會自動移除。

對於大多數類型的物件，只有物件的擁有者才能設定註解。角色本身沒有擁有者，因此 COMMENT ON ROLE 的規則是您必須是超級使用者才能對超級使用者角色註解，或具有 CREATEROLE 權限才能對非超級使用者角色註解。 同樣，存取方法也沒有擁有者；所以您必須是超級使用者才能對存取方法留下註解。 當然，超級使用者可以對任何物件註解。

可以使用psql的 \d 系列指令查看註解。其他使用者界面要檢索註解的話，可以使用 psql 相同內建函數的建置，即 obj_description，col_description 和 shobj_description（請參閱表格 9.68）。

參數

object_name relation_name.column_name aggregate_name constraint_name function_name operator_name policy_name rule_name trigger_name

要註釋的物件名稱。Table、aggregate、collation、conversion、domain、foreign table、function、index、operator、operator class、sequence、statistics、text search object、type、view 的名稱，並且可以是指定 schema。在對欄位進行註釋時，relation_name 必須引用資料表、檢視表、複合型別或外部資料表。

table_name domain_name

在 constraint、trigger、rule 或 policy 上建立註釋時，這些參數指定定義該物件的資料表或 domain 名稱。

source_type

來源資料型別轉換的名稱。

target_type

目標資料型別轉換的名稱。

argmode

函數或彙總函數的模式：IN，OUT，INOUT 或 VARIADIC。如果省略，則預設為 IN。請注意，COMMENT 實際上並不關心 OUT 參數，因為只需要輸入參數來決定函數的識別。因此，列出 IN，INOUT 和 VARIADIC 參數就足夠了。

argname

函數或彙總參數的名稱。請注意，COMMENT 實際上並不關心參數名稱，因為只需要參數資料型別來決定函數的識別。

argtype

函數或彙總參數的資料型別。

large_object_oid

large object 的 OID。

left_type right_type

運算子參數的資料型別（可加上綱要名稱）。使用 NONE 表示缺少前綴或後綴運算子的參數。

PROCEDURAL

這是一個噪音詞。

type_name

轉換的資料型別名稱。

lang_name

變換語言的名稱。

text

新的註解，寫成字串文字；或 NULL 以刪除註解。

注意

目前並沒有用於查看註解的安全機制：連線到資料庫的任何使用者可以看到該資料庫中的所有物件註解。對於資料庫而言，角色和資料表空間等共享物件，註解將以全域儲存，因此連線到叢集中任何資料庫的任何使用者都可以看到共享物件的所有註解。因此，請勿將安全關鍵訊息置於註解中。

範例

對資料表 mytable 加上註解：

COMMENT ON TABLE mytable IS 'This is my table.';

再來移除它：

COMMENT ON TABLE mytable IS NULL;

更多例子：

COMMENT ON ACCESS METHOD rtree IS 'R-Tree access method';
COMMENT ON AGGREGATE my_aggregate (double precision) IS 'Computes sample variance';
COMMENT ON CAST (text AS int4) IS 'Allow casts from text to int4';
COMMENT ON COLLATION "fr_CA" IS 'Canadian French';
COMMENT ON COLUMN my_table.my_column IS 'Employee ID number';
COMMENT ON CONVERSION my_conv IS 'Conversion to UTF8';
COMMENT ON CONSTRAINT bar_col_cons ON bar IS 'Constrains column col';
COMMENT ON CONSTRAINT dom_col_constr ON DOMAIN dom IS 'Constrains col of domain';
COMMENT ON DATABASE my_database IS 'Development Database';
COMMENT ON DOMAIN my_domain IS 'Email Address Domain';
COMMENT ON EXTENSION hstore IS 'implements the hstore data type';
COMMENT ON FOREIGN DATA WRAPPER mywrapper IS 'my foreign data wrapper';
COMMENT ON FOREIGN TABLE my_foreign_table IS 'Employee Information in other database';
COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral';
COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee ID';
COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures';
COMMENT ON LARGE OBJECT 346344 IS 'Planning document';
COMMENT ON MATERIALIZED VIEW my_matview IS 'Summary of order history';
COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts';
COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';
COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees';
COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees';
COMMENT ON POLICY my_policy ON mytable IS 'Filter rows by users';
COMMENT ON ROLE my_role IS 'Administration group for finance tables';
COMMENT ON RULE my_rule ON my_table IS 'Logs updates of employee records';
COMMENT ON SCHEMA my_schema IS 'Departmental data';
COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys';
COMMENT ON SERVER myserver IS 'my foreign server';
COMMENT ON STATISTICS my_statistics IS 'Improves planner row estimations';
COMMENT ON TABLE my_schema.my_table IS 'Employee Information';
COMMENT ON TABLESPACE my_tablespace IS 'Tablespace for indexes';
COMMENT ON TEXT SEARCH CONFIGURATION my_config IS 'Special word filtering';
COMMENT ON TEXT SEARCH DICTIONARY swedish IS 'Snowball stemmer for Swedish language';
COMMENT ON TEXT SEARCH PARSER my_parser IS 'Splits text into words';
COMMENT ON TEXT SEARCH TEMPLATE snowball IS 'Snowball stemmer';
COMMENT ON TRANSFORM FOR hstore LANGUAGE plpythonu IS 'Transform between hstore and Python dict';
COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for RI';
COMMENT ON TYPE complex IS 'Complex number data type';
COMMENT ON VIEW my_view IS 'View of departmental costs';

相容性

SQL 標準中並沒有 COMMENT 指令。

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

如果檢視表不存在，請不要拋出錯誤。在這種情況下發出 NOTICE。

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)

變更檢視表的 security-barrier 屬性。該值必須是布林值，也就是 true 或 false。

security_invoker (boolean)

Changes the security-invoker property of the view. The value must be a Boolean value, such as true or 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

相容性

ALTER VIEW 是基於 SQL 標準的 PostgreSQL 延伸功能。

參閱

CREATE VIEW, DROP VIEW

ANALYZE — 收集有關資料庫的統計資訊

語法

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

說明

ANALYZE 收集有關資料庫中資料表內容的統計資訊，並將結果儲在在 pg_statistic 系統目錄中。然後，查詢計劃程序會使用這些統計資訊來幫助決定查詢的最有效執行計劃。

如果沒有參數，ANALYZE 會檢查目前資料庫中的每個資料表。使用參數時，ANALYZE 僅檢查該資料表。還可以輸出欄位名稱列表，在這種情況下，僅收集這些欄位的統計資訊。

參數

VERBOSE

啟用進度訊息的顯示。

table_name

要分析的特定資料表的名稱（可以加入綱要名稱）。如果省略，則分析目前資料庫中的所有一般資料表，分割資料表和具體化檢視表（但不包括外部資料表）。如果指定的資料表是分割資料表，則更新分割資料表的繼承統計資訊和各個分割區的統計資訊。

column_name

要分析特定欄位的名稱。預設為所有欄位。

輸出

指定 VERBOSE 時，ANALYZE 會輸出進度訊息以顯示目前正在處理哪個資料表。還會列出有關資料表的各種統計資訊。

注意

僅在明確選擇時才會分析外部資料表。並非所有外部資料封裝器都支援 ANALYZE。如果資料表的封裝器不支援 ANALYZE，則該命令只會輸出警告並且不執行任何操作。

在預設的 PostgreSQL 配置中，autovacuum 背景程序（參閱第 24.1.6 節）負責在資料表首次載入資料時自動分析資料表，並在整個日常操作中進行變更。停用 autovacuum 時，最好定期運行 ANALYZE，或者在對資料表的內容進行大量變更後運行。準確的統計資訊可以幫助規劃程序選擇最合適的查詢計劃，從而提高查詢處理的效率。讀取主要資料庫的常見策略是在一天的離峰使用時間內每天運行一次 VACUUM 和 ANALYZE。（如果有大量更新活動的話，這是不夠的。）

ANALYZE 只需要對目標資料表執行讀取鎖定，因此它可以與資料表上的其他活動同時運行。

ANALYZE 收集的統計資訊通常包括每欄位中一些最常見值的列表，以及顯示每個欄位中近似資料分佈的直方圖。如果 ANALYZE 認為它們不感興趣（例如，在唯一鍵欄位中，沒有常見值）或者欄位資料型別不支援相應的運算子，則可以省略其中一個或兩個。第 24 章提供了有關統計資訊的更多訊息。

對於大型資料表，ANALYZE 採用資料表內容的隨機樣本，而不是檢查每一個資料列。這允許在很短的時間內分析非常大的資料表。但請注意，統計訊息只是近似值，並且每次運行 ANALYZE 時都會略有變化，即使實際資料表內容沒有變化也是如此。這可能會導致 EXPLAIN 顯示的計劃程序估算成本發生微小變化。在極少數情況下，這種非確定性會導致計劃程序在運行 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 將收集兩次統計訊息：一次僅在父資料表的資料列上，第二次在父資料表的資料列上及其所有子資料表。在規劃遍歷整個繼承樹的查詢時，需要第二組統計訊息。 但是，autovacuum 背景程序在決定是否觸發對該資料表的自動分析時，只會考慮父資料表本身的插入或更新。如果很少插入或更新該資料表，則除非您手動運行 ANALYZE，否則繼承統計訊息將不是最新的。

如果任何子資料表是外部資料封裝器不支援 ANALYZE 的外部資料表，則在收集繼承統計訊息時將忽略這些子資料表。

如果要分析的資料表完全為空，ANALYZE 將不會記錄該資料表的新統計訊息。任何現有統計資訊都會被保留。

相容性

SQL 標準中沒有 ANALYZE 語句。

參閱

VACUUM, vacuumdb, Section 19.4.4, Section 24.1.6

COMMIT PREPARED — commit a transaction that was earlier prepared for two-phase commit

Synopsis

COMMIT PREPARED transaction_id

Description

COMMIT PREPARED commits a transaction that is in prepared state.

Parameters

transaction_id

The transaction identifier of the transaction that is to be committed.

Notes

To commit a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in the same session that executed the transaction.

This command cannot be executed inside a transaction block. The prepared transaction is committed immediately.

All currently available prepared transactions are listed in the pg_prepared_xacts system view.

Examples

Commit the transaction identified by the transaction identifier foobar:

COMMIT PREPARED 'foobar';

Compatibility

COMMIT PREPARED is a PostgreSQL extension. It is intended for use by external transaction management systems, some of which are covered by standards (such as X/Open XA), but the SQL side of those systems is not standardized.

See Also

PREPARE TRANSACTION, ROLLBACK PREPARED

CLUSTER — cluster a table according to an index

Synopsis

CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

Description

CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name. The index must already have been defined on table_name.

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. (If one wishes, one can periodically recluster by issuing the command again. Also, setting the table's fillfactor storage parameter to less than 100% can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available there.)

When a table is clustered, PostgreSQL remembers which index it was clustered by. The form CLUSTER table_name reclusters the table using the same index as before. You can also use the CLUSTER or SET WITHOUT CLUSTER forms of ALTER TABLE to set the index to be used for future cluster operations, or to clear any previous setting.

CLUSTER without any parameter reclusters all the previously-clustered tables in the current database that the calling user owns, or all such tables if called by a superuser. This form of CLUSTER cannot be executed inside a transaction block.

When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.

Parameters

table_name

The name (possibly schema-qualified) of a table.index_name

The name of an index.VERBOSE

Prints a progress report as each table is clustered.

Notes

In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.

CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information.

When an index scan is used, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes.

When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. This method is often faster than the index scan method, but if the disk space requirement is intolerable, you can disable this choice by temporarily setting enable_sort to off.

It is advisable to set maintenance_work_mem to a reasonably large value (but not more than the amount of RAM you can dedicate to the CLUSTER operation) before clustering.

Because the planner records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly clustered table. Otherwise, the planner might make poor choices of query plans.

Because CLUSTER remembers which indexes are clustered, one can cluster the tables one wants clustered manually the first time, then set up a periodic maintenance script that executes CLUSTER without any parameters, so that the desired tables are periodically reclustered.

Examples

Cluster the table employees on the basis of its index employees_ind:

CLUSTER employees USING employees_ind;

Cluster the employees table using the same index that was used before:

CLUSTER employees;

Cluster all tables in the database that have previously been clustered:

CLUSTER;

Compatibility

There is no CLUSTER statement in the SQL standard.

The syntax

CLUSTER index_name ON table_name

is also supported for compatibility with pre-8.3 PostgreSQL versions.

See Also

clusterdb

CREATE ACCESS METHOD — define a new access method

Synopsis

CREATE ACCESS METHOD name
    TYPE access_method_type
    HANDLER handler_function

Description

CREATE ACCESS METHOD creates a new access method.

The access method name must be unique within the database.

Only superusers can define new access methods.

Parameters

name

The name of the access method to be created.

access_method_type

This clause specifies the type of access method to define. Only TABLE and INDEX are supported at present.

handler_function

handler_function is the name (possibly schema-qualified) of a previously registered function that represents the access method. The handler function must be declared to take a single argument of type internal, and its return type depends on the type of access method; for TABLE access methods, it must be table_am_handler and for INDEX access methods, it must be index_am_handler. The C-level API that the handler function must implement varies depending on the type of access method. The table access method API is described in Chapter 60 and the index access method API is described in Chapter 61.

Examples

Create an index access method heptree with handler function heptree_handler:

CREATE ACCESS METHOD heptree TYPE INDEX HANDLER heptree_handler;

Compatibility

CREATE ACCESS METHOD is a PostgreSQL extension.

See Also

DROP ACCESS METHOD, CREATE OPERATOR CLASS, CREATE OPERATOR FAMILY

CREATE DATABASE — 建立一個新的資料庫

語法

說明

CREATE DATABASE 建立一個新的 PostgreSQL 資料庫。

要建立資料庫，您必須是超級使用者或具有特殊的 CREATEDB 權限。請參閱 。

預設情況下，將透過複製標準系統資料庫 template1 來建立新的資料庫。可以透過修改 TEMPLATE 名稱來指定不同的樣板。特別是，通過修改 TEMPLATE template0，您可以建立一個僅包含您的 PostgreSQL 版本預定義的標準物件的原始資料庫。如果您希望避免複製可能已添加到 template1 的任何本地物件，這將非常有用。

參數

name

要建立的資料庫名稱。

user_name

將擁有新資料庫的使用者角色名稱，或 DEFAULT 使用預設值（即執行指令的使用者）。要建立由其他角色擁有的資料庫，您必須是該角色的直接或間接成員，或者是超級使用者。

template

從中建立新資料庫的樣板名稱，或 DEFAULT 以使用預設樣板（template1）。

encoding

lc_collate

要在新資料庫中使用的排列順序（LC_COLLATE）。這會影響套用於字串的排序順序，例如在使用 ORDER BY的查詢中，以及在文字欄位索引中使用的順序。預設設定是使用樣板資料庫的排序順序。其他限制請參閱下面說明。

lc_ctype

要在新資料庫中使用的字元分類（LC_CTYPE）。這會影響字元的分類，例如小寫、大寫和數字。預設設定是使用樣板資料庫的字元分類。其他限制請參閱下面的說明。

tablespace_name

allowconn

如果為 false，則沒有人可以連線到此資料庫。預設值為 true，允許連線（除非受到其他機制限制，例如 GRANT / REVOKE CONNECT）。

connlimit

可以對此資料庫建立多少同時連線。 -1（預設值）表示沒有限制。

istemplate

如果為 true，則任何具有 CREATEDB 權限的使用者都可以複製此資料庫；如果為false（預設值），則只有超級使用者或資料庫的擁有者才能複製它。

選擇性參數可以按任何順序輸入，而不僅僅是上面說明的順序。

注意

不能在交易事務區塊內執行 CREATE DATABASE。

「無法初始化資料庫目錄」的錯誤很可能與資料目錄，磁碟空間滿載或其他檔案系統的權限不足問題有關。

不會從樣板資料庫中複製資料庫級的組態參數（透過 ALTER DATABASE 設定）。

為新資料庫指定的字元集編碼必須與所選的區域設定（LC_COLLATE 和 LC_CTYPE）相容。如果語言環境是 C（或等效 POSIX），則允許所有編碼，但對於其他語言環境設定，只有一種編碼可以正常工作。（不過，在Windows上，UTF-8 編碼可以與任何語言環境一起使用。）CREATE DATABASE 將允許超級使用者指定 SQL_ASCII 編碼而不管語言環境設定如何，但是這種方式已被棄用。如果資料可能導致字串函數的不當行為，則與語言環境不相容的編碼就會儲存在資料庫中。

編碼和語言環境設定必須與樣板資料庫的設定相符合，除非將 template0 用作樣板。這是因為其他資料庫可能包含與指定編碼不相符合的資料，或者可能包含其排序順序受 LC_COLLATE 和 LC_CTYPE 影響的索引。複製此類資料將導致資料庫根據新設定而損壞。總之，template0 不包含任何會受影響的資料或索引。

CONNECTION LIMIT 選項僅近乎強制執行：如果兩個新連線幾乎同時開始，當資料庫只剩下一個連線「插槽」時，則兩者都可能會失敗。此外，不會對超級使用者或後台工作程序強制執行此限制。

範例

要建立新資料庫：

使用 salesspace 預設資料表空間並建立由使用者 salesapp 所擁有的資料庫 sales：

要使用不同的區域設定來建立資料庫 music：

在此範例中，如果指定的語言環境與 template1 中的語言環境不同，則需要 TEMPLATE template0 子句。（如果不是，則明確指定語言環境是多餘的。）

要建立具有不同區域設定和不同字元集編碼的資料庫 music2：

指定的區域設定和編碼設定必須相符合，否則將回報錯誤。

請注意，區域設定名稱專屬於作業系統，因此上述指令可能無法在其他地方以相同的方式工作。

相容性

SQL 標準中沒有 CREATE DATABASE 語句。資料庫等同於目錄，其建立是實作上定義的。

參閱

CREATE FOREIGN TABLE — define a new foreign table

Synopsis

Description

CREATE FOREIGN TABLEcreates a new foreign table in the current database. The table will be owned by the user issuing the command.

If a schema name is given (for example,CREATE FOREIGN TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. The name of the foreign table must be distinct from the name of any other foreign table, table, sequence, index, view, or materialized view in the same schema.

CREATE FOREIGN TABLEalso automatically creates a data type that represents the composite type corresponding to one row of the foreign table. Therefore, foreign tables cannot have the same name as any existing data type in the same schema.

IfPARTITION OFclause is specified then the table is created as a partition ofparent_tablewith specified bounds.

To be able to create a foreign table, you must haveUSAGEprivilege on the foreign server, as well asUSAGEprivilege on all column types used in the table.

Parameters

IF NOT EXISTS

Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created.

table_name

The name (optionally schema-qualified) of the table to be created.

column_name

The name of a column to be created in the new table.

data_type

COLLATE

collation

TheCOLLATEclause assigns a collation to the column (which must be of a collatable data type). If not specified, the column data type's default collation is used.

INHERITS (

parent_table

[, ... ] )

CONSTRAINT

constraint_name

An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages, so constraint names likecol must be positivecan be used to communicate helpful constraint information to client applications. (Double-quotes are needed to specify constraint names that contain spaces.) If a constraint name is not specified, the system generates a name.

NOT NULL

The column is not allowed to contain null values.

NULL

The column is allowed to contain null values. This is the default.

This clause is only provided for compatibility with non-standard SQL databases. Its use is discouraged in new applications.

CHECK (

expression

) [ NO INHERIT ]

TheCHECKclause specifies an expression producing a Boolean result which each row in the foreign table is expected to satisfy; that is, the expression should produce TRUE or UNKNOWN, never FALSE, for all rows in the foreign table. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns.

Currently,CHECKexpressions cannot contain subqueries nor refer to variables other than columns of the current row. The system columntableoidmay be referenced, but not any other system column.

A constraint marked withNO INHERITwill not propagate to child tables.

DEFAULT

default_expr

TheDEFAULTclause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column.

The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

server_name

OPTIONS (

option

'

value

' [, ...] )

Options to be associated with the new foreign table or one of its columns. The allowed option names and values are specific to each foreign data wrapper and are validated using the foreign-data wrapper's validator function. Duplicate option names are not allowed (although it's OK for a table option and a column option to have the same name).

Notes

Constraints on foreign tables (such asCHECKorNOT NULLclauses) are not enforced by the corePostgreSQLsystem, and most foreign data wrappers do not attempt to enforce them either; that is, the constraint is simply assumed to hold true. There would be little point in such enforcement since it would only apply to rows inserted or updated via the foreign table, and not to rows modified by other means, such as directly on the remote server. Instead, a constraint attached to a foreign table should represent a constraint that is being enforced by the remote server.

Some special-purpose foreign data wrappers might be the only access mechanism for the data they access, and in that case it might be appropriate for the foreign data wrapper itself to perform constraint enforcement. But you should not assume that a wrapper does that unless its documentation says so.

AlthoughPostgreSQLdoes not attempt to enforce constraints on foreign tables, it does assume that they are correct for purposes of query optimization. If there are rows visible in the foreign table that do not satisfy a declared constraint, queries on the table might produce incorrect answers. It is the user's responsibility to ensure that the constraint definition matches reality.

Examples

Create foreign tablefilms, which will be accessed through the serverfilm_server:

Create foreign tablemeasurement_y2016m07, which will be accessed through the serverserver_07, as a partition of the range partitioned tablemeasurement:

Compatibility

See Also

,

,

,

,

CREATE CAST — define a new cast

Synopsis

Description

CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types. For example,

converts the integer constant 42 to type float8 by invoking a previously specified function, in this case float8(int4). (If no suitable cast has been defined, the conversion fails.)

Two types can be binary coercible, which means that the conversion can be performed “for free” without invoking any function. This requires that corresponding values use the same internal representation. For instance, the types text and varchar are binary coercible both ways. Binary coercibility is not necessarily a symmetric relationship. For example, the cast from xml to text can be performed for free in the present implementation, but the reverse direction requires a function that performs at least a syntax check. (Two types that are binary coercible both ways are also referred to as binary compatible.)

You can define a cast as an I/O conversion cast by using the WITH INOUT syntax. An I/O conversion cast is performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type. In many common cases, this feature avoids the need to write a separate cast function for conversion. An I/O conversion cast acts the same as a regular function-based cast; only the implementation is different.

By default, a cast can be invoked only by an explicit cast request, that is an explicit CAST(x AS typename) or x::typename construct.

If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type. For example, supposing that foo.f1 is a column of type text, then:

will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT, otherwise not. (We generally use the term assignment cast to describe this kind of cast.)

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression. (We generally use the term implicit cast to describe this kind of cast.) For example, consider this query:

The parser initially marks the constants as being of type integer and numeric respectively. There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator. The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT — which in fact it is. The parser will apply the implicit cast and resolve the query as if it had been written

Now, the catalogs also provide a cast from numeric to integer. If that cast were marked AS IMPLICIT — which it is not — then the parser would be faced with choosing between the above interpretation and the alternative of casting the numeric constant to integer and applying the integer + integer operator. Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous. The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of a mixed numeric-and-integer expression as numeric; there is no built-in knowledge about that.

It is wise to be conservative about marking casts as implicit. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category. For example, the cast from int2 to int4 can reasonably be implicit, but the cast from float8 to int4 should probably be assignment-only. Cross-type-category casts, such as text to int4, are best made explicit-only.

Note

To be able to create a cast, you must own the source or the target data type and have USAGE privilege on the other type. To create a binary-coercible cast, you must be superuser. (This restriction is made because an erroneous binary-coercible cast conversion can easily crash the server.)

Parameters

source_type

The name of the source data type of the cast.

target_type

The name of the target data type of the cast.

function_name[(argument_type [, ...])]

The function used to perform the cast. The function name can be schema-qualified. If it is not, the function will be looked up in the schema search path. The function's result data type must match the target type of the cast. Its arguments are discussed below. If no argument list is specified, the function name must be unique in its schema.

WITHOUT FUNCTION

Indicates that the source type is binary-coercible to the target type, so no function is required to perform the cast.

WITH INOUT

Indicates that the cast is an I/O conversion cast, performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type.

AS ASSIGNMENT

Indicates that the cast can be invoked implicitly in assignment contexts.

AS IMPLICIT

Indicates that the cast can be invoked implicitly in any context.

Cast implementation functions can have one to three arguments. The first argument type must be identical to or binary-coercible from the cast's source type. The second argument, if present, must be type integer; it receives the type modifier associated with the destination type, or -1 if there is none. The third argument, if present, must be type boolean; it receives true if the cast is an explicit cast, false otherwise. (Bizarrely, the SQL standard demands different behaviors for explicit and implicit casts in some cases. This argument is supplied for functions that must implement such casts. It is not recommended that you design your own data types so that this matters.)

The return type of a cast function must be identical to or binary-coercible to the cast's target type.

Ordinarily a cast must have different source and target data types. However, it is allowed to declare a cast with identical source and target types if it has a cast implementation function with more than one argument. This is used to represent type-specific length coercion functions in the system catalogs. The named function is used to coerce a value of the type to the type modifier value given by its second argument.

When a cast has different source and target types and a function that takes more than one argument, it supports converting from one type to another and applying a length coercion in a single step. When no such entry is available, coercion to a type that uses a type modifier involves two cast steps, one to convert between data types and a second to apply the modifier.

A cast to or from a domain type currently has no effect. Casting to or from a domain uses the casts associated with its underlying type.

Notes

Remember that if you want to be able to convert types both ways you need to declare casts both ways explicitly.

It is normally not necessary to create casts between user-defined types and the standard string types (text, varchar, and char(n), as well as user-defined types that are defined to be in the string category). PostgreSQL provides automatic I/O conversion casts for that. The automatic casts to string types are treated as assignment casts, while the automatic casts from string types are explicit-only. You can override this behavior by declaring your own cast to replace an automatic cast, but usually the only reason to do so is if you want the conversion to be more easily invokable than the standard assignment-only or explicit-only setting. Another possible reason is that you want the conversion to behave differently from the type's I/O function; but that is sufficiently surprising that you should think twice about whether it's a good idea. (A small number of the built-in types do indeed have different behaviors for conversions, mostly because of requirements of the SQL standard.)

While not required, it is recommended that you continue to follow this old convention of naming cast implementation functions after the target data type. Many users are used to being able to cast data types using a function-style notation, that is typename(x). This notation is in fact nothing more nor less than a call of the cast implementation function; it is not specially treated as a cast. If your conversion functions are not named to support this convention then you will have surprised users. Since PostgreSQL allows overloading of the same function name with different argument types, there is no difficulty in having multiple conversion functions from different types that all use the target type's name.

Note

Actually the preceding paragraph is an oversimplification: there are two cases in which a function-call construct will be treated as a cast request without having matched it to an actual function. If a function call name(x) does not exactly match any existing function, but name is the name of a data type and pg_cast provides a binary-coercible cast to this type from the type of x, then the call will be construed as a binary-coercible cast. This exception is made so that binary-coercible casts can be invoked using functional syntax, even though they lack any function. Likewise, if there is no pg_cast entry but the cast would be to or from a string type, the call will be construed as an I/O conversion cast. This exception allows I/O conversion casts to be invoked using functional syntax.

Note

There is also an exception to the exception: I/O conversion casts from composite types to string types cannot be invoked using functional syntax, but must be written in explicit cast syntax (either CAST or :: notation). This exception was added because after the introduction of automatically-provided I/O conversion casts, it was found too easy to accidentally invoke such a cast when a function or column reference was intended.

Examples

To create an assignment cast from type bigint to type int4 using the function int4(bigint):

(This cast is already predefined in the system.)

Compatibility

The CREATE CAST command conforms to the SQL standard, except that SQL does not make provisions for binary-coercible types or extra arguments to implementation functions. AS IMPLICIT is a PostgreSQL extension, too.

See Also

CREATE EXTENSION — install an extension

語法

說明

CREATE EXTENSION loads a new extension into the current database. There must not be an extension of the same name already loaded.

Loading an extension essentially amounts to running the extension's script file. The script will typically create new SQL objects such as functions, data types, operators and index support methods. CREATE EXTENSION additionally records the identities of all the created objects, so that they can be dropped again if DROP EXTENSION is issued.

The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, and normally also becomes the owner of any objects created by the extension's script.

Loading an extension ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed. However, if the extension is marked trusted in its control file, then it can be installed by any user who has CREATE privilege on the current database. In this case the extension object itself will be owned by the calling user, but the contained objects will be owned by the bootstrap superuser (unless the extension's script explicitly assigns them to the calling user). This configuration gives the calling user the right to drop the extension, but not to modify individual objects within it.

參數

IF NOT EXISTS

Do not throw an error if an extension with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing extension is anything like the one that would have been created from the currently-available script file.

extension_name

The name of the extension to be installed. PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

schema_name

The name of the schema in which to install the extension's objects, given that the extension allows its contents to be relocated. The named schema must already exist. If not specified, and the extension's control file does not specify a schema either, the current default object creation schema is used.

If the extension specifies a schema parameter in its control file, then that schema cannot be overridden with a SCHEMA clause. Normally, an error will be raised if a SCHEMA clause is given and it conflicts with the extension's schema parameter. However, if the CASCADE clause is also given, then schema_name is ignored when it conflicts. The given schema_name will be used for installation of any needed extensions that do not specify schema in their control files.

Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.

version

The version of the extension to install. This can be written as either an identifier or a string literal. The default version is whatever is specified in the extension's control file.

CASCADE

Automatically install any extensions that this extension depends on that are not already installed. Their dependencies are likewise automatically installed, recursively. The SCHEMA clause, if given, applies to all extensions that get installed this way. Other options of the statement are not applied to automatically-installed extensions; in particular, their default versions are always selected.

註解

範例

將 hstore 延伸功能安裝到目前的資料庫中，將其物件置於 SCHEMA addons 中：

同樣結果的另一種做法：

相容性

CREATE EXTENSION 是 PostgreSQL 的延伸功能。

參閱

CREATE EVENT TRIGGER — define a new event trigger

Synopsis

Description

CREATE EVENT TRIGGER creates a new event trigger. Whenever the designated event occurs and the WHEN condition associated with the trigger, if any, is satisfied, the trigger function will be executed. For a general introduction to event triggers, see . The user who creates an event trigger becomes its owner.

Parameters

name

The name to give the new trigger. This name must be unique within the database.event

The name of the event that triggers a call to the given function. See for more information on event names.filter_variable

The name of a variable used to filter events. This makes it possible to restrict the firing of the trigger to a subset of the cases in which it is supported. Currently the only supported filter_variable is TAG.filter_value

A list of values for the associated filter_variable for which the trigger should fire. For TAG, this means a list of command tags (e.g., 'DROP FUNCTION').function_name

A user-supplied function that is declared as taking no argument and returning type event_trigger.

In the syntax of CREATE EVENT TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

Notes

Only superusers can create event triggers.

Examples

Compatibility

There is no CREATE EVENT TRIGGER statement in the SQL standard.

See Also

CREATE FOREIGN DATA WRAPPER — define a new foreign-data wrapper

Synopsis

CREATE FOREIGN DATA WRAPPER name
    [ HANDLER handler_function | NO HANDLER ]
    [ VALIDATOR validator_function | NO VALIDATOR ]
    [ OPTIONS ( option 'value' [, ... ] ) ]

Description

CREATE FOREIGN DATA WRAPPER creates a new foreign-data wrapper. The user who defines a foreign-data wrapper becomes its owner.

The foreign-data wrapper name must be unique within the database.

Only superusers can create foreign-data wrappers.

Parameters

name

The name of the foreign-data wrapper to be created.

HANDLER handler_function

handler_function is the name of a previously registered function that will be called to retrieve the execution functions for foreign tables. The handler function must take no arguments, and its return type must be fdw_handler.

It is possible to create a foreign-data wrapper with no handler function, but foreign tables using such a wrapper can only be declared, not accessed.

VALIDATOR validator_function

validator_function is the name of a previously registered function that will be called to check the generic options given to the foreign-data wrapper, as well as options for foreign servers, user mappings and foreign tables using the foreign-data wrapper. If no validator function or NO VALIDATOR is specified, then options will not be checked at creation time. (Foreign-data wrappers will possibly ignore or reject invalid option specifications at run time, depending on the implementation.) The validator function must take two arguments: one of type text[], which will contain the array of options as stored in the system catalogs, and one of type oid, which will be the OID of the system catalog containing the options. The return type is ignored; the function should report invalid options using the ereport(ERROR) function.

OPTIONS ( option 'value' [, ... ] )

This clause specifies options for the new foreign-data wrapper. The allowed option names and values are specific to each foreign data wrapper and are validated using the foreign-data wrapper's validator function. Option names must be unique.

Notes

PostgreSQL's foreign-data functionality is still under active development. Optimization of queries is primitive (and mostly left to the wrapper, too). Thus, there is considerable room for future performance improvements.

Examples

Create a useless foreign-data wrapper dummy:

CREATE FOREIGN DATA WRAPPER dummy;

Create a foreign-data wrapper file with handler function file_fdw_handler:

CREATE FOREIGN DATA WRAPPER file HANDLER file_fdw_handler;

Create a foreign-data wrapper mywrapper with some options:

CREATE FOREIGN DATA WRAPPER mywrapper
    OPTIONS (debug 'true');

Compatibility

CREATE FOREIGN DATA WRAPPER conforms to ISO/IEC 9075-9 (SQL/MED), with the exception that the HANDLER and VALIDATOR clauses are extensions and the standard clauses LIBRARY and LANGUAGE are not implemented in PostgreSQL.

Note, however, that the SQL/MED functionality as a whole is not yet conforming.

See Also

ALTER FOREIGN DATA WRAPPER, DROP FOREIGN DATA WRAPPER, CREATE SERVER, CREATE USER MAPPING, CREATE FOREIGN TABLE

CREATE DOMAIN — define a new domain

Synopsis

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

Description

CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.

If a schema name is given (for example, CREATE DOMAIN myschema.mydomain ...) then the domain is created in the specified schema. Otherwise it is created in the current schema. The domain name must be unique among the types and domains existing in its schema.

Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.

To be able to create a domain, you must have USAGE privilege on the underlying type.

Parameters

name

The name (optionally schema-qualified) of a domain to be created.

data_type

The underlying data type of the domain. This can include array specifiers.

collation

An optional collation for the domain. If no collation is specified, the underlying data type's default collation is used. The underlying type must be collatable if COLLATE is specified.

DEFAULT expression

The DEFAULT clause specifies a default value for columns of the domain data type. The value is any variable-free expression (but subqueries are not allowed). The data type of the default expression must match the data type of the domain. If no default value is specified, then the default value is the null value.

The default expression will be used in any insert operation that does not specify a value for the column. If a default value is defined for a particular column, it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.CONSTRAINT constraint_name

An optional name for a constraint. If not specified, the system generates a name.

NOT NULL

Values of this domain are prevented from being null (but see notes below).

NULL

Values of this domain are allowed to be null. This is the default.

This clause is only intended for compatibility with nonstandard SQL databases. Its use is discouraged in new applications.

CHECK (expression)

CHECK clauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the key word VALUE to refer to the value being tested. Expressions evaluating to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, an error is reported and the value is not allowed to be converted to the domain type.

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than VALUE.

When a domain has multiple CHECK constraints, they will be tested in alphabetical order by name. (PostgreSQL versions before 9.5 did not honor any particular firing order for CHECK constraints.)

Notes

Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));

The empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.

It is very difficult to avoid such problems, because of SQL's general assumption that a null value is a valid value of every data type. Best practice therefore is to design a domain's constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.

Examples

This example creates the us_postal_code data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US postal code:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY KEY,
  street1 TEXT NOT NULL,
  street2 TEXT,
  street3 TEXT,
  city TEXT NOT NULL,
  postal us_postal_code NOT NULL
);

Compatibility

The command CREATE DOMAIN conforms to the SQL standard.

See Also

ALTER DOMAIN, DROP DOMAIN

CREATE PROCEDURE — define a new procedure

Synopsis

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Description

CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have the USAGE privilege on the language.

If a schema name is included, then the procedure is created in the specified schema. Otherwise it is created in the current schema. The name of the new procedure must not match any existing procedure or function with the same input argument types in the same schema. However, procedures and functions of different argument types can share a name (this is called overloading).

To replace the current definition of an existing procedure, use CREATE OR REPLACE PROCEDURE. It is not possible to change the name or argument types of a procedure this way (if you tried, you would actually be creating a new, distinct procedure).

When CREATE OR REPLACE PROCEDURE is used to replace an existing procedure, the ownership and permissions of the procedure do not change. All other procedure properties are assigned the values specified or implied in the command. You must own the procedure to replace it (this includes being a member of the owning role).

The user that creates the procedure becomes the owner of the procedure.

To be able to create a procedure, you must have USAGE privilege on the argument types.

Parameters

name

The name (optionally schema-qualified) of the procedure to create.

argmode

The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. (OUT arguments are currently not supported for procedures. Use INOUT instead.)

argname

The name of an argument.

argtype

The data type(s) of the procedure's arguments (optionally schema-qualified), if any. The argument types can be base, composite, or domain types, or can reference the type of a table column.

Depending on the implementation language it might also be allowed to specify “pseudo-types” such as cstring. Pseudo-types indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types.