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

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

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

Table of Contents

ABORT — abort the current transaction

ALTER AGGREGATE — change the definition of an aggregate function

ALTER COLLATION — change the definition of a collation

ALTER CONVERSION — change the definition of a conversion

ALTER DATABASE — change a database

ALTER DEFAULT PRIVILEGES — define default access privileges

ALTER DOMAIN — change the definition of a domain

ALTER EVENT TRIGGER — change the definition of an event trigger

ALTER EXTENSION — change the definition of an extension

ALTER FOREIGN DATA WRAPPER — change the definition of a foreign-data wrapper

ALTER FOREIGN TABLE — change the definition of a foreign table

ALTER FUNCTION — change the definition of a function

ALTER GROUP — change role name or membership

ALTER INDEX — change the definition of an index

ALTER LANGUAGE — change the definition of a procedural language

ALTER LARGE OBJECT — change the definition of a large object

ALTER MATERIALIZED VIEW — change the definition of a materialized view

ALTER OPERATOR — change the definition of an operator

ALTER OPERATOR CLASS — change the definition of an operator class

ALTER OPERATOR FAMILY — change the definition of an operator family

ALTER POLICY — change the definition of a row-level security policy

ALTER PROCEDURE — change the definition of a procedure

ALTER PUBLICATION — change the definition of a publication

ALTER ROLE — change a database role

ALTER ROUTINE — change the definition of a routine

ALTER RULE — change the definition of a rule

ALTER SCHEMA — change the definition of a schema

ALTER SEQUENCE — change the definition of a sequence generator

ALTER SERVER — change the definition of a foreign server

ALTER STATISTICS — change the definition of an extended statistics object

ALTER SUBSCRIPTION — change the definition of a subscription

ALTER SYSTEM — change a server configuration parameter

ALTER TABLE — change the definition of a table

ALTER TABLESPACE — change the definition of a tablespace

ALTER TEXT SEARCH CONFIGURATION — change the definition of a text search configuration

ALTER TEXT SEARCH DICTIONARY — change the definition of a text search dictionary

ALTER TEXT SEARCH PARSER — change the definition of a text search parser

ALTER TEXT SEARCH TEMPLATE — change the definition of a text search template

ALTER TRIGGER — change the definition of a trigger

ALTER TYPE — change the definition of a type

ALTER USER — change a database role

ALTER USER MAPPING — change the definition of a user mapping

ALTER VIEW — change the definition of a view

ANALYZE — collect statistics about a database

BEGIN — start a transaction block

CALL — invoke a procedure

CHECKPOINT — force a write-ahead log checkpoint

CLOSE — close a cursor

CLUSTER — cluster a table according to an index

COMMENT — define or change the comment of an object

COMMIT — commit the current transaction

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

COPY — copy data between a file and a table

CREATE ACCESS METHOD — define a new access method

CREATE AGGREGATE — define a new aggregate function

CREATE CAST — define a new cast

CREATE COLLATION — define a new collation

CREATE CONVERSION — define a new encoding conversion

CREATE DATABASE — create a new database

CREATE DOMAIN — define a new domain

CREATE EVENT TRIGGER — define a new event trigger

CREATE EXTENSION — install an extension

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

CREATE FOREIGN TABLE — define a new foreign table

CREATE FUNCTION — define a new function

CREATE GROUP — define a new database role

CREATE INDEX — define a new index

CREATE LANGUAGE — define a new procedural language

CREATE MATERIALIZED VIEW — define a new materialized view

CREATE OPERATOR — define a new operator

CREATE OPERATOR CLASS — define a new operator class

CREATE OPERATOR FAMILY — define a new operator family

CREATE POLICY — define a new row-level security policy for a table

CREATE PROCEDURE — define a new procedure

CREATE PUBLICATION — define a new publication

CREATE ROLE — define a new database role

CREATE RULE — define a new rewrite rule

CREATE SCHEMA — define a new schema

CREATE SEQUENCE — define a new sequence generator

CREATE SERVER — define a new foreign server

CREATE STATISTICS — define extended statistics

CREATE SUBSCRIPTION — define a new subscription

CREATE TABLE — define a new table

CREATE TABLE AS — define a new table from the results of a query

CREATE TABLESPACE — define a new tablespace

CREATE TEXT SEARCH CONFIGURATION — define a new text search configuration

CREATE TEXT SEARCH DICTIONARY — define a new text search dictionary

CREATE TEXT SEARCH PARSER — define a new text search parser

CREATE TEXT SEARCH TEMPLATE — define a new text search template

CREATE TRANSFORM — define a new transform

CREATE TRIGGER — define a new trigger

CREATE TYPE — define a new data type

CREATE USER — define a new database role

CREATE USER MAPPING — define a new mapping of a user to a foreign server

CREATE VIEW — define a new view

DEALLOCATE — deallocate a prepared statement

DECLARE — define a cursor

DELETE — delete rows of a table

DISCARD — discard session state

DO — execute an anonymous code block

DROP ACCESS METHOD — remove an access method

DROP AGGREGATE — remove an aggregate function

DROP CAST — remove a cast

DROP COLLATION — remove a collation

DROP CONVERSION — remove a conversion

DROP DATABASE — remove a database

DROP DOMAIN — remove a domain

DROP EVENT TRIGGER — remove an event trigger

DROP EXTENSION — remove an extension

DROP FOREIGN DATA WRAPPER — remove a foreign-data wrapper

DROP FOREIGN TABLE — remove a foreign table

DROP FUNCTION — remove a function

DROP GROUP — remove a database role

DROP INDEX — remove an index

DROP LANGUAGE — remove a procedural language

DROP MATERIALIZED VIEW — remove a materialized view

DROP OPERATOR — remove an operator

DROP OPERATOR CLASS — remove an operator class

DROP OPERATOR FAMILY — remove an operator family

DROP OWNED — remove database objects owned by a database role

DROP POLICY — remove a row-level security policy from a table

DROP PROCEDURE — remove a procedure

DROP PUBLICATION — remove a publication

DROP ROLE — remove a database role

DROP ROUTINE — remove a routine

DROP RULE — remove a rewrite rule

DROP SCHEMA — remove a schema

DROP SEQUENCE — remove a sequence

DROP SERVER — remove a foreign server descriptor

DROP STATISTICS — remove extended statistics

DROP SUBSCRIPTION — remove a subscription

DROP TABLE — remove a table

DROP TABLESPACE — remove a tablespace

DROP TEXT SEARCH CONFIGURATION — remove a text search configuration

DROP TEXT SEARCH DICTIONARY — remove a text search dictionary

DROP TEXT SEARCH PARSER — remove a text search parser

DROP TEXT SEARCH TEMPLATE — remove a text search template

DROP TRANSFORM — remove a transform

DROP TRIGGER — remove a trigger

DROP TYPE — remove a data type

DROP USER — remove a database role

DROP USER MAPPING — remove a user mapping for a foreign server

DROP VIEW — remove a view

END — commit the current transaction

EXECUTE — execute a prepared statement

EXPLAIN — show the execution plan of a statement

FETCH — retrieve rows from a query using a cursor

GRANT — define access privileges

IMPORT FOREIGN SCHEMA — import table definitions from a foreign server

INSERT — create new rows in a table

LISTEN — listen for a notification

LOAD — load a shared library file

LOCK — lock a table

MERGE — conditionally insert, update, or delete rows of a table

MOVE — position a cursor

NOTIFY — generate a notification

PREPARE — prepare a statement for execution

PREPARE TRANSACTION — prepare the current transaction for two-phase commit

REASSIGN OWNED — change the ownership of database objects owned by a database role

REFRESH MATERIALIZED VIEW — replace the contents of a materialized view

REINDEX — rebuild indexes

RELEASE SAVEPOINT — destroy a previously defined savepoint

RESET — restore the value of a run-time parameter to the default value

REVOKE — remove access privileges

ROLLBACK — abort the current transaction

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

ROLLBACK TO SAVEPOINT — roll back to a savepoint

SAVEPOINT — define a new savepoint within the current transaction

SECURITY LABEL — define or change a security label applied to an object

SELECT — retrieve rows from a table or view

SELECT INTO — define a new table from the results of a query

SET — change a run-time parameter

SET CONSTRAINTS — set constraint check timing for the current transaction

SET ROLE — set the current user identifier of the current session

SET SESSION AUTHORIZATION — set the session user identifier and the current user identifier of the current session

SET TRANSACTION — set the characteristics of the current transaction

SHOW — show the value of a run-time parameter

START TRANSACTION — start a transaction block

TRUNCATE — empty a table or set of tables

UNLISTEN — stop listening for a notification

UPDATE — update rows of a table

VACUUM — garbage-collect and optionally analyze a database

VALUES — compute a set of rows

本參考資訊中的內容旨在提供適當長度的摘要，俱備一定的權威性、完整性和正式的性。本書的其他部分可以找到關於 PostgreSQL 使用的更多訊息，包括應用描述、教學或範例表單。 請參閱每個參考頁面上列出的參考資訊。

這些參考訊息也可以在傳統的「man」功能中取得。

ALTER MATERIALIZED VIEW — change the definition of a materialized view

Synopsis

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    action [, ... ]
ALTER MATERIALIZED VIEW name
    DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

where action is one of:

    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 }
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

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.

The statement subforms and actions available for ALTER MATERIALIZED VIEW are a subset of those available for ALTER TABLE, and have the same meaning when used for materialized views. See the descriptions for ALTER TABLE for details.

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:

ALTER MATERIALIZED VIEW foo RENAME TO bar;

Compatibility

ALTER MATERIALIZED VIEW is a PostgreSQL extension.

See Also

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

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

語法

ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

描述

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

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

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

參數

name

變更現有原則的名稱。

table_name

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

new_name

原則的新名稱。

role_name

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

using_expression

原則的 USING 表示式。 有關詳細訊息，請參閱 CREATE POLICY。

check_expression

原則的 WITH CHECK 表示式。有關詳細訊息，請參閱 CREATE POLICY。

相容性

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

相關資訊

CREATE POLICY, DROP POLICY

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 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 LANGUAGE — 變更程序語言的宣告

語法

說明

ALTER LANGUAGE 變更程序語言的宣告。唯一的功能是將語言重新命名或分配新的所有者。您必須是該語言所有者或超級使用者才能使用 ALTER LANGUAGE。

參數

name

語言名稱

new_name

語言的新名稱

new_owner

語言的新所有者e

相容性

SQL 標準中沒有 ALTER LANGUAGE 語句。

參閱

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 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 PUBLICATION — change the definition of a publication

Synopsis

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

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:

Add some tables to the publication:

Compatibility

ALTER PUBLICATION is a PostgreSQL extension.

See Also

ALTER STATISTICS — change the definition of an extended statistics object

Synopsis

ALTER STATISTICS name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER STATISTICS name RENAME TO new_name
ALTER STATISTICS name SET SCHEMA new_schema
ALTER STATISTICS name SET STATISTICS new_target

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

The statistic-gathering target for this statistics object for subsequent ANALYZE operations. The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using the maximum of the statistics target of the referenced columns, if set, or the system default statistics target (default_statistics_target). For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.

Compatibility

There is no ALTER STATISTICS command in the SQL standard.

See Also

CREATE STATISTICS, DROP STATISTICS

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 ROLE — 變更資料庫角色

語法

ALTER ROLE 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 ROLE name RENAME TO new_name

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

說明

ALTER ROLE 變更 PostgreSQL 角色的屬性。

語法中列出的此指令的第一種語法樣式可以變更可在 CREATE ROLE 中指定的許多角色屬性。（所有可能的屬性都會被覆寫，除了沒有增加或移除成員資格的選項之外，應使用 GRANT 和 REVOKE。）指令中未提及的屬性保留其先前的設定。資料庫超級使用者可以變更任何角色的任何設定。具有 CREATEROLE 權限的角色可以變更任何的這些設定，但僅適用於非超級使用者和非複寫角色。普通角色只能變更自己的密碼。

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

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

每當角色隨後啟動一個新連線時，指定的值將成為連線預設值，覆寫 postgresql.conf 中存在的任何設定或已從 postgres 命令列接收。這只發生在登入時；執行 SET ROLE 或 SET SESSION AUTHORIZATION 不會設定新的組態值。為所有資料庫的設定將由附加到角色的特定於資料庫的設定覆寫。特定資料庫或特定角色的設定會覆寫所有角色的設定。

超級使用者可以變更任何人的連線預設值。具有 CREATEROLE 權限的角色可以變更非超級使用者角色的預設值。普通角色只能為自己設定預設值。某些組態變數不能以這種方式設定，或者只能在超級使用者發出命令時設定。只有超級使用者才能變更所有資料庫中所有角色的設定。

參數

name

要變更其屬性的角色名稱。

CURRENT_USER

變更目前使用者而不是指定的角色。

SESSION_USER

變更目前連線使用者而不是指定的角色。

SUPERUSER NOSUPERUSER CREATEDB NOCREATEDB CREATEROLE NOCREATEROLE INHERIT NOINHERIT LOGIN NOLOGIN REPLICATION NOREPLICATION BYPASSRLS NOBYPASSRLS CONNECTION LIMIT connlimit [ ENCRYPTED ] PASSWORD 'password' PASSWORD NULL VALID UNTIL 'timestamp'

這些子句變更 CREATE ROLE 最初設定的屬性。有關更多訊息，請參閱 CREATE ROLE 參考頁面。

new_name

角色的新名稱。

database_name

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

configuration_parameter value

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

特定於角色的組態變數設定僅在登入時生效；SET ROLE 和 SET SESSION AUTHORIZATION 不處理特定於角色的組態變數設定。

有關可使用的參數名稱和內容的更多訊息，請參閱 SET 和第 19 章。

注意

使用 CREATE ROLE 增加新角色，使用 DROP ROLE 移除角色。

ALTER ROLE 無法變更角色的成員資格。請使用 GRANT 和 REVOKE 來做到這一點。

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

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

範例

變更角色的密碼：

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

移除角色的密碼：

ALTER ROLE davide WITH PASSWORD NULL;

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

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

使密碼永久有效：

ALTER ROLE fred VALID UNTIL 'infinity';

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

ALTER ROLE miriam CREATEROLE CREATEDB;

讓某個角色的 maintenance_work_mem 參數使用非預設值：

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

為 client_min_messages 參數指定一個非預設的，特定於某資料庫的設定：

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

相容性

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

參閱

CREATE ROLE, DROP ROLE, ALTER DATABASE, SET

ALTER SUBSCRIPTION — change the definition of a subscription

語法

ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name

說明

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

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

參數

name

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

CONNECTION 'conninfo'

此子句變更最初由 CREATE SUBSCRIPTION 設定的連線參數。請到該指令查看更多訊息。

SET PUBLICATION publication_name

變更訂閱發佈的列表。有關更多訊息，請參閱 CREATE SUBSCRIPTION。預設情況下，這個指令就如同 REFRESH PUBLICATION 一樣。

set_publication_option 為此操作指定了其他選項。支援的選項有：

refresh (boolean)

如果為 false，此指令將不會嘗試更新資料表訊息。REFRESH PUBLICATION 就應該要分開執行。預設值是 true。

此外，可能需要指定更新選項，如 REFRESH PUBLICATION 中所述。

REFRESH PUBLICATION

從發佈者取得缺少的資料表訊息。這將開始複寫自從上次呼叫 REFRESH PUBLICATION 或自從 CREATE SUBSCRIPTION 以來已加到訂閱發佈中的資料表。

refresh_option 指定更新操作的附加選項。支援的選項有：

copy_data (boolean)

指定在複寫開始之後是否應複寫正在訂閱的發佈中的現有資料。預設值是 true。

ENABLE

啟用先前停用的訂閱，在交易事務結束時啟動邏輯複寫程序。

DISABLE

停用正在運行的訂閱，在交易事務結束時停止邏輯複寫的工作。

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

此子句變更最初由 CREATE SUBSCRIPTION 設定的參數。查看該指令取得更多訊息。允許的選項是 slot_name 和 synchronous_commit

new_owner

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

new_name

訂閱的新名稱。

範例

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

ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;

停用（停止）訂閱：

ALTER SUBSCRIPTION mysub DISABLE;

相容性

ALTER SUBSCRIPTION 是 PostgreSQL 的延伸功能。

參閱

CREATE SUBSCRIPTION, DROP SUBSCRIPTION, CREATE PUBLICATION, ALTER PUBLICATION

ALTER TABLESPACE — 變更資料表空間的定義

語法

說明

ALTER TABLESPACE 可用於變更資料表空間的定義。

您必須擁有該資料表空間才能變更資料表空間的定義。要改變擁有者，您還必須是新角色的直接或間接成員。（請注意，超級使用者自動擁有這些權限。）

參數

name

現有資料表空間的名稱。

new_name

資料表空間的新名稱。新名稱不能以「pg_」開頭，因為這些名稱是為系統資料表空間保留的。

new_owner

資料表空間的新擁有者。

tablespace_option

範例

將資料表空間 index_space 重新命名為 fast_raid：

變更資料表空間 index_space 的擁有者：

相容性

SQL 標準中沒有 ALTER TABLESPACE 語句。

參閱

ALTER TYPE — change the definition of a type

Synopsis

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 .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:

To change the owner of the type email to joe:

To change the schema of the type email to customers:

To add a new attribute to a type:

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

To rename an enum value:

Compatibility

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

See Also

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 的延伸功能。

參閱

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

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

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

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

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 指令。

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

Synopsis

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 system view.

Examples

Commit the transaction identified by the transaction identifier 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

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 EXTENSION — install an extension

語法

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ CASCADE ]

說明

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.

註解

Before you can use CREATE EXTENSION to load an extension into a database, the extension's supporting files must be installed. Information about installing the extensions supplied with PostgreSQL can be found in Additional Supplied Modules.

The extensions currently available for loading can be identified from the pg_available_extensions or pg_available_extension_versions system views.

有關設計新的延伸功能相關資訊，請參閱第 38.17 節。

範例

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

CREATE EXTENSION hstore SCHEMA addons;

同樣結果的另一種做法：

SET search_path = addons;
CREATE EXTENSION hstore;

相容性

CREATE EXTENSION 是 PostgreSQL 的延伸功能。

參閱

ALTER EXTENSION, DROP EXTENSION

CREATE INDEX — 定義一個新的索引

語法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

說明

CREATE INDEX 在指定關連的指定欄位上建構索引，該索引可以是資料表或具體化檢視表。索引主要用於增強資料庫效能（儘管不恰當的使用會導致效能降低）。

索引的主要欄位指定欄位名稱，或者作為括號中的表示式指定。如果索引方法支援多欄位索引，則可以指定多個欄位。

索引欄位可以是根據資料表的一個欄位或多個欄位的計算表示式。此功能可用於基於對基本資料的某些轉換來快速存取資料。例如，在 upper(col) 上計算的索引將允許子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 提供索引方法 B-tree，hash，GiST，SP-GiST，GIN 和 BRIN。使用者也可以定義自己的索引方法，但這相當複雜。

WHERE 子句存在時，將建立部分索引。部分索引是一個索引，它只包含資料表的一部分項目，通常是比索引的其餘部分更有用的索引部分。例如，如果您的資料表包含已開票和未開單的訂單，其中未開單的訂單佔據總資料表的一小部分，但這是一個經常使用的部分，您可以透過僅在該部分上建立索引來提高效能。另一個可能的應用是使用帶有 UNIQUE 的 WHERE 來強制資料表子集的唯一性。有關更多討論，請參閱第 11.8 節。

WHERE 子句中使用的表示式只能引用基礎資料表的欄位，但它可以使用所有欄位，而不僅僅是被索引的欄位。目前，WHERE 中也禁止使用子查詢和彙總資料表示式。相同的限制適用於作為表示式的索引欄位。

索引定義中使用的所有函數和運算符必須是「immutable」，也就是說，它們的結果必須僅依賴於它們的參數，而不是任何外部影響（例如另一個資料表的內容或目前時間）。此限制可確保明確定義索引的行為。要在索引表示式或 WHERE 子句中使用使用者定義的函數，請記住在建立函數時將該函數標記為 immutable。

參數

UNIQUE

在建立索引時（如果資料已存在）並且每次插入資料時，系統都會檢查資料表中的重複值。嘗試插入或更新如果導致重複項目的資料將產生錯誤。

CONCURRENTLY

使用此選項時，PostgreSQL 將在建立索引時，不會採取任何阻止資料表上同時的插入，更新或刪除的鎖定；而標準索引建立會鎖定資料表上的寫入（但不是讀取），直到完成為止。使用此選項時需要注意幾點 - 請參閱同步建立索引。

IF NOT EXISTS

如果已存在具有相同名稱的關連，請不要拋出錯誤，在這種情況下發出 NOTICE。請注意，無法保證現有索引與已建立的索引類似。指定 IF NOT EXISTS 時需要索引名稱。

INCLUDE

The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them.

It's wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches. Furthermore, B-tree deduplication is never used with indexes that have a non-key column.

Columns listed in the INCLUDE clause don't need appropriate operator classes; the clause can include columns whose data types don't have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, the B-tree and the GiST index access methods support this feature. In B-tree and the GiST indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

name

要建立的索引名稱。這裡不能包含綱要名稱；索引始終在與其父資料表相同的綱要中創建。如果省略該名稱，PostgreSQL會根據父資料表的名稱和索引的欄位名稱選擇合適的名稱。

ONLY

Indicates not to recurse creating indexes on partitions, if the table is partitioned. The default is to recurse.

table_name

要編制索引的資料表名稱（可以加上綱要名稱）。

method

要使用的索引方法的名稱。選項是 btree，hash，gist，spgist，gin 和 brin。預設方法是 btree。

column_name

資料表欄位的名稱。

expression

基於資料表的一個欄位或多個欄位的表示式。表示式通常必須與周圍的括號一起填寫，如語法中所示。但是，如果表示式具有函數呼叫的形式，則可以省略括號。

collation

用於索引的排序規則的名稱。預設情況下，索引使用為要索引的欄位宣告排序規則或要索引的表示式結果排序規則。具有非預設排序規則的索引對於涉及使用非預設排序規則的表示式查詢非常有用。

opclass

運算子類的名稱。請參閱下文了解詳情。

opclass_parameter

The name of an operator class parameter. See below for details.

ASC

指定遞增排序順序（預設值）。

DESC

指定遞減排序。

NULLS FIRST

指定 nulls 排在非 null 之前。這是指定 DESC 時的預設值。

NULLS LAST

指定 nulls 排在非 null 之後。這是未指定 DESC 時的預設值。

storage_parameter

特定於索引方法的儲存參數的名稱。有關詳情，請參見索引儲存參數。

tablespace_name

用於建立索引的資料表空間。如果未指定，則查詢 default_tablespace，或臨時資料表 temp_tablespaces。

predicate

部分索引的限制條件表示式。

索引儲存參數

選擇性的 WITH 子句指定索引的儲存參數。每個索引方法都有自己的一組允許的儲存參數。B-tree，hash，GiST 和 SP-GiST 索引方法都接受此參數：

fillfactor

索引的 fillfactor 一個百分比，用於確定索引方法嘗試填充索引頁面的程度。對於B-tree，在初始索引建構期間以及在右側擴展索引時（在插入新的最大索引值時），葉子頁面將填充到此百分比。 如果頁面隨後變得完整，它們將被拆分，導致索引效率逐漸下降。B-tree 使用預設的 fillfactor 為90，但可以選擇 10 到 100 之間的任何整數值。如果資料表是靜態的，那麼 fillfactor 100 能最小化索引的實體大小，但是對於大量更新的資料表，較小的 fillfactor 能最小化頁面拆分的需要。其他索引方法以不同但大致類似的方式使用 fillfactor；預設的 fillfactor 會因方法而異。

B-tree 索引也接受以下參數：

deduplicate_items (boolean)

控制第 63.4.2 節中所描述的 B-tree 重複資料刪除技術的使用。設定為 ON 或 OFF 以啟用或停用該機制。（如第 19.1 節所述，允許使用 ON 和 OFF 的其他寫法。）預設值為 ON。

vacuum_cleanup_index_scale_factor (floating point)

Per-index value for vacuum_cleanup_index_scale_factor.

GiST 索引另外接受此參數：

buffering

確定是否使用第 62.4.1 節中描述的緩衝建構技術來建構索引。使用 OFF 時，它被停用，ON 時啟用。當使用 AUTO 時，它最初被停用，但一旦索引大小達到 effective_cache_size，就會立即打開。預設值為 AUTO。

GIN 索引接受不同的參數：

fastupdate

此設定控制第 64.4.1 節中描述的快速更新技術的運用。它是一個布林參數：ON 啟用快速更新，OFF 停用它。 （如第 19.1 節所述，允許使用 ON 和 OFF 的替代拼寫。）預設為 ON。

gin_pending_list_limit

自定義 gin_pending_list_limit 參數。此值以 KB 為單位。

BRIN 索引接受不同的參數：

pages_per_range

定義構成 BRIN 索引每個項目的一個區塊範圍的資料表區塊數（有關更多詳細訊息，請參閱第 65.1 節）。預設值為 128。

autosummarize

定義每當在下一個頁面上檢測到插入時是否為前一頁面範圍進行摘要計算。

同步建立索引

建立索引可能會干擾資料庫的日常操作。通常，PostgreSQL 會鎖定要對寫入進行索引的資料表，並通過對資料的單次掃描來執行整個索引建構。其他事務仍然可以讀取資料表，但如果它們嘗試插入，更新或刪除資料表中的資料列，它們將被阻擋，直到索引建構完成。如果系統是線上正式資料庫，這可能會產生嚴重影響。非常大的資料表可能需要很長時間才能被編入索引，即使對於較小的資料表，索引建構也可能會鎖定寫入程序，這些時間對於線上正式系統來說是不可接受的。

PostgreSQL 支援建構索引而不會鎖定寫入。透過指定 CREATE INDEX 的 CONCURRENTLY 選項來呼叫此方法。使用此選項時，PostgreSQL 必須對資料執行兩次掃描，此外，它必須等待可能修改或使用索引的所有事務。因此，這種方法比標準索引建構需要更多的工作，也需要更長的時間來完成。但是，由於它允許在建構索引時繼續正常操作，因此此方法對於在正式環境中增加新的索引很有用。當然，索引建立帶來的額外 CPU 和 I/O 負載可能會減慢其他操作。

在同步索引建構時，索引實際上在一個交易事務中輸入到系統目錄，然後在另外兩個事務中産生兩個資料表掃描。在每次掃描資料表之前，索引建構必須等待已修改資料表的現有事務結束。在第二次掃描之後，索引建構必須等待具有快照（參閱第 13 章）的任何事務在第二次掃描之前結束。最後，索引可以標記為可以使用，然後 CREATE INDEX 指令完成。但是，即使這樣，索引也可能無法立即用於查詢：在最壞的情況下，只要在索引建構開始之前存在事務，都不能使用它。

如果在掃描資料表時出現問題，例如鎖死或唯一索引中的唯一性違規，則 CREATE INDEX 指令將會失敗但留下「無效」索引。出於查詢目的，該索引將被忽略，因為它可能不完整；但它仍然會消耗更新成本。psql \d 指令將回報此類索引為 INVALID：

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

在這種情況下，建議的恢復方法是刪除索引並再次嘗試同時執行 CREATE INDEX。（另一種可能性是使用 REINDEX 重建索引。但是，由於 REINDEX 不支持同步建構，因此該選項看起來不太有吸引力。）

同時建構唯一索引時的另一個警告是，當第二個資料表掃描開始時，已經對其他事務強制加上唯一性限制條件。這意味著在索引可供使用之前，甚至在索引建構最終失敗的情況下，可以在其他查詢中回報違反限制條件。此外，如果在第二次掃描中確實發生了故障，則「無效」索引將繼續強制執行其唯一性約束。

表示式索引和部分索引的同步建構也是支援的。在評估這些表示式時發生的錯誤可能導致類似於上面針對唯一性違規所描述的行為。

一般索引建立允許同一資料表上的其他一般索引建立同時執行，但一次只能在一個資料表上進行一個同步索引構立。在這兩種情況下，同時不允許在資料表上進行其他類型的結構變更。另一個區別是可以在事務塊中執行一般的 CREATE INDEX 指令，但 CREATE INDEX CONCURRENTLY 不能。

注意

有關何時可以使用索引，何時不使用索引以及哪些特定情況可以使用索引的訊息，請參閱第 11 章。

目前，只有B-tree，GiST，GIN 和 BRIN 索引方法支持多欄位索引。預設情況下最多可以指定 32 個欄位。（編譯 PostgreSQL 時可以變更此限制。）只有 B-tree 目前支援唯一索引。

可以為索引的每個欄位指定運算子類。運算子類識別該欄位的索引要使用的運算子。例如，4 bytes 整數的 B-tree 索引將使用 int4_ops 類；此運算子類包括 4 bytes 整數的比較函數。實際上，欄位資料型別的預設運算子類通常就足夠了。擁有運算子類的要點是，對於某些資料型別，可能存在多個有意義的排序。例如，我們可能希望按絕對值或複數資料型別的實部進行排序。我們可以透過為資料型別定義兩個運算子類，然後在建立索引時選擇適當的類。有關運算子類的更多訊息，請參閱第 11.10 節和第 37.16 節。

When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

對於支援有序掃描的索引方法（目前只有 B-tree），可以指定選擇性子句 ASC，DESC，NULLS FIRST 和 NULLS LAST 來修改索引的排序順序。由於可以向前或向後掃描有序索引，因此建立單欄位 DESC 索引並不常用 - 排序順序已經可用於一般性索引。這些選項的值是可以建立多欄位索引，該索引搭配混合排序查詢所請求的排序順序，例如 SELECT ... ORDER BY x ASC, y DESC。如果您需要在依賴於索引以避免排序步驟的查詢中支援「nulls sort low」行為而不是預設的「nulls sort high」，那麼 NULLS 選項很有用。

對於大多數索引方法，建立索引的速度取決於 maintenance_work_mem 的設定。較大的值將減少索引建立所需的時間，只要您不要使其大於真正可用的記憶體大小，否則將驅使主機使用 SWAP。

PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build. For index methods that support building indexes in parallel (currently, only B-tree), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any.

Parallel index builds may benefit from increasing maintenance_work_mem where an equivalent serial index build will see little or no benefit. Note that maintenance_work_mem may influence the number of worker processes requested, since parallel workers must have at least a 32MB share of the total maintenance_work_mem budget. There must also be a remaining 32MB share for the leader process. Increasing max_parallel_maintenance_workers may allow more workers to be used, which will reduce the time needed for index creation, so long as the index build is not already I/O bound. Of course, there should also be sufficient CPU capacity that would otherwise lie idle.

Setting a value for parallel_workers via ALTER TABLE directly controls how many parallel worker processes will be requested by a CREATE INDEX against the table. This bypasses the cost model completely, and prevents maintenance_work_mem from affecting how many parallel workers are requested. Setting parallel_workers to 0 via ALTER TABLE will disable parallel index builds on the table in all cases.

Tip

You might want to reset parallel_workers after setting it as part of tuning an index build. This avoids inadvertent changes to query plans, since parallel_workers affects all parallel table scans.

While CREATE INDEX with the CONCURRENTLY option supports parallel builds without special restrictions, only the first table scan is actually performed in parallel.

使用 DROP INDEX 移除索引。

PostgreSQL 的早期版本也有一個 R-tree 索引方法。此方法已被移除，因為它沒有 GiST 方法的顯著優勢。如果指定了 USING rtree，CREATE INDEX 會將其解釋為USING gist，以簡化舊資料庫到 GiST 的轉換。

範例

To create a unique B-tree index on the column title in the table films:

CREATE UNIQUE INDEX title_idx ON films (title);

To create a unique B-tree index on the column title with included columns director and rating in the table films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

To create a B-Tree index with deduplication disabled:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

To create an index on the expression lower(title), allowing efficient case-insensitive searches:

CREATE INDEX ON films ((lower(title)));

(In this example we have chosen to omit the index name, so the system will choose a name, typically films_lower_idx.)

To create an index with non-default collation:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

To create an index with non-default sort ordering of nulls:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

To create an index with non-default fill factor:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

To create a GIN index with fast updates disabled:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

To create an index on the column code in the table films and have the index reside in the tablespace indexspace:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

To create an index without locking out writes to the table:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

相容性

CREATE INDEX 是 PostgreSQL 延伸語法。SQL 標準中沒有索引的規定。

參閱

ALTER INDEX, DROP INDEX, REINDEX

CREATE CAST — define a new cast

Synopsis

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]

Description

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

SELECT CAST(42 AS float8);

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:

INSERT INTO foo (f1) VALUES (42);

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:

SELECT 2 + 4.0;

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

SELECT CAST ( 2 AS numeric ) + 4.0;

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

Sometimes it is necessary for usability or standards-compliance reasons to provide multiple implicit casts among a set of types, resulting in ambiguity that cannot be avoided as above. The parser has a fallback heuristic based on type categories and preferred types that can help to provide desired behavior in such cases. See CREATE TYPE for more information.

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

Use DROP CAST to remove user-defined casts.

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):

CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;

(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 FUNCTION, CREATE TYPE, DROP CAST

CREATE EVENT TRIGGER — define a new event trigger

Synopsis

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

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 Chapter 39. 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 Section 39.1 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.

Event triggers are disabled in single-user mode (see postgres). If an erroneous event trigger disables the database so much that you can't even drop the trigger, restart in single-user mode and you'll be able to do that.

Examples

Forbid the execution of any DDL command:

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
   EXECUTE FUNCTION abort_any_command();

Compatibility

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

See Also

ALTER EVENT TRIGGER, DROP EVENT TRIGGER, CREATE FUNCTION

CREATE LANGUAGE — 宣告一種新的程序語言

語法

CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
    HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ]

說明

CREATE LANGUAGE 使用 PostgreSQL 資料庫註冊新的程序語言。隨後即可使用這種新語言定義函數和觸發器程序。

注意 從 PostgreSQL 9.1 開始，大多數程序語言都被製作成「extension」，因此應該使用 CREATE EXTENSION 而不是 CREATE LANGUAGE 安裝。現在應該直接使用 CREATE LANGUAGE 來限制性的延伸套件安裝腳本。如果資料庫中存在有純粹的程序語言（可能是升級後的結果），則可以使用 CREATE EXTENSION langname FROM unpackaged 將其轉換為延伸套件。

CREATE LANGUAGE 有效地將語言名稱與負責執行用該語言撰寫的函數與語言處理函數相關聯。有關語言處理程序的更多訊息，請參閱第 55 章。

CREATE LANGUAGE 指令有兩種形式。在第一種形式中，使用者只提供所需語言的名稱，PostgreSQL 伺服器查詢 pg_pltemplatesystem 目錄以確定正確的參數。在第二種形式中，使用者提供語言參數以及語言名稱。第二種形式可用於建立未在 pg_pltemplate 中定義的語言，但此方法被視為是過時的。

當伺服器在 pg_pltemplate 目錄中找到指定的語言名稱項目時，即使該命令包含語言參數，它也將使用目錄的資訊。此行為簡化了舊的備份檔案載入，這些備份檔案可能包含有關語言支援功能但過時的訊息。

通常，使用者必須具有 PostgreSQL 超級使用者權限才能註冊新的語言。但是，如果語言在 pg_pltemplate 目錄中列出並且標記為允許由資料庫擁有者建立（tmpldbacreate 為 true），則資料庫的擁有者可以在該資料庫中註冊新的語言。預設情況下，資料庫擁有者可以建立受信任的語言，但超級使用者可以透過修改 pg_pltemplate 的內容來調整它。語言的建立者成為其擁有者，以後可以將其移除，重新命名或將其分配給新的擁有者。

CREATE OR REPLACE LANGUAGE 將註冊新的語言或更換現有的定義。如果該語言已存在，則其參數將根據指定的值或從 pg_pltemplate 取得，但語言的擁有權和權限設定不會更改，並且假定使用該語言撰寫的任何現有函數仍然有效。除了建立語言的普通權限要求之外，使用者還必須是現有語言的擁有者或超級使用者。REPLACE 主要用於確保語言存在。如果該語言具有 pg_pltemplate 項目，則 REPLACE 實際上不會變更現有定義的任何內容，除非在建立語言後修改了 pg_pltemplate 項目的特殊情況。

參數

TRUSTED

TRUSTED 表該語言不會授予使用者不應該擁有的資料存取權限。如果在註冊語言時省略了此關鍵字，則只有具有 PostgreSQL 超級使用者權限的使用者才能使用該語言建立新的函數。

PROCEDURAL

這是一個無功能的修飾詞。

name

新的程序語言名稱。此名稱在資料庫中的語言中必須是唯一的。

為了向下相容，名稱可以用單引號括起來。

HANDLER call_handler

call_handler 是先前註冊的函數名稱，將呼叫該函數來執行程序語言的函數。程序語言的呼叫處理程序必須用編譯語言撰寫，例如 C with version 1 convention，並在 PostgreSQL 中註冊為不帶參數的函數，回傳 language_handlertype，這是一種 placeholder 型別，僅用於將函數識別為呼叫處理程序

INLINE inline_handler

inline_handler 是先前註冊的函數名稱，該函數將被呼叫以執行此語言的匿名代碼區塊（DO 指令）。如果未指定 inline_handlerfunction，則該語言不支援匿名代碼區塊。處理函數必須使用一個型別為 internal 的參數，它將是 DO 指令的內部形式，並且通常回傳是 void。處理程序的回傳值將被忽略。

VALIDATOR valfunction

valfunction 是先前註冊的函數名稱，該函數將在宣告語言中的新函數時呼叫，以驗證新函數。如果未指定驗證程序功能，則在建立新函數時不會檢查該函數。驗證程序函數必須使用一個型別為 oid 的參數，該參數將是要建立的函數 OID，並且通常回傳為 void。

驗證程序函數通常會檢查函數的語法正確性，但它也可以查看函數的其他屬性。例如，如果語言無法處理某些參數型別。要發出錯誤信號，驗證程序函數應使用ereport() 函數。該函數的回傳值將被忽略。

如果伺服器在 pg_pltemplate 中具有指定語言名稱的項目，則忽略 TRUSTED 選項和支援函數名稱。

注意

使用 DROP LANGUAGE 移除程序語言。

系統目錄 pg_language（參閱第 51.29 節）記錄有關目前安裝的語言訊息。此外，psql 指令 \dL 可列出已安裝的語言。

要以程序語言建立函數，使用者必須具有該語言的 USAGE 權限。預設情況下，USAGE 被授予 PUBLIC（即每個人）在可信任的語言上。如果需要，可以撤銷此權限。

程序語言在各個資料庫之間是獨立。但是，可以在 template1 資料庫中安裝一種語言，這將使其在所有後續建立的資料庫中自動可用。

如果伺務器在 pg_pltemplate 中沒有該語言的項目，則必須已存在呼叫處理函數，內嵌處理函數（如果有）和驗證程序函數（如果有）。但是當有項目時，處理函數就不一定需要存在；如果資料庫中不存在，它們將會自動被定義。（如果實作該語言的共享函式庫在安裝環境中不可用，則可能導致 CREATE LANGUAGE 失敗。）

在 7.3 之前的 PostgreSQL 版本中，有必要將處理函數宣告為回傳 placeholder 型別 opaque，而不是 language_handler。為了支援載入舊的備份檔案，CREATE LANGUAGE 將接受宣告為回傳 opaque 的函數，但它會發出通知並將函數宣告的回傳型別變更為 language_handler。

範例

建立任何標準程序語言的最好方式是：

CREATE LANGUAGE plperl;

對於 pg_pltemplate 目錄中未知的語言，需要這樣的指令程序：

CREATE FUNCTION plsample_call_handler() RETURNS language_handler
    AS '$libdir/plsample'
    LANGUAGE C;
CREATE LANGUAGE plsample
    HANDLER plsample_call_handler;

相容性

CREATE LANGUAGE 是 PostgreSQL 的延伸功能。

參閱

ALTER LANGUAGE, CREATE FUNCTION, DROP LANGUAGE, GRANT, REVOKE

CREATE FUNCTION — 定義一個新函數

語法

CREATE [ OR REPLACE ] FUNCTION
  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_namecolumn_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

說明

CREATE FUNCTION 用來定義一個新函數。CREATE OR REPLACE FUNCTION 將建立一個新的函數，或是更換現有的函數定義。為了能夠定義一個函數，使用者必須具有該程式語言的 USAGE 權限。

如果包含 schema，則該函數將在指定的 schema 中建立。否則它會在目前的 schema中建立。新函數的名稱不得與相同 schema 中具有相同輸入參數型別的任何現有函數相同。但是，不同參數型別的函數可以共享一個名稱（稱為多載 overloading）。

要更換現有函數的目前定義，請使用 CREATE OR REPLACE FUNCTION。以這種方式改變函數的名稱或參數型別是不可行的（如果你嘗試做了，實際上你會建立一個新的、不同的函數）。另外，CREATE OR REPLACE FUNCTION 不會讓你改變現有函數的回傳型別。為此，你必須刪除並重新建立該函數。（使用 OUT 參數時，這意味著你不能更改任何 OUT 參數的型別，除非移除該函數。）

當使用 CREATE OR REPLACE FUNCTION 替換現有函數時，該函數的所有權和權限都不會改變。所有其他的函數屬性都被分配了指令中指定或隱含的值。你必須擁有取代它的功能（這包括成為自己角色群組的成員）。

如果你刪除然後重新建立一個函數，那麼新函數與舊的函數不是同一個實體；你必須刪除引用舊功能的現有規則、view、觸發器等。使用 CREATE OR REPLACE FUNCTION 來更改函數定義而不會破壞引用該函數的物件。此外，ALTER FUNCTION 可用於更改現有函數的大部分輔助屬性。

建立函數的使用者會成為該函數的所有者。

為了能夠建立一個函數，你必須對參數型別和回傳型別具有 USAGE 權限。

參數

name

要建立的函數名稱（可以加上 schema）。

argmode

參數的模式：IN、OUT、INOUT 或 VARIADIC。如果省略的話，則預設為 IN。只有 OUT 參數可以接著 VARIADIC 之後。此外，OUT 和 INOUT 參數不能與 RETURNS TABLE 表示法一起使用。

argname

參數的名稱。在某些語言（包括 SQL 和 PL/pgSQL）可讓你在函數中使用該名稱。對於其他語言而言，就函數本身而言，輸入參數的名稱只是額外的文件；但可以在呼叫函數時使用輸入參數名稱，以提高可讀性（請參閱第 4.3 節）。在任何情況下，輸出參數的名稱都很重要，因為它會在結果的欄位型別中定義了欄位名稱。 （如果你省略輸出參數的名稱，系統將自行選擇一個預設的欄位名稱。）

argtype

如果有的話，函數參數的資料型別（可加上 schema）。參數型別可以是基本型別、複合型別或 domain 型別，也可以引用資料表欄位的型別。

根據實作語言的不同，它也可能被指定為「偽型別」，例如 cstring。偽型別表示實際參數型別或者是不完整指定的，或者是在普通 SQL 資料型別集合之外的型別。

透過寫成 table_name.column_name %TYPE來引用欄位的型別。使用此功能有時可以幫助建立獨立於資料表定義的修改功能。

default_expr

如果未指定參數，則將用作預設值的表示式。該表示式必須是參數的參數型別的強制性。 只有輸入（包括 INOUT）參數可以有一個預設值。具有預設值的參數之後的所有輸入參數也必須具有預設值。

rettype

回傳的資料型別（可加上 schema）。回傳型別可以是基本型別、複合型別或 domain 型別，也可以引用資料表欄位的型別。根據實作語言的不同，它也可能被指定為「偽型別」，例如 cstring。如果該函數不應該回傳一個值，則應指定 void 作為回傳型別。

當有 OUT 或 INOUT 參數時，可以省略 RETURNS 子句。如果存在的話，它就必須與輸出參數所暗示的結果型別一致：如果存在多個輸出參數，則為 RECORD，或者與單個輸出參數的型別相同。

SETOF 修飾字表示該函數將回傳一組值，而不是單個值。

以寫作 table_name.column_name %TYPE 的形式來引用欄位的型別。

column_name

RETURNS TABLE 語法中輸出欄位的名稱。這實際上是另一種宣告 OUT 參數的方式，除了 RETURNS TABLE 也意味著 RETURNS SETOF。

column_type

RETURNS TABLE 語法中輸出欄位的資料型別。

lang_name

該函數實作的程式語言名稱。它可以是 sql、c、internal 或使用者定義的程式語言的名稱，例如，PLPGSQL。將這個名字用單引號括起來，並且需要完全符合大小寫。

TRANSFORM { FOR TYPEtype_name} [, ... ] }

對該函數如何套用型別轉換的呼叫列表。在 SQL 型別和特定於語言的資料型別之間進行轉換；請參閱 CREATE TRANSFORM。程序語言實作通常具有內建型別的編碼知識，這些不需要在這裡列出。只是如果程序語言實作不知道如何處理這些資料型別並且沒有提供轉換方式，它將回退到轉換資料型別的預設行為，但這仍然取決於實作的情況而定。

WINDOW

WINDOW 表示該函數是一個窗函數，而不是一個普通函數。目前這僅對用 C 寫成的函數有用。在替換現有函數定義時，不能更改 WINDOW 屬性。

IMMUTABLE

STABLE

VOLATILE

這些屬性告知查詢優化器關於函數的行為。至多只能指定一個選項。如果沒有這些選項出現，VOLATILE 是基本的假設。

IMMUTABLE 表示該函數不能修改資料庫，並且在給定相同的參數值時總是回傳相同的結果；也就是說，它不會執行資料庫查詢或以其他方式使用不直接存在於其參數列表中的訊息。如果給出這個選項，任何具有所有常量參數的函數呼叫都可以立即替換為函數值。

STABLE 表示該函數無法修改資料庫，並且在單個資料表掃描時，它將始終為相同的參數值回傳相同的結果，但其結果可能會跨 SQL 語句更改。對於結果取決於資料庫查詢，參數變數（如目前時區）等的函數，這是合適的選擇（對於希望查詢由目前命令修改資料列的 AFTER 觸發器並不合適）。另請注意，current_timestamp 類的函數符合穩定性，因為它們的值在事務中不會改變。

VOLATILE 表示即使在單個資料表掃描中函數值也會改變，因此不能進行優化。 在這個意義上，相對較少的資料庫功能是不穩定的，有一些例子是random ()、currval()、timeofday()。 但請注意，任何具有副作用的函數都必須分類為 VOLATILE，即使其結果具有相當的可預測性，以防止結果被優化掉，這樣例子是setval()。

更多詳細訊息請參閱第 37.6 節。

LEAKPROOF

LEAKPROOF 表示該函數不會有副作用。除了其回傳值之外，它沒有揭示任何關於它的參數訊息。例如，某些參數值引發了錯誤訊息但不引發其他錯誤訊息的函數，或者在任何錯誤訊息中包含參數值的函數都是不洩漏的。這會影響系統如何對使用這些 security_barrier 選項建立的 view 或啟用資料列級別安全性的資料表執行查詢。在查詢本身包含非防漏功能的使用者提供的任何條件之前，系統將執行安全策略和安全屏障 view 的條件，以防止資料意外暴露。標記為防漏的功能和操作子被認為是可信的，並且可以在來自安全原則和安全障礙視圖的條件之前執行。另外，沒有參數或者沒有從安全屏障 view 或資料表中傳遞任何參數的函數在安全條件之前不必被標記為不可洩漏。請參閱 CREATE VIEW 和第 40.5 節。此選項只能由超級使用者設定。

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT（預設值）表示當其某些參數為 null 時，該函數仍將被正常呼叫。那麼函數作者有責任在必要時檢查 null，並作出適當的處理。

RETURNS NULL ON NULL INPUT 或 STRICT 表示函數每當其任何參數為 null 時就回傳 null。如果指定了該參數，那麼當有 null 參數時，該函數就不會被執行；也就是，會自動假定結果為 null。

[EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER

SECURITY INVOKER 表示該函數將以呼叫它的使用者權限執行。這是預設的設定。 SECURITY DEFINER 指定該功能將以擁有它的使用者權限執行。

關鍵字 EXTERNAL 允許 SQL 一致性，但它是選擇性的。與 SQL 標準不同的是，此功能適用於所有函數。

PARALLEL

PARALLEL UNSAFE 表示該函數不能在平行模式下執行，並且在 SQL 語句中存在此類函數會強制執行串列的執行計劃。這是預設的設定。PARALLEL RESTRICTED 表示該功能可以以平行模式執行，但執行僅限於平行群組領導。PARALLEL SAFE 表示該功能可以安全無限制地在平行模式下執行。

如果函數修改任何資料庫狀態，或者如果他們對交易事務進行了更新（如使用子事務，或者他們存取序列資料或試圖對設定進行永久性更改（例如 setval）），那麼函數就應該標記為 PARALLEL UNSAFE。如果它們存取臨時資料表、客戶端連線狀態、游標、prepared statement 或系統無法以平行模式同步的繁雜的後端狀態，它們應該被標記為 PARALLEL RESTRICTED（例如，設定種子不能由初始者執行，另一個流程所做的更改不會反映在初始者身上）。一般來說，如果一個函數在 RESTRICTED 或 UNSAFE 時被標記為 SAFE，或者當它實際上是 UNSAFE 的時候被標記為 RESTRICTED，那麼它在使用平行查詢時可能會引發錯誤或產生錯誤的結果。如果錯誤標記，C 語言函數在理論上可能表現出完全未定義的行為，因為系統無法保護自己免受任意 C 程式的影響，但在大多數情況下，結果不會比其他函數更差。只要有疑問，函數就應該標記為UNSAFE，這是預設值。

execution_cost

一個正數，以 cpu 執行成本為單位給予該函數的估計執行成本。如果函數回傳一個集合，則這是每個回傳資料列的成本。如果未指定成本，則假定 C 語言和內部函數為 1 個單元，其他語言為 100 個單元。較大的值會導致規劃單元嘗試避免比必要時更頻繁地評估該函數。

result_rows

一個正數，它給予規劃單元應該期望函數回傳的估計資料列數。只有在函數宣告回傳一個集合時才允許這樣做。預設是 1000 個資料列。

configuration_parameter

value

SET 子句在輸入函數時將指定的配置參數設定為指定的值，然後在函數退出時恢復為其先前的值。 SET FROM CURRENT 將執行 CREATE FUNCTION 時當時參數的值保存為輸入函數時要應用的值。

如果將一個 SET 子句附加到一個函數，那麼在該函數內對同一個變數執行的 SET LOCAL 命令的作用將僅限於該函數：配置參數的先前的值仍然會在函數離開時恢復。 然而，一個普通的 SET 命令（沒有 LOCAL）會覆蓋 SET 子句，就像它對於先前的 SET LOCAL 指令所做的那樣：除非當下的事務被回復，否則這種指令的效果將在函數退出後持續存在。

有關允許的參數名稱和值的更多訊息，請參閱 SET 和第 19 章。

definition

定義函數的字串常數；其意義取決於程式語言。它可以是內部函數名稱、目標檔案的路徑、SQL 指令或程序語言中的內容。

使用錢字號括弧（請參閱第 4.1.2.4 節）撰寫函數定義內容，而不是普通的單引號語法的話，通常很有幫助。如果沒有錢字號括弧，函數定義中的任何單引號或反斜線都必須通過加倍來避免編譯錯誤。

obj_file,link_symbol

當 C 語言原始碼中的函數名稱與 SQL 函數的名稱不同時，AS 子句的這種形式用於可動態載入的 C 語言函數。字串 obj_file 是包含已編譯 C 函數的共享函式庫檔案的名稱，會被解釋為 LOAD 指令。字串 link_symbol 是函數的連結，即 C 語言原始碼中函數的名稱。如果省略連結，則假設它與定義的 SQL 函數的名稱相同。

當重複 CREATE FUNCTION 呼叫引用同一個目標檔案時，該檔案僅會在每個連線中載入一次。要卸載並重新載入文件（可能在開發過程中），請重新啟動一個新的連線。

attribute

指定有關該功能的可選訊息的歷史方法。有以下屬性可以在這裡顯示：

isStrict

等同於 STRICT 或 RETURNS NULL ON NULL INPUT。

isCachable

isCachable 等同於 IMMUTABLE，但過時了；但它仍然被接受使用，因為相容性的理由。

屬性名稱都不區分大小寫。

有關撰寫函數的更多訊息，請參閱第 37.3 節。

函數多載（Overloading）

PostgreSQL 允許函數多載；也就是說，只要具有不同的輸入參數類型，相同的名稱可以用於多個不同的函數。但是，所有 C 的函數名稱必須不同，因此你必須為 C 函數重載不同C 的名稱（例如，使用參數型別作為 C 名稱的一部分）。

如果兩個函數具有相同的名稱和輸入參數型別，則忽略任何 OUT 參數將被視為相同。 因此，像這些聲明就會有衝突：

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

具有不同參數型別列表的函數在建立時不會被視為衝突，但如果提供了預設值，則它們可能會在使用中發生衝突。 例如下面的例子：

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

呼叫 foo(10) 的話會因為不知道應該呼叫哪個函數而失敗。

注意

以完整的 SQL 型別語法來宣告函數的參數和回傳值是可以。 但是，帶括號的型別修飾字（例如數字型別的精確度修飾字）將被 CREATE FUNCTION 丟棄。 因此，例如 CREATE FUNCTION foo（varchar（10））...與 CREATE FUNCTION foo（varchar）....完全相同。

使用 CREATE OR REPLACE FUNCTION 替換現有函數時，對於更改參數名稱是有限制的。你不能更改已分配給任何輸入參數的名稱（儘管你可以將名稱增加先前沒有的參數）。如果有多個輸出參數，則不能更改輸出參數的名稱，因為這會更改描述函數結果的匿名組合類型的欄位名稱。 這些限制是為了確保函數的現有的呼叫在更換時不會停止工作。

如果使用 VARIADIC 參數將函數聲明為 STRICT，則嚴格性檢查會測試整個動態陣列是否為 non-null。如果陣列有 null，該函數仍然可以被呼叫。

範例

這裡有一些簡單的例子可以幫助你開始。有關更多訊息和範例，請參閱第 37.3 節。

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

將一個整數遞增，在 PL/pgSQL 中使用參數名稱：

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

回傳包含多個輸出參數的結果：

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

你可以使用明確命名的複合型別更加詳細地完成同樣的事情：

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

回傳多個欄位的另一種方法是使用 TABLE 函數：

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

但是，TABLE 函數與前面的例子不同，因為它實際上回傳一堆記錄，而不僅僅是一條記錄。

安全地撰寫 SECURITY DEFINER 函數

由於SECURITY DEFINER函數是以擁有它的用戶的權限執行的，因此需要注意確保該函數不會被濫用。為了安全起見，應設定 search_path 以排除任何不受信任的使用者可以寫入的 schema。這可以防止惡意使用者建立掩蓋物件的物件（例如資料表、函數和運算元），使得該物件被函數使用。在這方面特別重要的是臨時資料表的 schema，它預設是首先被搜尋的，並且通常允許由任何人寫入。透過強制最後才搜尋臨時 schema 可以得到較為安全的處理。 為此，請將 pg_temp 作為 search_path 中的最後一個項目。此函數說明安全的使用情況：

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

這個函數的意圖是存取一個資料表 admin.pwds。但是，如果沒有 SET 子句，或者只提及 admin 的 SET 子句，則可以透過建立名為 pwds 的臨時資料表來破壞該函數。

在 PostgreSQL 8.3 之前，SET 子句還不能使用，所以舊的函數可能需要相當複雜的邏輯來儲存、設定和恢復 search_path。有了 SET 子句便更容易用於此目的。

還有一點需要注意的是，預設情況下，對於新建立的函數，將會把權限授予 PUBLIC（請參閱 GRANT 以獲取更多訊息）。通常情況下，你只希望將安全定義函數的使用僅限於某些使用者。為此，你必須撤銷預設的 PUBLIC 權限，然後選擇性地授予執行權限。為了避免出現一個破口，使得所有人都可以訪問新功能，可以在一個交易事務中建立它並設定權限。例如：

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

相容性

SQL:1999 及其更新的版本中定義了一個 CREATE FUNCTION 指令。與 PostgreSQL 版本的指令類似但不完全相容。這些屬性並不是可移植的，不同的程序語言之間也無法移植。

為了與其他資料庫系統相容，可以在 argname 之前或之後編寫 argmode。但只有第一種方法符合標準。

對於參數預設值，SQL標準僅使用 DEFAULT 關鍵字指定語法。帶有 = 的語法在 T-SQL 和 Firebird 中使用。

延伸閱讀

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE

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 MATERIALIZED VIEW — 定義一個新的具體化檢視表

語法

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

說明

CREATE MATERIALIZED VIEW 定義查詢的具體化檢視表。執行該查詢並在命令完成後將資料儲存於檢視表中（除非使用 WITH NO DATA），並可在稍後使用 REFRESH MATERIALIZED VIEW 更新。

CREATE MATERIALIZED VIEW 與 CREATE TABLE AS 類似，只是它還記得用於初始化檢視表的查詢，以便稍後可以根據需要更新它。具體化檢視表具有許多與資料表相同的屬性，但不支援臨時具體化檢視表或自動產生 OID。

參數

IF NOT EXISTS

如果已經存在具有相同名稱的具體化檢視表的話，請不要拋出錯誤。在這種情況下會發布通知。請注意，這不能保證現有的具體化檢視表與想要建立的檢視表相似。

table_name

要建立的具體化檢視表名稱（可選擇性加上所屬綱要）。

column_name

新的具體化檢視表中欄位的名稱。如果未提供欄位名稱，則從查詢的輸出的欄位名稱中取得它們。

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

此子句為新的具體化檢視表指定選擇性的儲存參數；請參閱儲存參數選項了解更多訊息。CREATE TABLE 支援的所有參數也都支援 CREATE MATERIALIZED VIEW，只有 OIDS 除外。有關更多訊息，請參閱 CREATE TABLE。

TABLESPACE tablespace_name

tablespace_name 用於要在其中建立新的具體化檢視表的資料表空間名稱。如果未指定，則會使用 default_tablespace 設定。

query

一個 SELECT、TABLE 或 VALUES 指令。該查詢將在安全保障的操作環境中執行；特別是對自己建立的臨時資料表函數的呼叫將會失敗。

WITH [ NO ] DATA

此子句指定是否需要在建立時把資料填入具體化檢視表。如果沒有的話，則具體化檢視表將被標記為不可進行資料掃描，並且在使用 REFRESH MATERIALIZED VIEW 之前都無法查詢。

相容性

CREATE MATERIALIZED VIEW is a PostgreSQL extension.

參閱

ALTER MATERIALIZED VIEW, CREATE TABLE AS, CREATE VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

CREATE PUBLICATION — define a new publication

Synopsis

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

Description

CREATE PUBLICATION adds a new publication into the current database. The publication name must be distinct from the name of any existing publication in the current database.

A publication is essentially a group of tables whose data changes are intended to be replicated through logical replication. See Section 30.1 for details about how publications fit into the logical replication setup.

Parameters

name

The name of the new publication.

FOR TABLE

Specifies a list of tables to add to the publication. If ONLY is specified before the table name, only that table is added to the publication. If ONLY is not specified, the table and all its descendant tables (if any) are added. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. This does not apply to a partitioned table, however. The partitions of a partitioned table are always implicitly considered part of the publication, so they are never explicitly added to the publication.

Only persistent base tables and partitioned tables can be part of a publication. Temporary tables, unlogged tables, foreign tables, materialized views, and regular views cannot be part of a publication.

When a partitioned table is added to a publication, all of its existing and future partitions are implicitly considered to be part of the publication. So, even operations that are performed directly on a partition are also published via publications that its ancestors are part of.

FOR ALL TABLES

Marks the publication as one that replicates changes for all tables in the database, including tables created in the future.

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

This clause specifies optional parameters for a publication. The following parameters are supported:

publish (string)

This parameter determines which DML operations will be published by the new publication to the subscribers. The value is comma-separated list of operations. The allowed operations are insert, update, delete, and truncate. The default is to publish all actions, and so the default value for this option is 'insert, update, delete, truncate'.

publish_via_partition_root (boolean)

This parameter determines whether changes in a partitioned table (or on its partitions) contained in the publication will be published using the identity and schema of the partitioned table rather than that of the individual partitions that are actually changed; the latter is the default. Enabling this allows the changes to be replicated into a non-partitioned table or a partitioned table consisting of a different set of partitions.

If this is enabled, TRUNCATE operations performed directly on partitions are not replicated.

Notes

If neither FOR TABLE nor FOR ALL TABLES is specified, then the publication starts out with an empty set of tables. That is useful if tables are to be added later.

The creation of a publication does not start replication. It only defines a grouping and filtering logic for future subscribers.

To create a publication, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)

To add a table to a publication, the invoking user must have ownership rights on the table. The FOR ALL TABLES clause requires the invoking user to be a superuser.

The tables added to a publication that publishes UPDATE and/or DELETE operations must have REPLICA IDENTITY defined. Otherwise those operations will be disallowed on those tables.

For an INSERT ... ON CONFLICT command, the publication will publish the operation that actually results from the command. So depending of the outcome, it may be published as either INSERT or UPDATE, or it may not be published at all.

COPY ... FROM commands are published as INSERT operations.

DDL operations are not published.

Examples

Create a publication that publishes all changes in two tables:

CREATE PUBLICATION mypublication FOR TABLE users, departments;

Create a publication that publishes all changes in all tables:

CREATE PUBLICATION alltables FOR ALL TABLES;

Create a publication that only publishes INSERT operations in one table:

CREATE PUBLICATION insert_only FOR TABLE mydata
    WITH (publish = 'insert');

Compatibility

CREATE PUBLICATION is a PostgreSQL extension.

See Also

ALTER PUBLICATION, DROP PUBLICATION

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 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.

The type of a column is referenced by writing table_name.column_name%TYPE. Using this feature can sometimes help make a procedure independent of changes to the definition of a table.

default_expr

An expression to be used as default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. All input parameters following a parameter with a default value must have default values as well.

lang_name

The name of the language that the procedure is implemented in. It can be sql, c, internal, or the name of a user-defined procedural language, e.g. plpgsql. Enclosing the name in single quotes is deprecated and requires matching case.

TRANSFORM { FOR TYPE type_name } [, ... ] }

Lists which transforms a call to the procedure should apply. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM. Procedural language implementations usually have hardcoded knowledge of the built-in types, so those don't need to be listed here. If a procedural language implementation does not know how to handle a type and no transform is supplied, it will fall back to a default behavior for converting data types, but this depends on the implementation.

[EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indicates that the procedure is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it.

The key word EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all procedures not only external ones.

A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

configuration_parameter value

The SET clause causes the specified configuration parameter to be set to the specified value when the procedure is entered, and then restored to its prior value when the procedure exits. SET FROM CURRENT saves the value of the parameter that is current when CREATE PROCEDURE is executed as the value to be applied when the procedure is entered.

If a SET clause is attached to a procedure, then the effects of a SET LOCAL command executed inside the procedure for the same variable are restricted to the procedure: the configuration parameter's prior value is still restored at procedure exit. However, an ordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command: the effects of such a command will persist after procedure exit, unless the current transaction is rolled back.

If a SET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

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

definition

A string constant defining the procedure; the meaning depends on the language. It can be an internal procedure name, the path to an object file, an SQL command, or text in a procedural language.

It is often helpful to use dollar quoting (see Section 4.1.2.4) to write the procedure definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the procedure definition must be escaped by doubling them.

obj_file, link_symbol

This form of the AS clause is used for dynamically loadable C language procedures when the procedure name in the C language source code is not the same as the name of the SQL procedure. The string obj_file is the name of the shared library file containing the compiled C procedure, and is interpreted as for the LOAD command. The string link_symbol is the procedure's link symbol, that is, the name of the procedure in the C language source code. If the link symbol is omitted, it is assumed to be the same as the name of the SQL procedure being defined.

When repeated CREATE PROCEDURE calls refer to the same object file, the file is only loaded once per session. To unload and reload the file (perhaps during development), start a new session.

Notes

See CREATE FUNCTION for more details on function creation that also apply to procedures.

Use CALL to execute a procedure.

Examples

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

Compatibility

A CREATE PROCEDURE command is defined in the SQL standard. The PostgreSQL version is similar but not fully compatible. For details see also CREATE FUNCTION.

See Also

ALTER PROCEDURE, DROP PROCEDURE, CALL, CREATE FUNCTION

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

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

CREATE FOREIGN TABLE — define a new foreign table

Synopsis

CREATE FOREIGN TABLE [ IF NOT EXISTS ] 
table_name
 ( [
  { 
column_name
data_type
 [ OPTIONS ( 
option
 '
value
' [, ... ] ) ] [ COLLATE 
collation
 ] [ 
column_constraint
 [ ... ] ]
    | 
table_constraint
 }
    [, ... ]
] )
[ INHERITS ( 
parent_table
 [, ... ] ) ]
  SERVER 
server_name

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

CREATE FOREIGN TABLE [ IF NOT EXISTS ] 
table_name

  PARTITION OF 
parent_table
 [ (
  { 
column_name
 [ WITH OPTIONS ] [ 
column_constraint
 [ ... ] ]
    | 
table_constraint
 }
    [, ... ]
) ] 
partition_bound_spec

  SERVER 
server_name

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


where 
column_constraint
 is:


[ CONSTRAINT 
constraint_name
 ]
{ NOT NULL |
  NULL |
  CHECK ( 
expression
 ) [ NO INHERIT ] |
  DEFAULT 
default_expr
 }


and 
table_constraint
 is:


[ CONSTRAINT 
constraint_name
 ]
CHECK ( 
expression
 ) [ NO INHERIT ]

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

The data type of the column. This can include array specifiers. For more information on the data types supported byPostgreSQL, refer toChapter 8.

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

[, ... ] )

The optionalINHERITSclause specifies a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be plain tables or foreign tables. See the similar form ofCREATE TABLEfor more details.

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

The name of an existing foreign server to use for the foreign table. For details on defining a server, seeCREATE SERVER.

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 TABLE films (
    code        char(5) NOT NULL,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
)
SERVER film_server;

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

CREATE FOREIGN TABLE measurement_y2016m07
    PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
    SERVER server_07;

Compatibility

TheCREATE FOREIGN TABLEcommand largely conforms to theSQLstandard; however, much as withCREATE TABLE,NULLconstraints and zero-column foreign tables are permitted. The ability to specify column default values is also aPostgreSQLextension. Table inheritance, in the form defined byPostgreSQL, is nonstandard.

See Also

ALTER FOREIGN TABLE

,

DROP FOREIGN TABLE