1 of 100

VI. 參考資訊

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

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

I. SQL 指令

本部分包含 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 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

ALTER DATABASE

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.

ALTER DEFAULT PRIVILEGES

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 EXTENSION

ALTER EXTENSION — 變更延伸功能的宣告內容

語法

ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object

where member_object is:

  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  CONVERSION object_name |
  DOMAIN object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  MATERIALIZED VIEW object_name |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  TABLE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TRANSFORM FOR type_name LANGUAGE lang_name |
  TYPE object_name |
  VIEW object_name

and aggregate_signature is:

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

說明

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

UPDATE

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

SET SCHEMA

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

ADD member_object

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

DROP member_object

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

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

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

Parameters

name

The name of an installed extension.

new_version

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

new_schema

The new schema for the extension.

object_name aggregate_name function_name operator_name procedure_name routine_name

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

source_type

The name of the source data type of the cast.

target_type

The name of the target data type of the cast.

argmode

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

argname

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

argtype

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

left_type right_type

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

PROCEDURAL

This is a noise word.

type_name

The name of the data type of the transform.

lang_name

The name of the language of the transform.

範例

要將 hstore 延伸功能更新到版本 2.0：

ALTER EXTENSION hstore UPDATE TO '2.0';

要將 hstore 延伸功能的綱要變更為 utils：

ALTER EXTENSION hstore SET SCHEMA utils;

要將現有函數新增到 hstore 延伸功能之中：

ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);

相容性

ALTER EXTENSION 是 PostgreSQL 的延伸功能。

參閱

CREATE EXTENSION, DROP EXTENSION

ALTER FUNCTION

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

語法

ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    action [ ... ] [ RESTRICT ]
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    RENAME TO new_name
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    SET SCHEMA new_schema
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
    DEPENDS ON EXTENSION extension_name

where action is one of:

    CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    PARALLEL { UNSAFE | RESTRICTED | SAFE }
    COST execution_cost
    ROWS result_rows
    SET configuration_parameter { TO | = } { value | DEFAULT }
    SET configuration_parameter FROM CURRENT
    RESET configuration_parameter
    RESET ALL

說明

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

您必須是函數擁有者才能使用 ALTER FUNCTION 功能。要變更函數的綱要，您還必須具有新綱要的 CREATE 權限。要變更擁有者，您還必須是新擁有角色的直接或間接成員，並且該角色必須對該函數的綱要具有 CREATE 權限。（這些限制強制改變擁有者不會做任何透過刪除和重新建立函數都無法做到的事情，但是超級用戶可以改變任何函數的所有權。）

參數

name

現有函數的名稱（可以加上綱要）。如果未指定參數列表，則該名稱在其綱要中必須是唯一的。

argmode

參數的模式：IN，OUT，INOUT 或 VARIADIC。如果省略，則預設為 IN。請注意，ALTER FUNCTION 實際上並不關心 OUT 參數，因為只需要輸入參數來確定函數的身份。所以列出 IN，INOUT 和 VARIADIC 參數就足夠了。

argname

參數的名稱。請注意，ALTER FUNCTION 實際上並不關心參數名稱，因為只需要參數資料型別來確定函數的身份。

argtype

如果有的話，函數參數的資料型別（可選用綱要修飾）。

new_name

函數的新名稱。

new_owner

函數的新擁有者。請注意，如果該函數被標記為 SECURITY DEFINER，隨後它將以新的擁有者執行。

new_schema

函數的新綱要。

extension_name

函數相依的延伸套件。

CALLED ON NULL INPUT RETURNS NULL ON NULL INPUT STRICT

CALLED ON NULL INPUT 變更此函數，以便在其某些或全部參數為空時呼叫此函數。回傳 NULL ON NULL INPUT 或 STRICT 變更該函數，以便在其任何參數為 null 時不呼叫此函數；相反地，則會自動假設空的結果。有關更多訊息，請參閱 CREATE FUNCTION。

IMMUTABLE STABLE VOLATILE

將函數的易變性變更為指定的設定。詳情請參閱 CREATE FUNCTION。

[ EXTERNAL ] SECURITY INVOKER [ EXTERNAL ] SECURITY DEFINER

變更該函數是否是安全性定義者。考慮到 SQL 標準的一致性，關鍵字 EXTERNAL 會被忽略。有關此功能的更多訊息，請參閱 CREATE FUNCTION。

PARALLEL

改變函數是否被認為是可以安全地平行運算。詳情請參閱 CREATE FUNCTION。

LEAKPROOF

變更函數是否被認為是 leakproof。有關此功能的更多訊息，請參閱 CREATE FUNCTION。

COST execution_cost

變更函數估計的執行成本。有關更多訊息，請參閱 CREATE FUNCTION。

ROWS result_rows

變更由 set-returning 函數回傳的估計資料列數。有關更多訊息，請參閱 CREATE FUNCTION。

configuration_parameter value

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

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

RESTRICT

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

範例

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

ALTER FUNCTION sqrt(integer) RENAME TO square_root;

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

ALTER FUNCTION sqrt(integer) OWNER TO joe;

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

ALTER FUNCTION sqrt(integer) SET SCHEMA maths;

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

ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;

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

ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;

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

ALTER FUNCTION check_password(text) RESET search_path;

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

相容性

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

參閱

CREATE FUNCTION, DROP FUNCTION

ALTER INDEX

ALTER INDEX — change the definition of an index

Synopsis

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

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:

To move an index to a different tablespace:

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

Compatibility

ALTER INDEX is a PostgreSQL extension.

ALTER LANGUAGE

版本：11

ALTER LANGUAGE — 變更程序語言的宣告

語法

說明

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

參數

name

語言名稱

new_name

語言的新名稱

new_owner

語言的新所有者e

相容性

SQL 標準中沒有 ALTER LANGUAGE 語句。

參閱

ALTER MATERIALIZED VIEW

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.

ALTER POLICY

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 所延伸支援的指令。

ALTER PUBLICATION

ALTER PUBLICATION — change the definition of a publication

Synopsis

ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name

Description

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

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

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

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

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

Parameters

name

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

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

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

The user name of the new owner of the publication.new_name

The new name for the publication.

Examples

Change the publication to publish only deletes and updates:

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

Add some tables to the publication:

ALTER PUBLICATION mypublication ADD TABLE users, departments;

Compatibility

ALTER PUBLICATION is a PostgreSQL extension.

ALTER ROLE

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

版本：11

ALTER RULE — 變更規則的宣告

語法

說明

ALTER RULE 變更現有規則的屬性。目前，唯一可用的操作是變更規則的名稱。

要使用 ALTER RULE，您必須擁有該規則適用的資料表或檢視表。

參數

name

要變更的現有規則名稱。

table_name

規則適用的資料表或檢視表名稱（可加入綱要限定）。

new_name

規則的新名稱。

範例

要重新命名現有規則：

相容性

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

參閱

ALTER SCHEMA

ALTER SCHEMA — change the definition of a schema

Synopsis

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

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.

ALTER SEQUENCE

ALTER SEQUENCE — change the definition of a sequence generator

語法

說明

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

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

參數

name

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

IF EXISTS

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

data_type

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

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

increment

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

minvalue NO MINVALUE

可選擇性子句 MINVALUE minvalue 決定序列可以産生的最小值。如果指定 NO MINVALUE，則將分別使用預設值 1 和遞增和遞減資料型別的最小值。如果未指定任何選項，則將保持目前的最小值。

maxvalue NO MAXVALUE

可選擇性子句 MAXVALUE maxvalue 決定序列的最大值。如果指定 NO MAXVALUE，則將分別使用資料型別最大值的預設值，以及遞增和遞減序列的預設值 -1。如果未指定任何選項，則將保持目前的最大值。

start

可選擇性子句 START WITH start 變更序列記錄的起始值。這對目前序列值沒有影響；它只是設定未來 ALTER SEQUENCE RESTART 指令將使用的值。

restart

可選擇性子句 RESTART [WITH restart] 變更序列的目前值。這與使用 is_called = false 呼叫 setval 函數類似：下一次呼叫 nextval 將回傳指定的值。寫入沒有重啟值的 RESTART 相當於提供由 CREATE SEQUENCE 記錄的起始值或 ALTER SEQUENCE START WITH 最後設定的起始值。

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

cache

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

CYCLE

可選擇性的 CYCLE 關鍵字可用於使序列在分別透過遞增或遞減達到 maxvalue 或 minvalue 時循環繞回。如果達到限制，則産生的下一個數字將分別為 minvalue 或 maxvalue。

NO CYCLE

如果指定了可選擇性的 NO CYCLE 關鍵字，則在序列達到其最大值後對 nextval 的任何呼叫都將回傳錯誤。如果未指定 CYCLE 或 NO CYCLE，則將保持舊的循環行為。

OWNED BY table_name.column_name OWNED BY NONE

OWNED BY 選項使序列與特定的資料表欄位相關連，這樣如果移除該欄位（或其整個資料表），序列也將自動移除。如果指定了，則此關連將替換先前為序列指定的任何關連。指定的資料表必須具有相同的擁有者，並且與序列位於相同的綱要中。指定 OWNED BY NONE 將移除任何現有關連，使序列「獨立」。

new_owner

序列新擁有者的使用者名稱。

new_name

序列的新名稱。

new_schema

序列的新綱要。

注意

ALTER SEQUENCE 不會立即影響具有預先分配（快取）序列值的後端（除了目前後端）的 nextval 結果。在注意到變更的序列産生參數之前，它們將使用所有快取的值。目前的後端將立即受到影響。

ALTER SEQUENCE 不會影響序列的 currval 狀態。（在 PostgreSQL 8.3 之前，有時會影響到。）

ALTER SEQUENCE 阻止同時間的 nextval，currval，lastval 和 setval 呼叫。

由於歷史原因，ALTER TABLE 也可以用於序列；但是序列允許的 ALTER TABLE 的語法就只有上面列出的形式。

範例

將序列 serial 從 105 重新啟動：

相容性

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

參閱

ALTER STATISTICS

版本：11

ALTER STATISTICS — change the definition of an extended statistics object

Synopsis

Description

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

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

Parameters

name

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

new_owner

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

new_name

The new name for the statistics object.

new_schema

The new schema for the statistics object.

Compatibility

There is no ALTER STATISTICS command in the SQL standard.

ALTER SUBSCRIPTION

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 SYSTEM

ALTER SYSTEM — 變更伺服器組態設定

語法

說明

ALTER SYSTEM 用於變更整個資料庫叢集的伺服器組態參數。它比手動編輯 postgresql.conf 檔案的傳統方法更為方便。ALTER SYSTEM 將設定的參數設定寫入到 postgresql.auto.conf 檔案中，該檔案是在 postgresql.conf 之外讀取的。將參數設定為 DEFAULT 或使用 RESET 變體，將從 postgresql.auto.conf 檔案中刪除該設定項目。使用 RESET ALL 刪除所有此類設定項目。

在下一次伺服器組態重新載入之後，或者對於只能在伺服器啟動時變更的參數，在下一次伺服器重新啟動之後，使用 ALTER SYSTEM 設定的值將會生效。可以透過呼叫 SQL 函數 pg_reload_conf()，執行 pg_ctl reload 或向主伺服器程序發送 SIGHUP 信號來命令伺服器組態重新載入。

只有超級使用者才能使用 ALTER SYSTEM。另外，由於此命令直接作用於檔案系統且無法回溯，因此不允許在交易區塊或函數內部使用此指令。

參數

configuration_parameter

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

value

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

注意

範例

設定 wal_level：

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

相容性

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

參閱

ALTER TABLE

ALTER TABLE — 變更資料表的定義

語法

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] 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
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 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 WITH OIDS
    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 table_constraint_using_index is:

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

說明

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 本身並不會改變資料表中的任何內容，它只是設定在將來的資料表更新期間追求的策略。有關更多訊息，請參閱第 66.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 命令建立的一樣。特別要注意是，刪除限制條件會使索引消失。

注意

在需要增加新的限制條件情況下，使用現有索引加上約束可能會很有幫助。這種情況下需要加上新限制條件需要很長一段時間但不會阻斷資料表更新。為此，請使用 CREATE INDEX CONCURRENTLY 建立索引，然後使用此語法將其作為官方限制條件進行安裝。請參閱後續的例子。

ALTER CONSTRAINT

在資料表變更先前建立限制條件屬性。目前只有外部鍵限制條件可以變更。

VALIDATE CONSTRAINT

此語法透過掃描資料表來驗證先前設定為 NOT VALID 的外部鍵或檢查限制條件，以確保沒有不滿足限制條件的資料列。如果限制條件已被標記為有效，則不會產生任何行為。

驗證對於大型資料表可能是一個漫長的過程。將驗證與初始設定分離的價值在於，您可以將驗證延遲到不太繁忙的時間處理，或者可以用來給額外的時間來糾正原先存在的錯誤，同時防止出現新的錯誤。另請注意，驗證本身並不妨礙它在執行時對該資料表的正常寫入命令。

驗證僅獲取變更資料表上的 SHARE UPDATE EXCLUSIVE 鎖定。如果限制條件是外部鍵，那麼在限制條件引用的資料表上也需要 ROW SHARE 鎖定。

DROP CONSTRAINT [ IF EXISTS ]

這個語法會在資料表上刪除指定的限制條件。如果指定了 IF EXISTS 並且該限制條件不存在，就不會引發錯誤。在這種情況下，會發布提示通知。

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

這個語法設定這個資料表的觸發器。被禁用的觸發器仍然是系統已知的，只是在發生觸發事件時不會執行而已。對於延遲觸發器，當事件發生時檢查啟用狀態，而不是在實際執行觸發器函數時檢查。可以禁用或啟用由名稱指定的單個觸發器或資料表中的所有觸發器，或者僅禁用使用者的觸發器（此選項不包括內部生成的限制條件觸發器，例如用於實作外部鍵約束或可延遲唯一性和排除限制條件的觸發器）。禁用或啟用內部生成的限制條件觸發器需要超級使用者權限；應該謹慎對待，因為如果不執行觸發器，限制條件的完整性將無法得到保證。觸發器的觸發機制也會受設定變數 session_replication_role 的影響。當複製角色是「origin」（預設）或「local」時，只需啟用觸發器就會觸發。配置為 ENABLE REPLICA 的觸發器只會在連線處於「replica」模式時觸發，而設定為 ENABLE ALWAYS 的觸發器將觸發，不論目前的複複模式為何。

此指令會取得一個 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。

注意

雖然 CREATE TABLE 允許在 WITH（storage_parameter）語法中指定 OIDS，但 ALTER TABLE 不會將 OIDS 視為儲存參數。而是使用 SET WITH OIDS 和 SET WITHOUT OIDS 語法來變更 OID 狀態。

RESET ( storage_parameter [, ... ] )

此語法將一個或多個儲存參數重置為其預設值。和 SET 一樣，可能需要重新寫入資料來完成更新其效果。

INHERIT parent_table

此子句將目標資料表加到指定的父資料表中成為新的子資料表。然後，針對父資料表的查詢將會包含目標資料表的資料。要作為子資料表加入前，目標資料表必須已經包含與父資料表的所有欄位（它也可以具有其他欄位）。這些欄位必須具有可匹配的資料型別，並且如果它們在父資料表中具有 NOT NULL 限制條件，那麼它們還必須在子資料表中也具有 NOT NULL 限制條件。

對於父資料表的所有 CHECK 限制條件，必須還有相對應的子資料表限制條件，除非父資料表中標記為不可繼承（即使用ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT 所建立的，它們將會被忽略；所有匹配的子資料表限制條件不得標記為不可繼承。目前不用考慮 UNIQUE，PRIMARY KEY 和 FOREIGN KEY，但未來這些可能會改變。

NO INHERIT parent_table

此字句從指定的父資料表的子資料表中刪除目標資料表。針對父資料表的查詢將不再包含從目標資料表中所産生的記錄。

OF type_name

此子句將資料表連接到複合型別，就像 CREATE TABLE OF 已經産生它一樣。該資料表的欄位名稱和型別必須與組合型別的列表完全吻合；oid 系統欄位的存在會有所不同。該資料表不得從任何其他的資料表繼承。這些限制確保了 CREATE TABLE OF 將得到一個等效的資料表定義。

NOT OF

此子句將複合型別資料表從它的型別中分離出來。

OWNER

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

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 TABLESPACE

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

語法

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )

說明

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

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

參數

name

現有資料表空間的名稱。

new_name

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

new_owner

資料表空間的新擁有者。

tablespace_option

要設定或重置的資料表空間參數。目前，唯一可用的參數是 seq_page_cost，random_page_cost 和 effective_io_concurrency。為特定資料表空間設定任一值將覆蓋查詢規劃器一般從該資料表空間中的資料表中讀取頁面成本的估計值，這由相同名稱的配置參數（請參閱 seq_page_cost，random_page_cost，effective_io_concurrency）所決定。如果一個資料表空間位於比一般 I/O 子系統更快或更慢的磁碟上，這可能很有用。

範例

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

ALTER TABLESPACE index_space RENAME TO fast_raid;

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

ALTER TABLESPACE index_space OWNER TO mary;

相容性

SQL 標準中沒有 ALTER TABLESPACE 語句。

參閱

CREATE TABLESPACE, DROP TABLESPACE

ALTER TRIGGER

ALTER TRIGGER — change the definition of a trigger

Synopsis

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

Examples

To rename an existing trigger:

To mark a trigger as being dependent on an extension:

Compatibility

ALTER TRIGGER is a PostgreSQL extension of the SQL standard.

ALTER TYPE

ALTER TYPE — change the definition of a type

Synopsis

ALTER TYPE name action [, ... ]
ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value

where action is one of:

    ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
    DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
    ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

Description

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

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

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

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

This form changes the owner of the type.RENAME

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

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

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

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

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

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

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

Parameters

name

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

new_name

The new name for the type.

new_owner

The user name of the new owner of the type.

new_schema

The new schema for the type.

attribute_name

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

new_attribute_name

The new name of the attribute to be renamed.

data_type

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

new_enum_value

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

neighbor_enum_value

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

existing_enum_value

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

CASCADE

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

RESTRICT

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

Notes

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

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

Examples

To rename a data type:

ALTER TYPE electronic_mail RENAME TO email;

To change the owner of the type email to joe:

ALTER TYPE email OWNER TO joe;

To change the schema of the type email to customers:

ALTER TYPE email SET SCHEMA customers;

To add a new attribute to a type:

ALTER TYPE compfoo ADD ATTRIBUTE f3 int;

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

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

To rename an enum value:

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

Compatibility

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

ALTER VIEW

ALTER VIEW — 變更檢視表的定義

語法

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。

注意

由於歷史因素，ALTER TABLE 也可以用於檢視表；但是檢視表能允許的 ALTER TABLE 的語法就等同於上面所列出的語法。

範例

要將檢視表 foo 重新命名為 bar：

要將預設欄位值加到可更新檢視表：

相容性

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

參閱

ANALYZE

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

語法

說明

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

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

參數

VERBOSE

啟用進度訊息的顯示。

table_name

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

column_name

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

輸出

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

注意

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

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

被分析欄位中最大的統計目標決定了為準備統計訊息而採樣的資料列數。增加目標會使得進行 ANALYZE 所需的時間和空間成比例增加。

如果正在分析的資料表有一個或多個子資料表，ANALYZE 將收集兩次統計訊息：一次僅在父資料表的資料列上，第二次在父資料表的資料列上及其所有子資料表。在規劃遍歷整個繼承樹的查詢時，需要第二組統計訊息。但是，autovacuum 背景程序在決定是否觸發對該資料表的自動分析時，只會考慮父資料表本身的插入或更新。如果很少插入或更新該資料表，則除非您手動運行 ANALYZE，否則繼承統計訊息將不是最新的。

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

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

相容性

SQL 標準中沒有 ANALYZE 語句。

參閱

CLUSTER

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.

COMMENT

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

語法

說明

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

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

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

參數

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 加上註解：

再來移除它：

更多例子：

相容性

SQL 標準中並沒有 COMMENT 指令。

COPY

COPY — 在檔案和資料表之間複製資料

語法

說明

COPY 在 PostgreSQL 資料表和標準檔案系統的檔案之間移動資料。COPY TO 將資料表的內容複製到檔案，而 COPY FROM 將資料從檔案複製到資料表（將資料附加到資料表中）。COPY TO 還可以複製 SELECT 查詢的結果。

如果指定了欄位列表，則 COPY 將僅將指定欄位中的資料複製到檔案或從檔案複製。如果資料表中有任何欄位不在欄位列表中，則 COPY FROM 將插入這些欄位的預設值。

帶有檔案名稱的 COPY 指示 PostgreSQL 伺服器直接讀取或寫入檔案。PostgreSQL 使用者必須可以存取該檔案（伺服器執行的作業系統使用者 ID），並且必須從伺服器的角度指定名稱。使用 PROGRAM 時，伺服器執行給定的命令並從程序的標準輸出讀取，或寫入程序的標準輸入。必須從伺服器的角度使用該命令，並且該命令可由 PostgreSQL 作業系統使用者執行。指定 STDIN 或 STDOUT 時，資料透過用戶端和伺服器之間的連線傳輸。

參數

table_name

現有資料表的名稱（可選擇性加上綱要）。

column_name

要複製欄位的選擇性列表。如果未指定欄位列表，則將複製資料表的所有欄位。

query

對於 INSERT，UPDATE 和 DELETE 查詢，必須提供 RETURNING 子句，並且目標關連不能具有條件規則，也不能具有 ALSO 規則，也不能具有延伸為多個語句的 INSTEAD 規則。

filename

輸入或輸出檔案的路徑名稱。輸入檔案名稱可以是絕對路徑或相對路徑，但輸出檔案名稱必須是絕對路徑。Windows 使用者可能需要使用 E''字串並將路徑名稱中所使用的任何倒斜線加倍。

PROGRAM

要執行的命令。在 COPY FROM 中，從命令的標準輸出讀取輸入；在 COPY TO 中，輸出給寫入命令的標準輸入。

請注意，該命令由 shell 呼叫，因此如果您需要將任何參數傳遞給來自不受信任來源的 shell 命令，則必須小心去除或轉義可能對 shell 具有特殊含義的任何特殊字串。出於安全原因，最好使用固定的命令字串，或者至少避免在其中傳遞任何使用者輸入參數。

STDIN

指定輸入來自用戶端應用程式。

STDOUT

指定輸出轉到用戶端應用程序式。

boolean

指定是應打開還是關閉所選選項。您可以寫入TRUE，ON 或 1 以啟用該選項，使用 FALSE，OFF 或 0 來停用它。布林值也可以省略，在這種情況下假定為 TRUE

FORMAT

選擇要讀取或寫入的資料格式：text，csv（逗號分隔值）或二進位。預設為 text。

OIDS

指定複製每個資料列的 OID。（如果為沒有 OID 的資料表指定 OIDS，或者在複製查詢的情況下，會引發錯誤。）

FREEZE

請求複製已經凍結的資料列，就像在執行 VACUUM FREEZE 命令之後一樣。這是初始資料載入的效能選項。只有在目前子事務中建立或清空正在載入的資料表時，才會凍結資料列，而沒有使用中游標，並且此事務不保留舊的快照。

請注意，所有其他連線在成功載入後將立即能夠看到資料。這違反了 MVCC 可見性的正常規則，使用者應該知道這可能的潛在問題。

DELIMITER

指定用於分隔檔案每行內欄位的字元。預設值為 text 格式的 tab 字元，CSV 格式的逗號。這必須是一個單位元組字元。採用二進位格式時不允許使用此選項。

NULL

指定表示空值的字串。預設值為 text 格式的 N（倒斜線-N）和 CSV 格式的未加引號的空字串。對於不希望將空值與空字串區分開的情況，即使是 text 格式，也可能更喜歡空字串。採用二進位格式時不允許使用此選項。

注意使用 COPY FROM 時，與該字串匹配的任何資料項都將儲存為空值，因此您應確保使用與 COPY TO 相同的字串。

HEADER

指定該檔案包含標題列，其中包含檔案中每個欄位的名稱。在輸出時，第一行包含資料表中的欄位名稱；在輸入時，第一行將被忽略。僅在採用 CSV 格式時才允許此選項。

QUOTE

指定引用資料值時要使用的引用字元。預設為雙引號。這必須是一個單位元組字元。僅在採用 CSV 格式時才允許此選項。

ESCAPE

指定應在與 QUOTE 值匹配的資料字元之前出現的字元。預設值與 QUOTE 值相同（因此，如果引號字元出現在資料中，則引號字元加倍）。這必須是一個單位元組字元。僅在使用 CSV 格式時才允許此選項。

FORCE_QUOTE

強制引用用於每個指定欄位中的所有非 NULL 值。從不引用 NULL 輸出。如果指定 *，則將在所有欄位中引用非 NULL 值。此選項僅在 COPY TO 中允許，並且僅在使用 CSV 格式時允許。

FORCE_NOT_NULL

不要將指定欄位的值與空字串匹配。在 null 字串為空的預設情況下，這意味著空值將被讀取為零長度字串而不是空值，即使它們未被引用也是如此。此選項僅在 COPY FROM 中允許，並且僅能用在 CSV 格式時。

FORCE_NULL

將指定欄位的值與空字串匹配，即使它已被引用，如果找到匹配項，則將值設定為 NULL。在 null 字串為空的預設情況下，這會將帶引號的空字串轉換為 NULL。此選項僅在 COPY FROM 中允許，並且僅能用在 CSV 格式。

ENCODING

指定文件在 encoding_name 中編碼。如果省略此選項，則使用目前用戶端編碼。有關詳細訊息，請參閱下面的註釋。

輸出

成功完成後，COPY 命令將回傳命令標記的形式

計數是複製的資料列數量。

提醒僅當命令不是 COPY ... TO STDOUT 或等效的 psql 元命令 \copy ... to stdout 時，psql 才會輸出此命令標記。這是為了防止命令標記與剛剛輸出的資料混淆。

注意

COPY TO 只能用於普通資料表，而不能用於檢視表。但是，您可以使用 COPY（SELECT * FROM viewname） ...以被複製檢視表的目前內容。

COPY FROM 可以與普通資料表一起使用，也可以與具有 INSTEAD OF INSERT 觸發器的檢視表一起使用。

COPY 僅處理指定名稱的資料表；它不會將資料複製到子資料表或從子資料表複製資料。因此，例如 COPY table TO 會輸出與 SELECT FROM ONLY table 相同的資料。但 COPY（SELECT FROM table）TO ... 可用於轉存繼承結構中的所有資料。

您必須對其值由 COPY TO 讀取的資料表具有 select 權限，並對透過 COPY FROM 插入值的資料表有 INSERT 權限。在命令中列出的欄位上具有欄位權限就足夠了。

如果為資料表啟用了資料列級安全性原則，則相關的 SELECT 安全原則將套用於 COPY table TO 語句。目前，具有資料列級安全性的資料表不支援 COPY FROM。請改用等效的 INSERT 語句。

在 COPY 命令中所指名的檔案由伺服器直接讀取或寫入，而不是由用戶端應用程序讀取或寫入。因此，它們必須儲存在資料庫伺服器主機上，或者具有它們的存取能力，而非用戶端。它們必須是 PostgreSQL 使用者帳號（伺服器執行的使用者 ID）可存取，可讀或可寫，而不是用戶端。同樣地，用 PROGRAM 指定的命令是由伺服器直接執行，而不是由用戶端應用程序執行，且必須由 PostgreSQL 使用者執行。COPY 指名的檔案或命令僅允許資料庫超級使用者使用，因為它允許讀取或寫入伺服器有權存取的任何檔案。

建議始終都將 COPY 中使用的檔案名稱指定為絕對路徑。這在 COPY TO 的情況下由伺服器是強制執行的，但對於 COPY FROM，您可以選擇由相對路徑指定的檔案中讀取。該路徑將相對於伺服器程序的工作目錄（通常是叢集的資料目錄）作為起點，而不是用戶端的工作目錄。

使用 PROGRAM 執行命令可能受作業系統的存取控制機制（如 SELinux）所限制。

COPY FROM 將呼叫目標資料表上的所有觸發器和檢查限制條件。但是，它不會呼叫規則。

對於標識欄位，COPY FROM 命令將會寫入輸入資料中提供的欄位值，如 INSERT 選項 OVERRIDING SYSTEM VALUE。

COPY 輸入和輸出受 DateStyle 影響。為確保可以使用非預設 DateStyle 設定的其他 PostgreSQL 安裝的可移植性，應在使用 COPY TO 之前將 DateStyle 設定為 ISO。避免使用 IntervalStyle 設定 tosql_standard 轉存資料也是一個好主意，因為負間隔值可能會被具有不同 IntervalStyle 設定的伺服器所誤解。

輸入資料根據 ENCODING 選項或目前用戶端編碼進行解譯，輸出資料以 ENCODING 或目前用戶端編碼進行編碼，即使資料未透過用戶端而直接由伺服器讀取或寫入檔案。

COPY 會在第一個錯誤時停止操作。這不應該會使 COPY TO 出現問題，但目標資料表已經收到了 COPY FROM 中的之前的資料列。這些資料列將不可見或無法存取，但它們仍佔用磁碟空間。如果故障發生在大量的複製操作中，則可能相當於浪費大量磁碟空間。您可能需要呼叫 VACUUM 來恢復浪費的空間。

FORCE_NULL 和 FORCE_NOT_NULL 可以在同一個欄位上同時使用。這會導致將帶引號的空字串轉換為空值，將不帶引號的空字串轉換為空字串。

檔案格式

Text Format

使用文字格式時，讀取或寫入的資料是一個文字檔案，資料表的每筆資料會產生一行。行中的欄位由分隔符號分隔。欄位值本身是每個屬性的資料型別的輸出函數所產生的字串，或輸入函數可接受的字串。使用指定的空字串代替空欄位。如果輸入檔案的任何行包含的欄位比預期的多或少，則 COPY FROM 將引發錯誤。如果指定了 OIDS，則將 OID 讀取或寫入為資料欄位之前的第一欄位。

End of data can be represented by a single line containing just backslash-period (\.). An end-of-data marker is not necessary when reading from a file, since the end of file serves perfectly well; it is needed only when copying data to or from client applications using pre-3.0 client protocol.

Backslash characters (\) can be used in the COPY data to quote data characters that might otherwise be taken as row or column delimiters. In particular, the following characters must be preceded by a backslash if they appear as part of a column value: backslash itself, newline, carriage return, and the current delimiter character.

The specified null string is sent by COPY TO without adding any backslashes; conversely, COPY FROM matches the input against the null string before removing backslashes. Therefore, a null string such as \N cannot be confused with the actual data value \N (which would be represented as \\N).

The following special backslash sequences are recognized by COPY FROM:

Presently, COPY TO will never emit an octal or hex-digits backslash sequence, but it does use the other sequences listed above for those control characters.

Any other backslashed character that is not mentioned in the above table will be taken to represent itself. However, beware of adding backslashes unnecessarily, since that might accidentally produce a string matching the end-of-data marker (\.) or the null string (\N by default). These strings will be recognized before any other backslash processing is done.

It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively. At present it is possible to represent a data carriage return by a backslash and carriage return, and to represent a data newline by a backslash and newline. However, these representations might not be accepted in future releases. They are also highly vulnerable to corruption if the COPY file is transferred across different machines (for example, from Unix to Windows or vice versa).

COPY TO will terminate each row with a Unix-style newline (“\n”). Servers running on Microsoft Windows instead output carriage return/newline (“\r\n”), but only for COPY to a server file; for consistency across platforms, COPY TO STDOUT always sends “\n” regardless of server platform. COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines. To reduce the risk of error due to un-backslashed newlines or carriage returns that were meant as data, COPY FROM will complain if the line endings in the input are not all alike.

CSV Format

此格式選項用於匯入和匯出許多其他應用程式（例如試算表）也常使用的逗號分隔（CSV, Comma Separated Value）檔案格式。它不會使用 PostgreSQL 的標準文字格式所使用轉譯規則，而是產成通用的 CSV 轉譯機制。

每個記錄中的值由 DELIMITER 字元分隔。如果該值包含 DELIMITER，QUOTE 字元，NULL 字串，Carriage Return 或換行字元，則整個值將以 QUOTE 字元前後夾住，以及在 QUOTE 字元或 ESCAPE 字元前面有轉譯字元。在特定欄位中輸出非 NULL 值時，也可以使用 FORCE_QUOTE 強制使用引號。

CSV 格式沒有區分空值和空字串的標準方法。PostgreSQL 的 COPY 透過引號來處理。輸出 NULL 作為 NULL 參數字串，並且不加引號，而與 NULL 參數字串相符的非 NULL 值則被加引號。例如，使用預設設定，將 NULL 寫入未加引號的空字串，而將空字串資料值寫入雙引號（""）。讀取時則遵循類似的規則。您可以使用 FORCE_NOT_NULL 來防止對特定欄位進行 NULL 輸入比較。您還可以使用 FORCE_NULL 將帶引號的空字串轉換為 NULL。

由於反斜線不是 CSV 格式的特殊字元，因此 .（資料結尾標記）也可能會顯示為資料。為避免任何誤解，請使用 . 在行上顯示為單獨項目的資料將在輸出上自動加上引號，並且在輸入（如果加引號）時不會被解釋為資料結束標記。如果要載入的檔案是由另一個應用程式產生的，該檔案只有一個未加引號的欄位，並且值可能為 .，則可能需要在輸入檔案中將值加上引號。

在 CSV 格式中，所有字元均為有效字元。用引號括起來的值用空格或除 DELIMITER 以外的任何字元包圍，將包括這些字元。如果從從空白行填充到固定寬度的 CSV 行的系統中匯入資料，則可能會導致錯誤。如果出現這種情況，在將資料匯入 PostgreSQL 之前，可能需要預處理 CSV 檔案以除去尾隨的空白字元。

CSV 格式將識別並產生帶有括號的 CSV 檔案，這些值包含嵌入式回車字元和換行字元。因此，與文字格式的檔案相比，檔案並非嚴格限於每筆資料一行。

許多程式會產生奇怪的，有時是錯誤的 CSV 檔案，因此檔案格式更像是一種約定，而不是一種標準。因此，您可能會遇到一些無法使用此機制匯入的檔案，並且 COPY 也可能會產生成其他程式無法處理的檔案內容。

Binary 格式

binary 格式選項使所有資料以二進位格式而不是文字形式儲存/讀取。它比 text 和 CSV 格式快一些，但二進位格式檔案在機器架構和 PostgreSQL 版本之間的可移植性較低。此外，二進位格式是資料型別專屬的；例如，它不能從 smallint 欄位輸出二進位資料並將其讀入 int 欄位，即使它在 text 格式中可以正常運作。

二進位檔案格式由檔案標頭，包含資料列資料的零個或多個 tuple 以及檔案結尾組成。標頭和資料按 network byte order 排列。

7.4 之前的 PostgreSQL 版本使用了不同的二進位檔案格式。

File Header

The file header consists of 15 bytes of fixed fields, followed by a variable-length header extension area. The fixed fields are:Signature

11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is a required part of the signature. (The signature is designed to allow easy identification of files that have been munged by a non-8-bit-clean transfer. This signature will be changed by end-of-line-translation filters, dropped zero bytes, dropped high bits, or parity changes.)Flags field

32-bit integer bit mask to denote important aspects of the file format. Bits are numbered from 0 (LSB) to 31 (MSB). Note that this field is stored in network byte order (most significant byte first), as are all the integer fields used in the file format. Bits 16-31 are reserved to denote critical file format issues; a reader should abort if it finds an unexpected bit set in this range. Bits 0-15 are reserved to signal backwards-compatible format issues; a reader should simply ignore any unexpected bits set in this range. Currently only one flag bit is defined, and the rest must be zero:Bit 16

if 1, OIDs are included in the data; if 0, notHeader extension area length

32-bit integer, length in bytes of remainder of header, not including self. Currently, this is zero, and the first tuple follows immediately. Future changes to the format might allow additional data to be present in the header. A reader should silently skip over any header extension data it does not know what to do with.

The header extension area is envisioned to contain a sequence of self-identifying chunks. The flags field is not intended to tell readers what is in the extension area. Specific design of header extension contents is left for a later release.

This design allows for both backwards-compatible header additions (add header extension chunks, or set low-order flag bits) and non-backwards-compatible changes (set high-order flag bits to signal such changes, and add supporting data to the extension area if needed).

Tuples

Each tuple begins with a 16-bit integer count of the number of fields in the tuple. (Presently, all tuples in a table will have the same count, but that might not always be true.) Then, repeated for each field in the tuple, there is a 32-bit length word followed by that many bytes of field data. (The length word does not include itself, and can be zero.) As a special case, -1 indicates a NULL field value. No value bytes follow in the NULL case.

There is no alignment padding or any other extra data between fields.

Presently, all data values in a binary-format file are assumed to be in binary format (format code one). It is anticipated that a future extension might add a header field that allows per-column format codes to be specified.

To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the *send and *recv functions for each column's data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution).

If OIDs are included in the file, the OID field immediately follows the field-count word. It is a normal field except that it's not included in the field-count. In particular it has a length word — this will allow handling of 4-byte vs. 8-byte OIDs without too much pain, and will allow OIDs to be shown as null if that ever proves desirable.

File Trailer

The file trailer consists of a 16-bit integer word containing -1. This is easily distinguished from a tuple's field-count word.

A reader should report an error if a field-count word is neither -1 nor the expected number of columns. This provides an extra check against somehow getting out of sync with the data.

範例

以下範例使用破折號「|」作為欄位分隔符把資料表複製到用戶端：

要將檔案中的資料複製到 country 資料表中：

要將名稱以「A」開頭的國家複製到檔案中：

要複製到壓縮檔案，可以透過外部壓縮程序輸出：

以下是適合從 STDIN 複製到資料表中的資料範例：

請注意，每行上的空白實際上是 tab 字元。

以下是相同的資料，以二進位格式輸出。在透過 Unix 實用工具 od -c 過濾後顯示資料。該資料表有三個欄位；第一個是 char(2) 型別，第二個是 text 型別，第三個是 integer 型別。所有行在第三欄位中都具有空值。

相容性

SQL 標準中沒有 COPY 語句。

在 PostgreSQL 版本 9.0 之前使用了以下語法並且仍然支援：

請注意，在此語法中，BINARY 和 CSV 被視為獨立的關鍵字，而不是 FORMAT 選項的參數。

在 PostgreSQL 版本 7.3 之前使用了以下語法，並且仍然支援：

CREATE CAST

CREATE CAST — define a new cast

Synopsis

Description

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

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

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

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

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

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

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

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

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

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

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

Note

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

Parameters

source_type

The name of the source data type of the cast.target_type

The name of the target data type of the cast.function_name[(argument_type [, ...])]

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

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

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

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

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

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

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

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

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

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

Notes

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

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

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

Note

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

Note

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

Examples

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

(This cast is already predefined in the system.)

Compatibility

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

CREATE DATABASE

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

語法

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

說明

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

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

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

參數

name

要建立的資料庫名稱。

user_name

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

template

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

encoding

要在新資料庫中使用的字元集編碼。指定字串常數（例如，'SQL_ASCII'）或整數編碼數字，或指定 DEFAULT 以使用預設編碼（即樣板資料庫的編碼）。 PostgreSQL 伺服器支援的字元集在第 23.3.1 節中描述。其他限制請參閱下面說明。

lc_collate

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

lc_ctype

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

tablespace_name

將與新資料庫關連的資料表空間名稱，或 DEFAULT 以使用樣板資料庫的資料表空間。此資料表空間將是用於在此資料庫中建立物件的預設資料表空間。有關更多訊息，請參閱 CREATE TABLESPACE。

allowconn

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

connlimit

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

istemplate

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

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

注意

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

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

使用 DROP DATABASE 移除資料庫。

工具 createdb 是一個封裝此指令的程式，為方便起見而提供。

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

雖然可以透過將其名稱指定為模板來複製除 template1 之外的資料庫，但這並不是（通常）用作通用的「COPY DATABASE」工具。主要限制是在複製樣板資料庫時不能有其他連線到樣板資料庫。如果啟動時存在任何其他連線，則 CREATE DATABASE 將失敗；否則，在 CREATE DATABASE 完成之前，將鎖定與樣版資料庫新的連線。有關更多訊息，請參閱第 22.3 節。

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

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

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

範例

要建立新資料庫：

CREATE DATABASE lusiadas;

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

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

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

CREATE DATABASE music
    LC_COLLATE 'sv_SE.utf8' LC_CTYPE 'sv_SE.utf8'
    TEMPLATE template0;

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

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

CREATE DATABASE music2
    LC_COLLATE 'sv_SE.iso885915' LC_CTYPE 'sv_SE.iso885915'
    ENCODING LATIN9
    TEMPLATE template0;

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

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

相容性

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

參閱

ALTER DATABASE, DROP DATABASE

CREATE EXTENSION

CREATE EXTENSION — 安裝延伸套件

語法

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

Description

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 EXTENSIONadditionally records the identities of all the created objects, so that they can be dropped again if DROP EXTENSION is issued.

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.

Parameters

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

FROM old_version must be specified when, and only when, you are attempting to install an extension that replaces an “old style” module that is just a collection of objects not packaged into an extension. This option causes CREATE EXTENSION to run an alternative installation script that absorbs the existing objects into the extension, instead of creating new objects. Be careful that SCHEMA specifies the schema containing these pre-existing objects.

The value to use for old_version is determined by the extension's author, and might vary if there is more than one version of the old-style module that can be upgraded into an extension. For the standard additional modules supplied with pre-9.1 PostgreSQL, use unpackaged for old_version when updating a module to extension style.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.

Notes

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.

For information about writing new extensions, see Section 37.15.

Examples

Install the hstore extension into the current database:

CREATE EXTENSION hstore;

Update a pre-9.1 installation of hstore into extension style:

CREATE EXTENSION hstore SCHEMA public FROM unpackaged;

Be careful to specify the schema in which you installed the existing hstore objects.

相容性

CREATE EXTENSION 是 PostgreSQL 的延伸功能。

參閱

ALTER EXTENSION, DROP EXTENSION

CREATE FOREIGN TABLE

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.

CREATE FOREIGN DATA WRAPPER

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 WRAPPERcreates 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 befdw_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 orNO VALIDATORis 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 typetext[], which will contain the array of options as stored in the system catalogs, and one of typeoid, which will be the OID of the system catalog containing the options. The return type is ignored; the function should report invalid options using theereport(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 wrapperdummy:

CREATE FOREIGN DATA WRAPPER dummy;

Create a foreign-data wrapperfilewith handler functionfile_fdw_handler:

CREATE FOREIGN DATA WRAPPER file HANDLER file_fdw_handler;

Create a foreign-data wrappermywrapperwith some options:

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

Compatibility

CREATE FOREIGN DATA WRAPPERconforms to ISO/IEC 9075-9 (SQL/MED), with the exception that theHANDLERandVALIDATORclauses are extensions and the standard clausesLIBRARYandLANGUAGEare not implemented inPostgreSQL.

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

CREATE FUNCTION

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 INDEX

CREATE INDEX — 定義一個新的索引

語法

說明

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

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

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

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

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

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

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

參數

UNIQUE

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

CONCURRENTLY

IF NOT EXISTS

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

name

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

table_name

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

method

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

column_name

資料表欄位的名稱。

expression

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

collation

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

opclass

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

ASC

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

DESC

指定遞減排序。

NULLS FIRST

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

NULLS LAST

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

storage_parameter

tablespace_name

predicate

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

索引儲存參數

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

fillfactor

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

GiST 索引另外接受此參數：

buffering

GIN 索引接受不同的參數：

fastupdate

提醒

透過 ALTER INDEX 關閉 fastupdate 可防止將來的插入進入擱置的索引項目列表，但本身不會更新以前的項目。您可能希望 VACUUM 資料表或之後呼叫 gin_clean_pending_list 函數以確保清空擱置列表。

gin_pending_list_limit

BRIN 索引接受不同的參數：

pages_per_range

autosummarize

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

同步建立索引

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

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

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

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

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

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

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

注意

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

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

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

範例

要在資料表中的欄位 title 上建立 B-tree 索引：

要在表示式 lower(title) 上建立索引，允許有效的不分大小寫搜尋：

（在此範例中，我們選擇省略索引名稱，因此系統將選擇一個名稱，通常為 films_lower_idx。）

要使用非預設排序規則建立索引：

要建立具有空值的非預設排序順序索引：

要使用非預設 fill factor 建立索引：

要建立停用快速更新的 GIN 索引：

要在資料表 film 中的欄位 code 上建立索引，並將索引放在資料表空間 indexspace 中：

要在 point 屬性上建立 GiST 索引，以便我們可以在轉換函數的結果上有效地使用 box 運算子：

要建立索引但不鎖定對資料表的寫入：

相容性

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

參閱

CREATE LANGUAGE

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

語法

說明

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

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

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

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

VALIDATOR valfunction

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

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

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

注意

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

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

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

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

範例

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

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

相容性

CREATE LANGUAGE 是 PostgreSQL 的延伸功能。

參閱

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW — 定義一個新的具體化檢視表

語法

說明

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

TABLESPACE tablespace_name

query

WITH [ NO ] DATA

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

相容性

CREATE MATERIALIZED VIEW is a PostgreSQL extension.

參閱

CREATE DOMAIN

CREATE DOMAIN — define a new domain

Synopsis

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

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:

Compatibility

The command CREATE DOMAIN conforms to the SQL standard.

CREATE POLICY

CREATE POLICY — 為資料表定義新的資料列級的安全原則

語法

說明

CREATE POLICY 指令用於為資料表定義新的資料列級安全原則。請注意，你必須在資料表上啟用資料列級安全性（使用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY）以便套用所建立的原則。

安全原則會授予 SELECT、INSERT、UPDATE 或 DELETE 與相關安全原則表示式所匹配的資料列的權限。根據 USING 中指定的表示式檢查現有的資料列，同時根據 WITH CHECK 中指定的表示式檢查透過 INSERT 或 UPDATE 建立的新資料列。當 USING 表示式對給定的資料列回傳 true 時，那麼該資料對使用者是可見的，而如果回傳 false 或 null，那麼該資料列為不可見。當 WITH CHECK 表示式對一筆資料列回傳 true 時，則插入或更新該資料列，而如果回傳 false 或 null，則會產生錯誤。

對於 INSERT 和 UPDATE 語句而言，在觸發 BEFORE 觸發器之後，以及在進行任何實際的資料修改之前，WITH CHECK 表示式都會強制執行。因此，BEFORE ROW 觸發器可能會修改要插入的資料，從而影響安全原則檢查的結果。WITH CHECK 表示式會在任何其他限制條件之前執行。

安全原則的名稱是對應每個資料表的。因此，一個原則名稱可用於許多不同的資料表，並為每個資料表定義適合該表格的定義。

安全原則可以應用於特定的指令或特定角色。新建立的安全原則預設適用於所有的指令和角色，除非另有設定。多個原則可能適用於單個命令；請參閱下面的詳細訊息。總結了不同類型的原則如何應用於特定指令。

對於同時具有 USING 和 WITH CHECK 表達式（ALL 和 UPDATE）的安全原則，如果沒有定義 WITH CHECK 表示式，那麼 USING 表示式將用於確定哪些資料列為可見（一般的 USING 情況）以及哪些新資料列將會允許新增（WITH CHECK 情況下）。

如果對資料表啟用資料列級安全性，但卻沒有適用的原則，則會假定「預設拒絕」的原則，不會顯示或更新任何資料列。

參數

name

要建立的原則名稱。它必須與資料表的任何其他原則的名稱不同。

table_name

該原則適用的資料表名稱（可選擇性加上 schema）。

PERMISSIVE

指定將原則建立為寬鬆的原則。所有適用查詢的寬鬆原則將使用布林運算的「OR」運算組合在一起。通過建立寬鬆的原則，管理者可以增加可以存取的資料。安全原則預設就是寬容的。

RESTRICTIVE

指定將該原則建立為限制性原則。所有適用查詢的限制性原則將使用布林運算「AND」運算組合在一起。透過建立限制性原則，管理者可以減少可以存取的資料集合大小，因為必須為每條資料都會檢核所有限制性策略。

請注意，在限制性原則可用於減少存取權限之前，需要至少有一項允許原則來授予對於資料列的存取權限。如果只有限制性原則存在，則不能存取任何資料列。如果存在一系列的寬鬆原則和限制性原則，那麼除了所有限制性原則之外，只有在至少有一項寬鬆原則通過的情況下才能得得資料。

command

該原則適用的指令。有效的選項是 ALL、SELECT、INSERT、UPDATE 和 DELETE。ALL 是預設值。請參閱下面有關如何應用這些選項的細節。

role_name

要適用該原則的角色。預設值是 PUBLIC，它將會把原則適用於所有角色。

using_expression

可以是任何的 SQL 條件表示式（回傳布林值）。條件表示式不能包含任何彙總函數或窗函數。如果啟用了資料列的安全原則，則將此表示式加入到引用該資料表的查詢中。表示式回傳 true 的資料列將會是可見的。表示式回傳 false 或 null 的任何資料列對使用者來說都是不可見的（在 SELECT 中），並且不可用於資料更新（在 UPDATE 或 DELETE 中）。這樣的資料列被無聲地壓制；不會有錯誤的回報。

check_expression

為一 SQL 條件表示式（回傳布林值）。條件表示式不能包含任何彙總函數或窗函數。如果啟用了資料列的安全原則，則將在針對該資料表的 INSERT 和 UPDATE 查詢中使用此表示式。只有表示式認定為 true 的資料列才會被允許操作。如果對於插入的任何資料或由更新產生的任何資料，表示式的計算結果為 false 或 null，則會引發錯誤。請注意，check_expression 將根據資料列的建議新內容進行評估，而不是原始內容。

個別指令安全原則

ALL

將 ALL 用於安全原則中意味著它將適用於所有指令，而不管指令的類型如何。如果同時存在 ALL 原則及更具體的原則，則兩個原則都會適用。此外，如果僅定義了 USING 表示式，則所有原則都將適用於查詢類操作的和更新類操作，對兩種情況均使用 USING 表示式。

舉例來說，當執行 UPDATE 時，ALL 原則將適用於 UPDATE 將能夠選擇作為要更新的資料列（適用 USING 表示式）和更新後的資料列，以檢查它們是否它們允許增加到資料表中（如果定義了 WITH CHECK 表示式，則使用 WITH CHECK 表示式，否則使用 USING 表示式）。如果 INSERT 或 UPDATE 指令嘗試將資料列加入到未通過 ALL 原則的 WITH CHECK 表示式的資料表中，則整個指令將被中止。

SELECT

將 SELECT 用於原則意味著它將適用於 SELECT 查詢，所有當定義原則的關連需要 SELECT 權限的時候。結果是只有通過 SELECT 原則的那些資料才會在 SELECT 查詢中回傳，而那些需要 SELECT 權限的查詢（如 UPDATE）也只能看到 SELECT 原則允許的那些資料。SELECT 原則不能有 WITH CHECK 表示式，因為它只適用於從關連中檢索資料的情況。

INSERT

在原則中使用 INSERT 意味著它將適用於 INSERT 指令。插入的資料列不通過此原則將導致原則違規錯誤，並且整個 INSERT 指令將被中止。INSERT 原則不能有 USING 表示式，因為它只適用於資料被增加到關連中的情況。

請注意，帶有 ON CONFLICT DO UPDATE 的 INSERT 只對於隨 INSERT 路徑追加到關連的資料列檢查 INSERT 原則的 WITH CHECK 表示式。

UPDATE

在安全原則中使用 UPDATE 意味著它將適用於 UPDATE、SELECT FOR UPDATE 和 SELECT FOR SHARE 指令，以及 INSERT 指令的輔助 ON CONFLICT DO UPDATE 子句。由於 UPDATE 涉及取得現有資料並用新的更新資料替換它，所以 UPDATE 原則同時接受 USING 表示式和 WITH CHECK 表示式。USING 表示式定義 UPDATE 命令將查看哪些資料進行操作，而 WITH CHECK 表示式則定義允許哪些修改後的資料儲回關連之中。

任何未通過 WITH CHECK 表示式的資料列都會導致錯誤，並且使得整個命令被中止。如果僅指定 USING 子句，則該子句將同時用於 USING 和 WITH CHECK 兩種情況。

通常，UPDATE 指令還需要從正在更新中的欄位（例如，在 WHERE 子句或 RETURNING 子句中，又或者在 SET 子句的右側的表示式中）讀取資料。在這種情況下，正在更新的關連也需要 SELECT 權限，除 UPDATE 原則外，還將適用適當的 SELECT 或 ALL 原則。因此，除了被授予通過 UPDATE 或 ALL 原則更新資料列的權限之外，用戶還必須能夠存取通過 SELECT 或 ALL 原則更新的資料列。

當 INSERT 指令具有輔助的 ON CONFLICT DO UPDATE 子句時，如果採用 UPDATE 執行路徑，則首先針對任何 UPDATE 原則的 USING 表示式檢查要更新的資料列，然後根據 WITH CHECK 表示式檢查即將更新的資料列。但是請注意，與獨立的 UPDATE 指令不同，如果現有的資料列未通過 USING 表示式，則會引發錯誤（UPDATE 執行路徑永遠不會被默默地忽視）。

DELETE

將 DELETE 用於原則意味著它將適用於 DELETE 命令。只有通過此原則的資料列才會被 DELETE 指令看到。如果不能通過 DELETE 原則的 USING 表達式，但可以透過 SELECT 顯示不能刪除的資料列。

在大多數情況下，DELETE 指令還需要從正在刪除中的關連（例如，在 WHERE 子句或 RETURNING 子句中）的欄位讀取資料。在這種情況下，就還需要 SELECT 權限，所以除了 DELETE 原則外，還將適用適當的 SELECT 或 ALL 策略。因此，除了被授予通過 DELETE 或 ALL 原則刪除資料列的權限外，使用者還必須能夠存取通過 SELECT 或 ALL 原則刪除的資料列。

DELETE 原則不能有 WITH CHECK 表示式，因為它只適用於從關連中刪除資料的情況，所以沒有新的資料需要檢查。

Table 240. Policies Applied by Command Type

多安全原則的套用方式

當不同命令類型的多個原則適用於同一指令（例如，適用於 UPDATE 指令的 SELECT 和 UPDATE 原則）時，使用者必須同時具有這兩種類型指令的權限（例如，從關連中查詢資料列的權限以及允許可以更新它們）。因此將會使用 AND 運算將一種原則類型的表示式與其他類型原則的表示式組合在一起。

當相同指令類型的多個原則套用於同一個指令時，必須至少有一個 PERMISSIVE 原則授予的存取權限，而所有 RESTRICTIVE 原則都必須通過。也就是，所有的 PERMISSIVE 原則表示式均使用 OR 組合，而所有 RESTRICTIVE 原則表示式都使用 AND 進行組合，並使用 AND 組合其結果。如果沒有 PERMISSIVE 原則，則存取將會被拒絕。

請注意，出於合併多個原則的目的，所有原則都被視為與正在套用的其他任何類型的原則具有相同的類型。

例如，在需要 SELECT 和 UPDATE 權限的 UPDATE 指令中，如果每種類型都有多個適用的原則，則它們將按如下方式組合：

Notes

您必須是資料表的擁有者才能為其建立或變更安全原則。

雖然安全原則是應用於對資料庫中資料表的查詢，但系統在內部執行參考完整性檢查或驗證限制條件時並不會套用安全原則。這意味著有間接的方法來確認給定的值是否存在。其中的一個例子是嘗試將重複值插入到主鍵或具有唯一值限制的欄位中。如果插入失敗，則使用者可以推斷該值已經存在。（這個例子假定使用者在安全原則下允許插入他們不允許看到的資料。）另一個例子是允許使用者插入資料表，而該表引用了另一個資料表，即使資料表被隱藏了。存在值可以由使用者在引用資料表中插入值來確定，如果成功表示該值存在於引用表中。這些問題可以透過仔細制定安全原則來解決，以防止用戶能夠以插入、刪除或更新所有可能表明他們無法看到的值的資料，或者使用衍生的值（例如代理鍵）而不是具有外在意義的鍵。

通常，為了防止受保護數據無意中暴露給可能不可信的使用者自訂函數，系統將在出現在使用者查詢的資格之前執行使用安全原則施加的過濾條件。但是，系統（或系統管理員）標記為 LEAKPROOF 功能的操作員可以在原則表示式之前進行執行函式，因為它們被認為是可信的。

由於原則表示式是直接加到使用者的查詢中，它們將以執行整個查詢的使用者的權限執行。因此，使用給定原則的使用者必須能夠存取表示式中所引用的任何資料表或函數，否則當嘗試查詢啟用了資料列級安全性的資料表時，它們將會收到拒絕權限的錯誤。然而，這並不會改變 View 的工作方式。與普通查詢和 View 一樣，View 所引用的資料表權限檢查和原則將使用 View 擁有者的權限並套用於視圖擁有者的任何原則。

Compatibility

CREATE POLICY 屬於 PostgreSQL 延伸指令。

參考文件

CREATE PROCEDURE

CREATE PROCEDURE — define a new procedure

Synopsis

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

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

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.

obj_file, link_symbol

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

Examples

Compatibility

DROP POLICY

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

Synopsis

DROP POLICY [ IF EXISTS ] 
name
 ON 
table_name
 [ CASCADE | RESTRICT ]

Description

DROP POLICYremoves the specified policy from the table. Note that if the last policy is removed for a table and the table still has row level security enabled viaALTER TABLE, then the default-deny policy will be used.ALTER TABLE ... DISABLE ROW LEVEL SECURITYcan be used to disable row level security for a table, whether policies for the table exist or not.

Parameters

IF EXISTS

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

name

The name of the policy to drop.

table_name

The name (optionally schema-qualified) of the table that the policy is on.

CASCADE

RESTRICT

These key words do not have any effect, since there are no dependencies on policies.

Examples

To drop the policy calledp1on the table namedmy_table:

DROP POLICY p1 ON my_table;

Compatibility

DROP POLICYis aPostgreSQLextension.

CREATE TABLE

版本：11

CREATE TABLE — 建立一個新的資料表

語法

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where 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 like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }

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 )

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

說明

CREATE TABLE 將在目前資料庫中建立一個新的，初始化為空的資料表。該資料表將由發出此指令的使用者擁有。

如果加上了綱要名稱（例如，CREATE TABLE myschema.mytable ...），那麼將在指定的綱要中建立資料表。否則，它將在目前綱要中建立。臨時資料表存在於特殊綱要中，因此在建立臨時資料表時無法使用綱要名稱。資料表的名稱必須與同一綱要中的任何其他資料表、序列、索引、檢視表或外部資料表的名稱不同。

CREATE TABLE 會自動建立一個資料型別，表示與資料表的一個資料列對應的複合型別。因此，資料表不能與同一綱要中的任何現有資料型別具有相同的名稱。可選擇性加上限制條件子句指定新的資料列或更新資料列必須滿足的限制條件才能使其插入或更新操作成功。限制條件是一個 SQL 物件，它有助於以各種方式定義資料表中的有效值集合。

定義限制條件有兩種方法：資料表限制條件和欄位限制條件。欄位限制條件被定義為欄位定義的一部分。資料表限制條件定義不依賴於特定欄位，它可以包含多個欄位。

每個欄位限制條件也可以寫為資料表限制條件；欄位限制條件只是在其限制僅影響一欄位時使用的語法方便。

為了能夠建立資料表，您必須分別對所有欄位型別或 OF 子句中的型別具有 USAGE 權限。

參數

TEMPORARY or TEMP

如果使用此參數，則將資料表建立為臨時資料表。臨時資料表會在連線結束時自動刪除，或者選擇性地在目前交易事務結束時刪除（請參閱下面的 ON COMMIT）。當臨時資料表存在時，目前連線不會顯示具有相同名稱的現有永久資料表，除非它們使用綱要限定的名稱引用。在臨時資料表上建立的任何索引也都自動是臨時的。

由於 autovacuum 背景程序無法存取，因此無法對臨時資料表進行清理或分析。所以，應透過線上的 SQL 命令執行適當的清理和分析操作。例如，如果要在複雜查詢中使用臨時資料表，在填入資料後的臨時表上執行 ANALYZE 是個不錯的作法。

你可以選擇性地在 TEMPORARY 或 TEMP 之前加上 GLOBAL 或 LOCAL。目前這在 PostgreSQL 中沒有任何區別，也已經被棄用；請參閱相容性。

UNLOGGED

如果指定了這個選項，則將此表建立為無日誌記錄的資料表。寫入無日誌記錄資料表的資料不寫入 WAL（詳見第 29 章），這使得它們比普通的資料表快得多。但是，它們就不是完全安全的：在系統崩潰或不正常關閉之後，會自動清除無日誌記錄的資料表。無日誌記錄的資料表內容也無法複製到備用伺服器。在無日誌記錄資料表上所建的所有索引也沒有日誌記錄。

IF NOT EXISTS

如果已經存在同樣名稱的關連物件，請不要拋出錯誤。在這種情況下發出 NOTICE。請注意，不能保證現有關連物件類似於將要建立的關連物件。

table_name

要建立的資料表名稱（可選擇性加上的綱要）。

OF type_name

Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE ... CASCADE).

When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the CREATE TABLE command. But the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.

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 by PostgreSQL, refer to Chapter 8.

COLLATE collation

The COLLATE clause 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 optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Parent tables can be plain tables or foreign tables.

Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s).

If the same column name exists in more than one parent table, an error is reported unless the data types of the columns match in each of the parent tables. If there is no conflict, then the duplicate columns are merged to form a single column in the new table. If the column name list of the new table contains a column name that is also inherited, the data type must likewise match the inherited column(s), and the column definitions are merged into one. If the new table explicitly specifies a default value for the column, this default overrides any defaults from inherited declarations of the column. Otherwise, any parents that specify default values for the column must all specify the same default, or an error will be reported.

CHECK constraints are merged in essentially the same way as columns: if multiple parent tables and/or the new table definition contain identically-named CHECK constraints, these constraints must all have the same check expression, or an error will be reported. Constraints having the same name and expression will be merged into one copy. A constraint marked NO INHERIT in a parent will not be considered. Notice that an unnamed CHECK constraint in the new table will never be merged, since a unique name will always be chosen for it.

Column STORAGE settings are also copied from parent tables.

If a column in the parent table is an identity column, that property is not inherited. A column in the child table can be declared identity column if desired.

PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ opclass ] [, ...] )

The optional PARTITION BY clause specifies a strategy of partitioning the table. The table thus created is called a partitioned table. The parenthesized list of columns or expressions forms the partition key for the table. When using range or hash partitioning, the partition key can include multiple columns or expressions (up to 32, but this limit can be altered when building PostgreSQL), but for list partitioning, the partition key must consist of a single column or expression.

Range and list partitioning require a btree operator class, while hash partitioning requires a hash operator class. If no operator class is specified explicitly, the default operator class of the appropriate type will be used; if no default operator class exists, an error will be raised. When hash partitioning is used, the operator class used must implement support function 2 (see Section 37.16.3 for details).

A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. If no existing partition matches the values in the new row, an error will be reported.

Partitioned tables do not support EXCLUDE constraints; however, you can define these constraints on individual partitions.

See Section 5.11 for more discussion on table partitioning.

PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }

建資料表作為指定父資料表的分割區。此資料表可以使用 FOR VALUES 建立為特定值的分割區，也可以使用 DEFAULT 建立為預設分割區。父資料表中存在所有的索引、限制條件和使用者定義的資料列級觸發器都將複製到新的分割區上。

The partition_bound_spec must correspond to the partitioning method and partition key of the parent table, and must not overlap with any existing partition of that parent. The form with IN is used for list partitioning, the form with FROM and TO is used for range partitioning, and the form with WITH is used for hash partitioning.

partition_bound_expr is any variable-free expression (subqueries, window functions, aggregate functions, and set-returning functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated once at table creation time, so it can even contain volatile expressions such as CURRENT_TIMESTAMP.

When creating a list partition, NULL can be specified to signify that the partition allows the partition key column to be null. However, there cannot be more than one such list partition for a given parent table. NULL cannot be specified for range partitions.

When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound. That is, the values specified in the FROM list are valid values of the corresponding partition key columns for this partition, whereas those in the TO list are not. Note that this statement must be understood according to the rules of row-wise comparison (Section 9.23.5). For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.

The special values MINVALUE and MAXVALUE may be used when creating a range partition to indicate that there is no lower or upper bound on the column's value. For example, a partition defined using FROM (MINVALUE) TO (10) allows any values less than 10, and a partition defined using FROM (10) TO (MAXVALUE) allows any values greater than or equal to 10.

When creating a range partition involving more than one column, it can also make sense to use MAXVALUE as part of the lower bound, and MINVALUE as part of the upper bound. For example, a partition defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows where the first partition key column is greater than 0 and less than or equal to 10. Similarly, a partition defined using FROM ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first partition key column starts with "a".

Note that if MINVALUE or MAXVALUE is used for one column of a partitioning bound, the same value must be used for all subsequent columns. For example, (10, MINVALUE, 0) is not a valid bound; you should write (10, MINVALUE, MINVALUE).

Also note that some element types, such as timestamp, have a notion of "infinity", which is just another value that can be stored. This is different from MINVALUE and MAXVALUE, which are not real values that can be stored, but rather they are ways of saying that the value is unbounded. MAXVALUE can be thought of as being greater than any other value, including "infinity" and MINVALUE as being less than any other value, including "minus infinity". Thus the range FROM ('infinity') TO (MAXVALUE) is not an empty range; it allows precisely one value to be stored — "infinity".

If DEFAULT is specified, the table will be created as the default partition of the parent table. This option is not available for hash-partitioned tables. A partition key value not fitting into any other partition of the given parent will be routed to the default partition.

When a table has an existing DEFAULT partition and a new partition is added to it, the default partition must be scanned to verify that it does not contain any rows which properly belong in the new partition. If the default partition contains a large number of rows, this may be slow. The scan will be skipped if the default partition is a foreign table or if it has a constraint which proves that it cannot contain rows which should be placed in the new partition.

When creating a hash partition, a modulus and remainder must be specified. The modulus must be a positive integer, and the remainder must be a non-negative integer less than the modulus. Typically, when initially setting up a hash-partitioned table, you should choose a modulus equal to the number of partitions and assign every table the same modulus and a different remainder (see examples, below). However, it is not required that every partition have the same modulus, only that every modulus which occurs among the partitions of a hash-partitioned table is a factor of the next larger modulus. This allows the number of partitions to be increased incrementally without needing to move all the data at once. For example, suppose you have a hash-partitioned table with 8 partitions, each of which has modulus 8, but find it necessary to increase the number of partitions to 16. You can detach one of the modulus-8 partitions, create two new modulus-16 partitions covering the same portion of the key space (one with a remainder equal to the remainder of the detached partition, and the other with a remainder equal to that value plus 8), and repopulate them with data. You can then repeat this -- perhaps at a later time -- for each modulus-8 partition until none remain. While this may still involve a large amount of data movement at each step, it is still better than having to create a whole new table and move all the data at once.

A partition must have the same column names and types as the partitioned table to which it belongs. Modifications to the column names or types of a partitioned table will automatically propagate to all partitions. CHECK constraints will be inherited automatically by every partition, but an individual partition may specify additional CHECK constraints; additional constraints with the same name and condition as in the parent will be merged with the parent constraint. Defaults may be specified separately for each partition. But note that a partition's default value is not applied when inserting a tuple through a partitioned table.

Rows inserted into a partitioned table will be automatically routed to the correct partition. If no suitable partition exists, an error will occur.

Operations such as TRUNCATE which normally affect a table and all of its inheritance children will cascade to all partitions, but may also be performed on an individual partition. Note that dropping a partition with DROP TABLE requires taking an ACCESS EXCLUSIVE lock on the parent table.

LIKE source_table [ like_option ... ]

The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

Also unlike INHERITS, columns and constraints copied by LIKE are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error is signaled.

The optional like_option clauses specify which additional properties of the original table to copy. Specifying INCLUDING copies the property, specifying EXCLUDING omits the property. EXCLUDING is the default. If multiple specifications are made for the same kind of object, the last one is used. The available options are:

INCLUDING COMMENTS

Comments for the copied columns, constraints, and indexes will be copied. The default behavior is to exclude comments, resulting in the copied columns and constraints in the new table having no comments.

INCLUDING CONSTRAINTS

CHECK constraints will be copied. No distinction is made between column constraints and table constraints. Not-null constraints are always copied to the new table.

INCLUDING DEFAULTS

Default expressions for the copied column definitions will be copied. Otherwise, default expressions are not copied, resulting in the copied columns in the new table having null defaults. Note that copying defaults that call database-modification functions, such as nextval, may create a functional linkage between the original and new tables.

INCLUDING GENERATED

Any generation expressions of copied column definitions will be copied. By default, new columns will be regular base columns.

INCLUDING IDENTITY

Any identity specifications of copied column definitions will be copied. A new sequence is created for each identity column of the new table, separate from the sequences associated with the old table.

INCLUDING INDEXES

Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be created on the new table. Names for the new indexes and constraints are chosen according to the default rules, regardless of how the originals were named. (This behavior avoids possible duplicate-name failures for the new indexes.)

INCLUDING STATISTICS

Extended statistics are copied to the new table.

INCLUDING STORAGE

STORAGE settings for the copied column definitions will be copied. The default behavior is to exclude STORAGE settings, resulting in the copied columns in the new table having type-specific default settings. For more on STORAGE settings, see Section 68.2.

INCLUDING ALL

INCLUDING ALL is an abbreviated form selecting all the available individual options. (It could be useful to write individual EXCLUDING clauses after INCLUDING ALL to select all but some specific options.)

The LIKE clause can also be used to copy column definitions from views, foreign tables, or composite types. Inapplicable options (e.g., INCLUDING INDEXES from a view) are ignored.

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 like col must be positive can 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 ]

The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result, an error exception is raised and the insert or update does not alter the database. 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, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column.

A constraint marked with NO INHERIT will not propagate to child tables.

When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints. (PostgreSQL versions before 9.5 did not honor any particular firing order for CHECK constraints.)

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. 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.

GENERATED ALWAYS AS ( generation_expr ) STORED

This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned.

The keyword STORED is required to signify that the column will be computed on write and will be stored on disk.

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it.

The clauses ALWAYS and BY DEFAULT determine how the sequence value is given precedence over a user-specified value in an INSERT statement. If ALWAYS is specified, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is specified, then the user-specified value takes precedence. See INSERT for details. (In the COPY command, user-specified values are always used regardless of this setting.)

The optional sequence_options clause can be used to override the options of the sequence. See CREATE SEQUENCE for details.UNIQUE (column constraint)

UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ] (table constraint)

The UNIQUE constraint specifies that a group of one or more columns of a table can contain only unique values. The behavior of the unique table constraint is the same as that for column constraints, with the additional capability to span multiple columns.

For the purpose of a unique constraint, null values are not considered equal.

Each unique table constraint must name a set of columns that is different from the set of columns named by any other unique or primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.)

When establishing a unique constraint for a multi-level partition hierarchy, all the columns in the partition key of the target partitioned table, as well as those of all its descendant partitioned tables, must be included in the constraint definition.

Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. The optional clause INCLUDE adds to that index one or more columns on which the uniqueness is not enforced. Note that although the constraint is not enforced on the included columns, it still depends on them. Consequently, some operations on these columns (e.g. DROP COLUMN) can cause cascaded constraint and index deletion.

PRIMARY KEY (column constraint) PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ] (table constraint)

The PRIMARY KEY constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Only one primary key can be specified for a table, whether as a column constraint or a table constraint.

The primary key constraint should name a set of columns that is different from the set of columns named by any unique constraint defined for the same table. (Otherwise, the unique constraint is redundant and will be discarded.)

PRIMARY KEY enforces the same data constraints as a combination of UNIQUE and NOT NULL, but identifying a set of columns as the primary key also provides metadata about the design of the schema, since a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

PRIMARY KEY constraints share the restrictions that UNIQUE constraints have when placed on partitioned tables.

Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in the constraint. The optional INCLUDE clause allows a list of columns to be specified which will be included in the non-key portion of the index. Although uniqueness is not enforced on the included columns, the constraint still depends on them. Consequently, some operations on the included columns (e.g. DROP COLUMN) can cause cascaded constraint and index deletion.

EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ]

The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE. If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint, although an ordinary unique constraint will be faster. However, exclusion constraints can specify constraints that are more general than simple equality. For example, you can specify a constraint that no two rows in the table contain overlapping circles (see Section 8.8) by using the && operator.

Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class (see Section 11.10) for the index access method index_method. The operators are required to be commutative. Each exclude_element can optionally specify an operator class and/or ordering options; these are described fully under CREATE INDEX.

The access method must support amgettuple (see Chapter 61); at present this means GIN cannot be used. Although it's allowed, there is little point in using B-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn't do better. So in practice the access method will always be GiST or SP-GiST.

The predicate allows you to specify an exclusion constraint on a subset of the table; internally this creates a partial index. Note that parentheses are required around the predicate.REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint) FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (table constraint)

These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If the refcolumn list is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table. The user must have REFERENCES permission on the referenced table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note that foreign key constraints cannot be defined between temporary tables and permanent tables.

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)

In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually changed, no action is done. Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable. There are the following possible actions for each clause:

NO ACTION

Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

RESTRICT

Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

CASCADE

Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.

SET NULL

Set the referencing column(s) to null.

SET DEFAULT

Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.)

If the referenced column(s) are changed frequently, it might be wise to add an index to the referencing column(s) so that referential actions associated with the foreign key constraint can be performed more efficiently.

DEFERRABLE NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.

INITIALLY IMMEDIATE INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

USING method

This optional clause specifies the table access method to use to store the contents for the new table; the method needs be an access method of type TABLE. See Chapter 60 for more information. If this option is not specified, the default table access method is chosen for the new table. See default_table_access_method for more information.

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

This clause specifies optional storage parameters for a table or index; see Storage Parameters for more information. For backward-compatibility the WITH clause for a table can also include OIDS=FALSE to specify that rows of the new table should not contain OIDs (object identifiers), OIDS=TRUE is not supported anymore.

WITHOUT OIDS

This is backward-compatible syntax for declaring a table WITHOUT OIDS, creating a table WITH OIDS is not supported anymore.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

No special action is taken at the ends of transactions. This is the default behavior.

DELETE ROWS

All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit. When used on a partitioned table, this is not cascaded to its partitions.

DROP

The temporary table will be dropped at the end of the current transaction block. When used on a partitioned table, this action drops its partitions and when used on tables with inheritance children, it drops the dependent children.

TABLESPACE tablespace_name

The tablespace_name is the name of the tablespace in which the new table is to be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary. For partitioned tables, since no storage is required for the table itself, the tablespace specified overrides default_tablespace as the default tablespace to use for any newly created partitions when no other tablespace is explicitly specified.

USING INDEX TABLESPACE tablespace_name

This clause allows selection of the tablespace in which the index associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary.

Storage Parameters

The WITH clause can specify storage parameters for tables, and for indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint. Storage parameters for indexes are documented in CREATE INDEX. The storage parameters currently available for tables are listed below. For many of these parameters, as shown, there is an additional parameter with the same name prefixed with toast., which controls the behavior of the table's secondary TOAST table, if any (see Section 68.2 for more information about TOAST). If a table parameter value is set and the equivalent toast. parameter is not, the TOAST table will use the table's parameter value. Specifying these parameters for partitioned tables is not supported, but you may specify them for individual leaf partitions.

fillfactor (integer)

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

toast_tuple_target (integer)

The toast_tuple_target specifies the minimum tuple length required before we try to move long column values into TOAST tables, and is also the target length we try to reduce the length below once toasting begins. This only affects columns marked as either External or Extended and applies only to new tuples - there is no effect on existing rows. By default this parameter is set to allow at least 4 tuples per block, which with the default blocksize will be 2040 bytes. Valid values are between 128 bytes and the (blocksize - header), by default 8160 bytes. Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. This parameter cannot be set for TOAST tables.

parallel_workers (integer)

This sets the number of workers that should be used to assist a parallel scan of this table. If not set, the system will determine a value based on the relation size. The actual number of workers chosen by the planner or by utility statements that use parallel scans may be less, for example due to the setting of max_worker_processes.

autovacuum_enabled, toast.autovacuum_enabled (boolean)

Enables or disables the autovacuum daemon for a particular table. If true, the autovacuum daemon will perform automatic VACUUM and/or ANALYZE operations on this table following the rules discussed in Section 24.1.6. If false, this table will not be autovacuumed, except to prevent transaction ID wraparound. See Section 24.1.5 for more about wraparound prevention. Note that the autovacuum daemon does not run at all (except to prevent transaction ID wraparound) if the autovacuum parameter is false; setting individual tables' storage parameters does not override that. Therefore there is seldom much point in explicitly setting this storage parameter to true, only to false.

vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)

Enables or disables index cleanup when VACUUM is run on this table. The default value is true. Disabling index cleanup can speed up VACUUM very significantly, but may also lead to severely bloated indexes if table modifications are frequent. The INDEX_CLEANUP parameter of VACUUM, if specified, overrides the value of this option.

vacuum_truncate, toast.vacuum_truncate (boolean)

Enables or disables vacuum to try to truncate off any empty pages at the end of this table. The default value is true. If true, VACUUM and autovacuum do the truncation and the disk space for the truncated pages is returned to the operating system. Note that the truncation requires ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM, if specified, overrides the value of this option.

autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold (integer)

Per-table value for autovacuum_vacuum_threshold parameter.

autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (floating point)

Per-table value for autovacuum_vacuum_scale_factor parameter.

autovacuum_analyze_threshold (integer)

Per-table value for autovacuum_analyze_threshold parameter.

autovacuum_analyze_scale_factor (floating point)

Per-table value for autovacuum_analyze_scale_factor parameter.

autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (floating point)

Per-table value for autovacuum_vacuum_cost_delay parameter.

autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)

Per-table value for autovacuum_vacuum_cost_limit parameter.

autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)

Per-table value for vacuum_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_min_age parameters that are larger than half the system-wide autovacuum_freeze_max_age setting.

autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)

Per-table value for autovacuum_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_max_age parameters that are larger than the system-wide setting (it can only be set smaller).

autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (integer)

Per-table value for vacuum_freeze_table_age parameter.

autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer)

Per-table value for vacuum_multixact_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_min_age parameters that are larger than half the system-wide autovacuum_multixact_freeze_max_age setting.

autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer)

Per-table value for autovacuum_multixact_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_max_age parameters that are larger than the system-wide setting (it can only be set smaller).

autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer)

Per-table value for vacuum_multixact_freeze_table_age parameter.

log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)

Per-table value for log_autovacuum_min_duration parameter.

user_catalog_table (boolean)

Declare the table as an additional catalog table for purposes of logical replication. See Section 48.6.2 for details. This parameter cannot be set for TOAST tables.

Notes

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. (See CREATE INDEX for more information.)

Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional.

A table cannot have more than 1600 columns. (In practice, the effective limit is usually lower because of tuple-length constraints.)

Examples

Create table films and table distributors:

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE distributors (
     did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL CHECK (name <> '')
);

Create a table with a 2-dimensional array:

CREATE TABLE array_int (
    vector  int[][]
);

Define a unique table constraint for the table films. Unique table constraints can be defined on one or more columns of the table:

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

Define a check column constraint:

CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

Define a check table constraint:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

Define a primary key table constraint for the table films:

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint syntax:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

Assign a literal constant default value for the column name, arrange for the default value of column did to be generated by selecting the next value of a sequence object, and make the default value of modtime be the time at which the row is inserted:

CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

Define a unique constraint for the name column:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

The same, specified as a table constraint:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

Create the same table, specifying 70% fill factor for both the table and its unique index:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

Create table circles with an exclusion constraint that prevents any two circles from overlapping:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Create table cinemas in tablespace diskvol1:

CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;

Create a composite type and a typed table:

CREATE TYPE employee_type AS (name text, salary numeric);

CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
);

Create a range partitioned table:

CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

Create a range partitioned table with multiple columns in the partition key:

CREATE TABLE measurement_year_month (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

Create a list partitioned table:

CREATE TABLE cities (
    city_id      bigserial not null,
    name         text not null,
    population   bigint
) PARTITION BY LIST (left(lower(name), 1));

Create a hash partitioned table:

CREATE TABLE orders (
    order_id     bigint not null,
    cust_id      bigint not null,
    status       text
) PARTITION BY HASH (order_id);

Create partition of a range partitioned table:

CREATE TABLE measurement_y2016m07
    PARTITION OF measurement (
    unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

Create a few partitions of a range partitioned table with multiple columns in the partition key:

CREATE TABLE measurement_ym_older
    PARTITION OF measurement_year_month
    FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);

CREATE TABLE measurement_ym_y2016m11
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2016, 11) TO (2016, 12);

CREATE TABLE measurement_ym_y2016m12
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2016, 12) TO (2017, 01);

CREATE TABLE measurement_ym_y2017m01
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2017, 01) TO (2017, 02);

Create partition of a list partitioned table:

CREATE TABLE cities_ab
    PARTITION OF cities (
    CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');

Create partition of a list partitioned table that is itself further partitioned and then add a partition to it:

CREATE TABLE cities_ab
    PARTITION OF cities (
    CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);

CREATE TABLE cities_ab_10000_to_100000
    PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);

Create partitions of a hash partitioned table:

CREATE TABLE orders_p1 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Create a default partition:

CREATE TABLE cities_partdef
    PARTITION OF cities DEFAULT;

相容性

The CREATE TABLE command conforms to the SQL standard, with exceptions listed below.

Temporary Tables

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases.

The SQL standard also distinguishes between global and local temporary tables, where a local temporary table has a separate set of contents for each SQL module within each session, though its definition is still shared across sessions. Since PostgreSQL does not support SQL modules, this distinction is not relevant in PostgreSQL.

For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they currently have no effect. Use of these keywords is discouraged, since future versions of PostgreSQL might adopt a more standard-compliant interpretation of their meaning.

The ON COMMIT clause for temporary tables also resembles the SQL standard, but has some differences. If the ON COMMIT clause is omitted, SQL specifies that the default behavior is ON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option does not exist in SQL.

Non-Deferred Uniqueness Constraints

When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

Column Check Constraints

The SQL standard says that CHECK column constraints can only refer to the column they apply to; only CHECK table constraints can refer to multiple columns. PostgreSQL does not enforce this restriction; it treats column and table check constraints alike.

`EXCLUDE` Constraint

The EXCLUDE constraint type is a PostgreSQL extension.

`NULL` “Constraint”

The NULL “constraint” (actually a non-constraint) is a PostgreSQL extension to the SQL standard that is included for compatibility with some other database systems (and for symmetry with the NOT NULL constraint). Since it is the default for any column, its presence is simply noise.

Constraint Naming

The SQL standard says that table and domain constraints must have names that are unique across the schema containing the table or domain. PostgreSQL is laxer: it only requires constraint names to be unique across the constraints attached to a particular table or domain. However, this extra freedom does not exist for index-based constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated index is named the same as the constraint, and index names must be unique across all relations within the same schema.

Currently, PostgreSQL does not record names for NOT NULL constraints at all, so they are not subject to the uniqueness restriction. This might change in a future release.

Inheritance

Multiple inheritance via the INHERITS clause is a PostgreSQL language extension. SQL:1999 and later define single inheritance using a different syntax and different semantics. SQL:1999-style inheritance is not yet supported by PostgreSQL.

Zero-Column Tables

PostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from the SQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec restriction.

Multiple Identity Columns

PostgreSQL allows a table to have more than one identity column. The standard specifies that a table can have at most one identity column. This is relaxed mainly to give more flexibility for doing schema changes or migrations. Note that the INSERT command supports only one override clause that applies to the entire statement, so having multiple identity columns with different behaviors is not well supported.

Generated Columns

The option STORED is not standard but is also used by other SQL implementations. The SQL standard does not specify the storage of generated columns.

`LIKE` Clause

While a LIKE clause exists in the SQL standard, many of the options that PostgreSQL accepts for it are not in the standard, and some of the standard's options are not implemented by PostgreSQL.

`WITH` Clause

The WITH clause is a PostgreSQL extension; storage parameters are not in the standard.

Tablespaces

The PostgreSQL concept of tablespaces is not part of the standard. Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are extensions.

Typed Tables

Typed tables implement a subset of the SQL standard. According to the standard, a typed table has columns corresponding to the underlying composite type as well as one other column that is the “self-referencing column”. PostgreSQL does not support self-referencing columns explicitly.

`PARTITION BY` Clause

The PARTITION BY clause is a PostgreSQL extension.

`PARTITION OF` Clause

The PARTITION OF clause is a PostgreSQL extension.

參閱

ALTER TABLE, DROP TABLE, CREATE TABLE AS, CREATE TABLESPACE, CREATE TYPE

SELECT

SELECT, TABLE, WITH — 從資料表或檢視表中檢索資料列

語法

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

說明

SELECT 從零個或多個資料表中檢索資料列。SELECT 的一般處理如下：

計算 WITH 列表中的所有查詢語句。這有效地製作可以在 FROM 列表中引用的臨時資料表。在 FROM 中多次引用的 WITH 查詢僅會計算一次。（參閱下面的 WITH 子句。）
FROM 列表中的所有元素都是計算出來的。（FROM 列表中的每個元素可以是一個真實或虛擬的資料表。）如果在 FROM 列表中指定了多個元素，它們將會交叉查詢在一起。（參閱下面的 FROM 語句。）
如果指定了 WHERE 子句，則從會輸出中過濾掉所有不滿足條件的資料列。（參閱下面的 WHERE 子句。）
如果使用了 GROUP BY 子句，或者存在彙總函數的呼叫，則將輸出組合成與一個或多個相符合的資料列群組，合併計算彙總函數的結果。如果存在 HAVING 子句，則會刪除不滿足給定條件的群組。（參閱下面的 GROUP BY 子句和 HAVING 子句。）
使用每個選定的資料列或資料列群組的 SELECT 輸出表示式計算實際輸出的資料列。（參閱下面的 SELECT List。）
SELECT DISTINCT 從結果中刪除重複的資料列。SELECT DISTINCT ON 消除了在所有指定表示式上符合的資料列。SELECT ALL（預設值）將回傳所有的候選資料列，包括重複的資料列。（參閱下面的 DISTINCT 子句。）
使用運算子 UNION、INTERSECT 和 EXCEPT，可以組合多個 SELECT 語句的輸出以形成單個結果集合。UNION 運算子回傳一個或兩個結果集合中的所有資料列。 INTERSECT 運算子回傳同時在兩個結果集合中的所有資料列。EXCEPT 運算子回傳第一個結果集合中但不在第二個結果集合中的資料列。在所有三種情況下，除非指定了 ALL，否則將刪除重複的資料列。可以添加 DISTINCT 以明確指定消除重複資料列。請注意，DISTINCT 是此處的預設行為，即使 ALL 是 SELECT 本身的預設行為。（參閱下面的 UNION 子句、INTERSECT 子句和 EXCEPT 子句。）
如果使用了 ORDER BY 子句，則回傳的資料列按指定的順序排序。如果未使用 ORDER BY，則以系統最快産生的順序回傳資料列。（參閱下面的 ORDER BY 子句。）
如果使用了 LIMIT（或 FETCH FIRST）或 OFFSET 子句，則 SELECT 語句僅回傳結果資料列的子集。（參閱下面的 LIMIT 子句。）
如果使用了 FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE 或 FOR KEY SHARE，則 SELECT 語句將鎖定所選的資料列以防止同時更新。（參閱下面的 Locking 子句。）

您必須對 SELECT 指令中使用的每個欄位具有 SELECT 權限。FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE 或 FOR KEY SHARE 的使用也需要 UPDATE 權限（對於如此選擇的每個資料表的至少一個欄位）。

子句

`WITH` 子句

WITH 子句可以讓您指定一個或多個子查詢，這些子查詢可以在主查詢中以名稱引用。子查詢在主查詢的存續時間內有效地充當臨時資料表或檢視表。每個子查詢可以是 SELECT、TABLE、VALUES、INSERT、UPDATE 或 DELETE 語句。在 WITH 中使用資料變更語句（INSERT、UPDATE 或 DELETE）時，通常會包含 RETURNING 子句。它作為 RETURNING 的輸出，而不是語句修改的基礎資料表，它會構成主查詢所讀取的臨時資料表。如果省略 RETURNING，則仍會執行該語句，但不會產生任何輸出，因此主查詢就不能將其作為資料表所引用。

必須為每個 WITH 查詢指定名稱（不用限定綱要）。選擇性地，可以指定欄位名稱列表；如果省略的話，則從子查詢中推斷欄位名稱。

如果指定了 RECURSIVE，也允許 SELECT 子查詢以名稱引用本身。這樣的子查詢必須具有這樣的形式

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

遞迴自我引用必須出現在 UNION 的右側。每個查詢只允許一次遞迴自我引用。不支援遞迴式的資料變更語句，但您可以在資料變更語句中使用遞迴 SELECT 查詢的結果。相關範例，請參閱第 7.8 節。

RECURSIVE 的另一個作用是不需要對 WITH 查詢進行排序：查詢可以引用列表後面的另一個查詢。（但是，未實作循環引用或相互遞迴。）如果沒有 RECURSIVE，WITH 查詢只能引用相鄰的 WITH，精確來說是 WITH 列表中較早的查詢。

WITH 查詢的一個關鍵屬性是，每次執行主查詢時，它們只會被運算一次，即使主查詢多次引用它們也是如此。特別的是，無論主查詢是讀取所有輸出還是任何輸出，都保證資料變更語句只會執行一次。

主查詢和 WITH 查詢都是（概念上）同時執行的。這意味著除了透過讀取其 RETURNING 輸出之外，不能從查詢的其他部分看到 WITH 中的資料變更語句的執行結果。如果兩個此類資料變更語句嘗試修改同一筆資料，則無法預想其結果為何。

有關其他資訊，請參閱第 7.8 節。

`FROM` Clause

FROM子句為SELECT指定一個或多個來源資料表。如果指定了多個來源，則結果是所有來源的 Cartesian product（cross join）。但通常會加上過濾條件（透過 WHERE），將回傳的資料列限制在其中一小部分。

FROM 子句可以包含以下內容：

table_name

現有資料表或檢視表的名稱（可加上綱要名稱）。如果在資料表名稱之前指定了 ONLY，則僅掃描該資料表。如果未指定 ONLY，則掃描資料表及其所有繼承資料表（如果有的）。（選擇性）可以在資料表名稱後指定 * 以明確指示包含繼承資料表。

alias

提供 FROM 子句中的項目別名。別名用於簡潔或消除自我交叉查詢的模糊性（多次掃描同一個資料表）。提供別名時，它會完全隱藏資料表或函數的實際名稱；例如，給定 FROM foo AS f，SELECT 的其餘部分就必須將此項目稱為 f 而不是 foo。使用別名時，還可以編寫欄位別名列表以提供資料表的一個或多個欄位的替換名稱。

TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

table_name 之後的 TABLESAMPLE 子句表示應使用指定的 sampling_method 來檢索該資料表中的子集合。此抽樣將優先於任何其他過濾程序（如 WHERE 子句）。標準的 PostgreSQL 發行版包含兩種抽樣方法，BERNOULLI 和 SYSTEM，其他抽樣方法可以通過延伸功能安裝在資料庫中。

BERNOULLI 和 SYSTEM 抽樣方法都接受一個參數，該參數是要抽樣資料表的一部分，為 0 到 100 之間的百分比。此參數可以是任何實數表示式。（其他採樣方法可以接受更多或不同的參數。）這兩種方法都回傳一個隨機選擇的資料表樣本，該資料表將包含表行的大約指定的百分比。BERNOULLI 方法會掃描整個資料表，並以指定的機率獨立選擇或忽略各個資料列。SYSTEM 方法對具有指定機會被選中的每個磁碟區塊進行區塊級抽樣；回傳每個選定區塊中的所有資料列。當指定小的抽樣百分比時，SYSTEM 方法明顯快於 BERNOULLI 方法，但由於聚類效應，它可能回傳資料表中較不隨機的樣本。

選擇性的參數 REPEATABLE 子句指定用於在抽樣方法中産生隨機數的種子編號或表示式。種子值可以是任何非 null 浮點值。如果資料表沒有更新，則指定相同種子和參數值的兩個查詢將選擇資料表相同樣本。但是不同的種子值通常會產生不同的樣本。如果未設定 REPEATABLE，則由系統産生種子為每個查詢選擇新的隨機樣本。請注意，某些附加的抽樣方法不接受 REPEATABLE，每次使用時都會産生新的樣本。

select

sub-SELECT 可以出現在 FROM 子句中。就像在一般 SELECT 指令在執行時間時將其輸出建立為臨時資料表一樣。請注意，sub-SELECT 必須使用括號括起來，並且必須為它提供別名。這裡也可以使用 VALUES 指令。

with_query_name

透過使用其名稱來引用 WITH 查詢，就像查詢名稱是資料表名稱一樣。（事實上，為了主查詢的需要，WITH 查詢會隱藏任何同名的資料表。如果需要的話，也可以加上綱要限定的資料表名稱來引用同名的真實資料表。）別名可以使用，方式與資料表相同。

function_name

Function calls can appear in the FROM clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though the function's output were created as a temporary table for the duration of this single SELECT command. When the optional WITH ORDINALITY clause is added to the function call, a new column is appended after all the function's output columns with numbering for each row.

An alias can be provided in the same way as for a table. If an alias is written, a column alias list can also be written to provide substitute names for one or more attributes of the function's composite return type, including the column added by ORDINALITY if present.

Multiple function calls can be combined into a single FROM-clause item by surrounding them with ROWS FROM( ... ). The output of such an item is the concatenation of the first row from each function, then the second row from each function, etc. If some of the functions produce fewer rows than others, null values are substituted for the missing data, so that the total number of rows returned is always the same as for the function that produced the most rows.

If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ]). The column definition list must match the actual number and types of columns returned by the function.

When using the ROWS FROM( ... ) syntax, if one of the functions requires a column definition list, it's preferred to put the column definition list after the function call inside ROWS FROM( ... ). A column definition list can be placed after the ROWS FROM( ... ) construct only if there's just a single function and no WITH ORDINALITY clause.

To use ORDINALITY together with a column definition list, you must use the ROWS FROM( ... ) syntax and put the column definition list inside ROWS FROM( ... ).

join_type

One of

[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN

For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, ...]). See below for the meaning. For CROSS JOIN, none of these clauses can appear.

A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items.

CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE.

LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition. This left-hand row is extended to the full width of the joined table by inserting null values for the right-hand columns. Note that only the JOIN clause's own condition is considered while deciding which rows have matches. Outer conditions are applied afterwards.

Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). This is just a notational convenience, since you could convert it to a LEFT OUTER JOIN by switching the left and right tables.

FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).ON join_condition

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match.USING ( join_column [, ...] )

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

NATURAL

NATURAL 是 USING 列表的簡寫，它表示兩個資料表中具有匹配名稱的所有欄位。如果沒有共同的欄位名稱，則 NATURAL 等於 ON TRUE。

LATERAL

The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROMitem.)

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

The column source table(s) must be INNER or LEFT joined to the LATERAL item, else there would not be a well-defined set of rows from which to compute each set of rows for the LATERAL item. Thus, although a construct such as X RIGHT JOIN LATERAL Y is syntactically valid, it is not actually allowed for Y to reference X.

`WHERE` Clause

The optional WHERE clause has the general form

WHERE condition

where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

`GROUP BY` Clause

The optional GROUP BY clause has the general form

GROUP BY grouping_element [, ...]

GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

If any of GROUPING SETS, ROLLUP or CUBE are present as grouping elements, then the GROUP BY clause as a whole defines some number of independent grouping sets. The effect of this is equivalent to constructing a UNION ALL between subqueries with the individual grouping sets as their GROUP BY clauses. For further details on the handling of grouping sets see Section 7.2.4.

Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group. (If there are aggregate functions but no GROUP BY clause, the query is treated as having a single group comprising all the selected rows.) The set of rows fed to each aggregate function can be further filtered by attaching a FILTER clause to the aggregate function call; see Section 4.2.7 for more information. When a FILTER clause is present, only those rows matching it are included in the input to that aggregate function.

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

Keep in mind that all aggregate functions are evaluated before evaluating any “scalar” expressions in the HAVING clause or SELECT list. This means that, for example, a CASE expression cannot be used to skip evaluation of an aggregate function; see Section 4.2.14.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with GROUP BY.

`HAVING` Clause

The optional HAVING clause has the general form

HAVING condition

where condition is the same as specified for the WHERE clause.

HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns.

The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECTlist and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with HAVING.

`WINDOW` Clause

The optional WINDOW clause has the general form

WINDOW window_name AS ( window_definition ) [, ...]

where window_name is a name that can be referenced from OVER clauses or subsequent window definitions, and window_definition is

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

If an existing_window_name is specified it must refer to an earlier entry in the WINDOW list; the new window copies its partitioning clause from that entry, as well as its ordering clause if any. In this case the new window cannot specify its own PARTITION BY clause, and it can specify ORDER BY only if the copied window does not have one. The new window always uses its own frame clause; the copied window must not specify a frame clause.

The elements of the PARTITION BY list are interpreted in much the same fashion as elements of a GROUP BY Clause, except that they are always simple expressions and never the name or number of an output column. Another difference is that these expressions can contain aggregate function calls, which are not allowed in a regular GROUP BY clause. They are allowed here because windowing occurs after grouping and aggregation.

Similarly, the elements of the ORDER BY list are interpreted in much the same fashion as elements of an ORDER BY Clause, except that the expressions are always taken as simple expressions and never the name or number of an output column.

The optional frame_clause defines the window frame for window functions that depend on the frame (not all do). The window frame is a set of related rows for each row of the query (called the current row). The frame_clause can be one of

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

where frame_start and frame_end can be one of

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

If frame_end is omitted it defaults to CURRENT ROW. Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, frame_end cannot be UNBOUNDED PRECEDING, and the frame_end choice cannot appear earlier in the above list than the frame_start choice — for example RANGE BETWEEN CURRENT ROW AND value PRECEDING is not allowed.

The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; it sets the frame to be all rows from the partition start up through the current row's last peer (a row that ORDER BY considers equivalent to the current row, or all rows if there is no ORDER BY). In general, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly UNBOUNDED FOLLOWING means that the frame ends with the last row of the partition (regardless of RANGE or ROWS mode). In ROWS mode, CURRENT ROW means that the frame starts or ends with the current row; but in RANGE mode it means that the frame starts or ends with the current row's first or last peer in the ORDER BY ordering. The value PRECEDING and value FOLLOWING cases are currently only allowed in ROWS mode. They indicate that the frame starts or ends with the row that many rows before or after the current row. value must be an integer expression not containing any variables, aggregate functions, or window functions. The value must not be null or negative; but it can be zero, which selects the current row itself.

Beware that the ROWS options can produce unpredictable results if the ORDER BY ordering does not order the rows uniquely. The RANGE options are designed to ensure that rows that are peers in the ORDER BY ordering are treated alike; all peer rows will be in the same frame.

The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's SELECT List or ORDER BY Clause. These functions can reference the WINDOW clause entries by name in their OVER clauses. A WINDOW clause entry does not have to be referenced anywhere, however; if it is not used in the query it is simply ignored. It is possible to use window functions without any WINDOW clause at all, since a window function call can specify its window definition directly in its OVER clause. However, the WINDOW clause saves typing when the same window definition is needed for more than one window function.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with WINDOW.

Window functions are described in detail in Section 3.5, Section 4.2.8, and Section 7.2.5.

`SELECT` List

The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause.

Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display, but when the SELECT is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query. To specify the name to use for an output column, write AS output_name after the column's expression. (You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.) If you do not specify a column name, a name is chosen automatically by PostgreSQL. If the column's expression is a simple column reference then the chosen name is the same as that column's name. In more complex cases a function or type name may be used, or the system may fall back on a generated name such as ?column?.

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Instead of an expression, * can be written in the output list as a shorthand for all the columns of the selected rows. Also, you can write table_name.* as a shorthand for the columns coming from just that table. In these cases it is not possible to specify new names with AS; the output column names will be the same as the table columns' names.

According to the SQL standard, the expressions in the output list should be computed before applying DISTINCT, ORDER BY, or LIMIT. This is obviously necessary when using DISTINCT, since otherwise it's not clear what values are being made distinct. However, in many cases it is convenient if output expressions are computed after ORDER BY and LIMIT; particularly if the output list contains any volatile or expensive functions. With that behavior, the order of function evaluations is more intuitive and there will not be evaluations corresponding to rows that never appear in the output. PostgreSQL will effectively evaluate output expressions after sorting and limiting, so long as those expressions are not referenced in DISTINCT, ORDER BY or GROUP BY. (As a counterexample, SELECT f(x) FROM tab ORDER BY 1 clearly must evaluate f(x) before sorting.) Output expressions that contain set-returning functions are effectively evaluated after sorting and before limiting, so that LIMIT will act to cut off the output from a set-returning function.

Note

PostgreSQL versions before 9.6 did not provide any guarantees about the timing of evaluation of output expressions versus sorting and limiting; it depended on the form of the chosen query plan.

`DISTINCT` Clause

If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). SELECT ALL specifies the opposite: all rows are kept; that is the default.

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

retrieves the most recent weather report for each location. But if we had not used ORDER BY to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with DISTINCT.

`UNION` Clause

The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

The UNION operator computes the set union of the rows returned by the involved SELECT statements. A row is in the set union of two result sets if it appears in at least one of the result sets. The two SELECT statements that represent the direct operands of the UNION must produce the same number of columns, and corresponding columns must be of compatible data types.

The result of UNION does not contain any duplicate rows unless the ALL option is specified. ALL prevents elimination of duplicates. (Therefore, UNION ALL is usually significantly quicker than UNION; use ALL when you can.) DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for a UNION result or for any input of a UNION.

`INTERSECT` Clause

The INTERSECT clause has this general form:

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause.

The INTERSECT operator computes the set intersection of the rows returned by the involved SELECT statements. A row is in the intersection of two result sets if it appears in both result sets.

The result of INTERSECT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear min(m,n) times in the result set. DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for an INTERSECT result or for any input of an INTERSECT.

`EXCEPT` Clause

The EXCEPT clause has this general form:

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause.

The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.

The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m-n,0) times in the result set. DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. EXCEPT binds at the same level as UNION.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for an EXCEPT result or for any input of an EXCEPT.

`ORDER BY` Clause

The optional ORDER BY clause has this general form:

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

The ORDER BY clause causes the result rows to be sorted according to the specified expression(s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause.

It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list. Thus the following statement is valid:

SELECT name FROM distributors ORDER BY code;

A limitation of this feature is that an ORDER BY clause applying to the result of a UNION, INTERSECT, or EXCEPT clause can only specify an output column name or number, not an expression.

If an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY will interpret it as the output column name. This is the opposite of the choice that GROUP BY will make in the same situation. This inconsistency is made to be compatible with the SQL standard.

Optionally one can add the key word ASC (ascending) or DESC (descending) after any expression in the ORDER BY clause. If not specified, ASC is assumed by default. Alternatively, a specific ordering operator name can be specified in the USING clause. An ordering operator must be a less-than or greater-than member of some B-tree operator family. ASC is usually equivalent to USING < and DESC is usually equivalent to USING >. (But the creator of a user-defined data type can define exactly what the default sort ordering is, and it might correspond to operators with other names.)

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

Note that ordering options apply only to the expression they follow; for example ORDER BY x, y DESC does not mean the same thing as ORDER BY x DESC, y DESC.

Character-string data is sorted according to the collation that applies to the column being sorted. That can be overridden at need by including a COLLATE clause in the expression, for example ORDER BY mycolumn COLLATE "en_US". For more information see Section 4.2.10 andSection 23.2.

`LIMIT` Clause

The LIMIT clause consists of two independent sub-clauses:

LIMIT { count | ALL }
OFFSET start

count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.

SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

In this syntax, to write anything except a simple integer constant for start or count, you must write parentheses around it. If count is omitted in a FETCH clause, it defaults to 1. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. According to the standard, the OFFSET clause must come before the FETCH clause if both are present; but PostgreSQL is laxer and allows either order.

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.

The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.

The Locking Clause

FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE are locking clauses; they affect how SELECT locks rows as they are obtained from the table.

The locking clause has the general form

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

where lock_strength can be one of

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

For more information on each row-level lock mode, refer to Section 13.3.2.

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP LOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.

If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as usual. A locking clause without a table list affects all tables used in the statement. If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query. However, these clauses do not apply to WITH queries referenced by the primary query. If you want row locking to occur within a WITH query, specify a locking clause within the WITH query.

Multiple locking clauses can be written if it is necessary to specify different locking behavior for different tables. If the same table is mentioned (or implicitly affected) by more than one locking clause, then it is processed as if it was only specified by the strongest one. Similarly, a table is processed as NOWAIT if that is specified in any of the clauses affecting it. Otherwise, it is processed as SKIP LOCKED if that is specified in any of the clauses affecting it.

The locking clauses cannot be used in contexts where returned rows cannot be clearly identified with individual table rows; for example they cannot be used with aggregation.

When a locking clause appears at the top level of a SELECT query, the rows that are locked are exactly those that are returned by the query; in the case of a join query, the rows locked are those that contribute to returned join rows. In addition, rows that satisfied the query conditions as of the query snapshot will be locked, although they will not be returned if they were updated after the snapshot and no longer satisfy the query conditions. If a LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked). Similarly, if a locking clause is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked.

When a locking clause appears in a sub-SELECT, the rows locked are those returned to the outer query by the sub-query. This might involve fewer rows than inspection of the sub-query alone would suggest, since conditions from the outer query might be used to optimize execution of the sub-query. For example,

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

will lock only rows having col1 = 5, even though that condition is not textually within the sub-query.

Previous releases failed to preserve a lock which is upgraded by a later savepoint. For example, this code:

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

would fail to preserve the FOR UPDATE lock after the ROLLBACK TO. This has been fixed in release 9.3.

Caution

It is possible for a SELECT command running at the READ COMMITTED transaction isolation level and using ORDER BY and a locking clause to return rows out of order. This is because ORDER BY is applied first. The command sorts the result, but might then block trying to obtain a lock on one or more of the rows. Once the SELECT unblocks, some of the ordering column values might have been modified, leading to those rows appearing to be out of order (though they are in order in terms of the original column values). This can be worked around at need by placing the FOR UPDATE/SHARE clause in a sub-query, for example

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

Note that this will result in locking all rows of mytable, whereas FOR UPDATE at the top level would lock only the actually returned rows. This can make for a significant performance difference, particularly if the ORDER BY is combined with LIMIT or other restrictions. So this technique is recommended only if concurrent updates of the ordering columns are expected and a strictly sorted result is required.

At the REPEATABLE READ or SERIALIZABLE transaction isolation level this would cause a serialization failure (with a SQLSTATE of '40001'), so there is no possibility of receiving rows out of order under these isolation levels.

`TABLE` Command

The command

TABLE name

is equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH and FOR locking clauses can be used with TABLE; the WHERE clause and any form of aggregation cannot be used.

範例

要讓資料表 films 與資料表 distributors 進行交叉查詢的話：

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...

要在 films 資料表中以 kind 分組，並彙總 len 欄位的話：

SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38

To sum the column len of all films, group the results by kind and show those group totals that are less than 5 hours:

SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38

The following two examples are identical ways of sorting the individual results according to the contents of the second column (name):

SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward

The next example shows how to obtain the union of the tables distributors and actors, restricting the results to those that begin with the letter W in each table. Only distinct rows are wanted, so the key word ALL is omitted.

distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen

This example shows how to use a function in the FROM clause, both with and without a column definition list:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

Here is an example of a function with an ordinality column added:

SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
 unnest | ordinality
--------+----------
 a      |        1
 b      |        2
 c      |        3
 d      |        4
 e      |        5
 f      |        6
(6 rows)

This example shows how to use a simple WITH clause:

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t

         x          
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

Notice that the WITH query was evaluated only once, so that we got two sets of the same three random values.

This example uses WITH RECURSIVE to find all subordinates (direct or indirect) of the employee Mary, and their level of indirectness, from a table that shows only direct subordinates:

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

Notice the typical form of recursive queries: an initial condition, followed by UNION, followed by the recursive part of the query. Be sure that the recursive part of the query will eventually return no tuples, or else the query will loop indefinitely. (See Section 7.8 for more examples.)

This example uses LATERAL to apply a set-returning function get_product_names() for each row of the manufacturers table:

SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;

Manufacturers not currently having any products would not appear in the result, since it is an inner join. If we wished to include the names of such manufacturers in the result, we could do:

SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;

Compatibility

Of course, the SELECT statement is compatible with the SQL standard. But there are some extensions and some missing features.

Omitted `FROM` Clauses

PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions:

SELECT 2+2;

 ?column?
----------
        4

Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT.

Note that if a FROM clause is not specified, the query cannot reference any database tables. For example, the following query is invalid:

SELECT distributors.* WHERE distributors.name = 'Westward';

PostgreSQL releases prior to 8.1 would accept queries of this form, and add an implicit entry to the query's FROM clause for each table referenced by the query. This is no longer allowed.

Empty `SELECT` Lists

The list of output expressions after SELECT can be empty, producing a zero-column result table. This is not valid syntax according to the SQL standard. PostgreSQL allows it to be consistent with allowing zero-column tables. However, an empty list is not allowed when DISTINCT is used.

Omitting the `AS` Key Word

In the SQL standard, the optional key word AS can be omitted before an output column name whenever the new column name is a valid column name (that is, not the same as any reserved keyword). PostgreSQL is slightly more restrictive: AS is required if the new column name matches any keyword at all, reserved or not. Recommended practice is to use AS or double-quote output column names, to prevent any possible conflict against future keyword additions.

In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

`ONLY` and Inheritance

The SQL standard requires parentheses around the table name when writing ONLY, for example SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL considers these parentheses to be optional.

PostgreSQL allows a trailing * to be written to explicitly specify the non-ONLY behavior of including child tables. The standard does not allow this.

(These points apply equally to all SQL commands supporting the ONLY option.)

`TABLESAMPLE` Clause Restrictions

The TABLESAMPLE clause is currently accepted only on regular tables and materialized views. According to the SQL standard it should be possible to apply it to any FROM item.

Function Calls in `FROM`

PostgreSQL allows a function call to be written directly as a member of the FROM list. In the SQL standard it would be necessary to wrap such a function call in a sub-SELECT; that is, the syntax FROMfunc(...) alias is approximately equivalent to FROM LATERAL (SELECT func(...)) alias. Note that LATERAL is considered to be implicit; this is because the standard requires LATERAL semantics for an UNNEST() item in FROM. PostgreSQL treats UNNEST() the same as other set-returning functions.

Namespace Available to `GROUP BY` and `ORDER BY`

In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.

SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does.

Functional Dependencies

PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY) only when a table's primary key is included in the GROUP BY list. The SQL standard specifies additional conditions that should be recognized.

`LIMIT` and `OFFSET`

The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ... for the same functionality, as shown above in LIMIT Clause. This syntax is also used by IBM DB2. (Applications written for Oracle frequently use a workaround involving the automatically generated rownumcolumn, which is not available in PostgreSQL, to implement the effects of these clauses.)

Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in any SELECT query as well as in sub-SELECTs, but this is an extension. The FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE variants, as well as the NOWAIT and SKIP LOCKED options, do not appear in the standard.

Data-Modifying Statements in `WITH`

PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in the SQL standard.

Nonstandard Clauses

DISTINCT ON ( ... ) is an extension of the SQL standard.

ROWS FROM( ... ) is an extension of the SQL standard.

VI. 參考資訊

I. SQL 指令

ALTER DATABASE

Synopsis

Description

Parameters

Notes

Examples

Compatibility

See Also

ALTER DEFAULT PRIVILEGES

語法

說明

參數

注意

範例

相容性

參閱

ALTER EXTENSION

語法

說明

Parameters

範例

相容性

參閱

ALTER FUNCTION

語法

說明

參數

範例

相容性

參閱

ALTER INDEX

Synopsis

Description

Parameters

Notes

Examples

Compatibility

See Also

ALTER LANGUAGE

語法

說明

參數

相容性

參閱

ALTER MATERIALIZED VIEW

Synopsis

Description

Parameters

Examples

Compatibility

See Also

ALTER POLICY

語法

描述

參數

相容性

相關資訊

ALTER PUBLICATION

Synopsis

Description

Parameters

Examples

Compatibility

See Also

ALTER ROLE

語法

說明

參數

注意

範例

相容性

參閱

ALTER RULE

語法

說明

參數

範例

相容性