PostgreSQL表操作之表的創(chuàng)建及表基礎(chǔ)語法總結(jié)

更新時間：2024年05月29日 08:34:36 作者：DBA圈小圈

在PostgreSQL中創(chuàng)建表命令用于在任何給定的數(shù)據(jù)庫中創(chuàng)建新表,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL表操作之表的創(chuàng)建及表基礎(chǔ)語法的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

PostgreSQL表類型

PostgreSQL支持以下幾種表類型：

普通表（Regular Table）：這是最常用的表類型，用于存儲數(shù)據(jù)。
臨時表（Temporary Table）：這些表只在當前會話中存在，并在會話結(jié)束后自動刪除。臨時表通常用于存儲中間結(jié)果或臨時數(shù)據(jù)。
視圖（View）：視圖是一種虛擬的表，它是通過查詢已存在的表或其他視圖來創(chuàng)建的。視圖提供了一種簡化數(shù)據(jù)訪問的方式，并可以用作數(shù)據(jù)的過濾器。
外部表（External Table）：這些表實際上并不存儲數(shù)據(jù)，而是與外部數(shù)據(jù)源（如文件或遠程數(shù)據(jù)庫）建立連接，以便讀取和查詢數(shù)據(jù)。
分區(qū)表（Partitioned Table）：分區(qū)表將數(shù)據(jù)劃分為多個較小的子表，每個子表稱為一個分區(qū)。每個分區(qū)可以獨立查詢和管理，從而提高查詢性能。
顯式鎖定表（Explicitly Locked Table）：這種類型的表在執(zhí)行某些操作時會被鎖定，以防止其他會話對其進行讀取或修改。鎖定表用于處理并發(fā)訪問的問題。
復(fù)制表（Replicated Table）：復(fù)制表是通過將數(shù)據(jù)復(fù)制到多個節(jié)點來提供數(shù)據(jù)冗余和高可用性的表。

這些表類型提供了不同的功能和用途，可以根據(jù)具體需求選擇合適的表類型來存儲和管理數(shù)據(jù)。

創(chuàng)建表Create Table

命令：

postgres=# \help create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ] [ 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 [ NULLS [ NOT ] DISTINCT ] 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 [ NULLS [ NOT ] DISTINCT ] ( 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 | COMPRESSION | 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 } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

URL: https://www.postgresql.org/docs/16/sql-createtable.html

postgres=#

修改表命令 Alter Table

命令

postgres=# \help alter table
Command:     ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
    ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
    ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ACCESS METHOD new_access_method
    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_ROLE | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] 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 [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint_using_index is:

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

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

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

URL: https://www.postgresql.org/docs/16/sql-altertable.html

postgres=#

刪除表命令

命令

postgres=# postgres=# \help drop table
Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/16/sql-droptable.html

postgres=#

查看表列表命令

ci_database_test01=# \dtS
                    List of relations
   Schema   |           Name           | Type  |  Owner   
------------+--------------------------+-------+----------
 pg_catalog | pg_aggregate             | table | postgres
 pg_catalog | pg_am                    | table | postgres
 pg_catalog | pg_amop                  | table | postgres
 pg_catalog | pg_amproc                | table | postgres
 pg_catalog | pg_attrdef               | table | postgres
 pg_catalog | pg_attribute             | table | postgres
 pg_catalog | pg_auth_members          | table | postgres

或者

ci_database_test01=# select * from pg_tables;
ci_database_test01=# select * from pg_tables where schemaname= '<schema name>';

搜索表

命令

\dt pg_range

或者

ci_database_test01=# select * from pg_tables where tablename ='<table name>' ;

查看表詳情

命令

ci_database_test01=# \d pg_range
               Table "pg_catalog.pg_range"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 rngtypid      | oid     |           | not null | 
 rngsubtype    | oid     |           | not null | 
 rngmultitypid | oid     |           | not null | 
 rngcollation  | oid     |           | not null | 
 rngsubopc     | oid     |           | not null | 
 rngcanonical  | regproc |           | not null | 
 rngsubdiff    | regproc |           | not null | 
Indexes:
    "pg_range_rngtypid_index" PRIMARY KEY, btree (rngtypid)
    "pg_range_rngmultitypid_index" UNIQUE CONSTRAINT, btree (rngmultitypid)

ci_database_test01=#

或者

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_name = '<table name>';

ci_database_test01=# SELECT column_name, data_type, is_nullable, column_default
ci_database_test01-# FROM information_schema.columns 
ci_database_test01-# WHERE table_name = 'pg_range';
  column_name  | data_type | is_nullable | column_default 
---------------+-----------+-------------+----------------
 rngtypid      | oid       | NO          | 
 rngsubtype    | oid       | NO          | 
 rngmultitypid | oid       | NO          | 
 rngcollation  | oid       | NO          | 
 rngsubopc     | oid       | NO          | 
 rngcanonical  | regproc   | NO          | 
 rngsubdiff    | regproc   | NO          | 
(7 rows)

ci_database_test01=#

參數(shù)

參數(shù)	子參數(shù)	說明
TEMPORARY or TEMP		如果指定，則該表將創(chuàng)建為臨時表。臨時表在會話結(jié)束時自動刪除，或者在當前事務(wù)結(jié)束時自動刪除（見下文）。默認search_path首先包括臨時架構(gòu)，因此，當臨時表存在時，不會為新計劃選擇名稱相同的現(xiàn)有永久表，除非使用架構(gòu)限定的名稱引用它們。在臨時表上創(chuàng)建的任何索引也自動成為臨時索引。ON COMMIT autovacuum 守護程序無法訪問，因此無法對臨時表進行清空或分析。因此，應(yīng)通過會話 SQL 命令執(zhí)行適當?shù)恼婵蘸头治霾僮鳌＠?，如果要在?fù)雜查詢中使用臨時表，則最好在填充臨時表后對臨時表運行該表。ANALYZE 可選，or 可以寫在或之前。這目前在 PostgreSQL 中沒有區(qū)別，并且已被棄用;請參閱下面的兼容性。GLOBAL \ LOCAL TEMP OR ARYTEMP
UNLOGGED		如果指定，則該表將創(chuàng)建為未記錄的表。寫入未記錄表的數(shù)據(jù)不會寫入預(yù)寫日志，這使得它們比普通表快得多。但是，它們不是崩潰安全的：在崩潰或不干凈的關(guān)閉后，未記錄的表會自動截斷。未記錄表的內(nèi)容也不會復(fù)制到備用服務(wù)器。在未記錄的表上創(chuàng)建的任何索引也會自動取消記錄。如果指定此項，則與未記錄表（對于標識或序列列）一起創(chuàng)建的任何序列也會創(chuàng)建為未記錄。
IF NOT EXISTS		如果已存在同名關(guān)系，則不要引發(fā)錯誤。在這種情況下，將發(fā)出通知。請注意，不能保證現(xiàn)有關(guān)系與將要創(chuàng)建的關(guān)系類似。
table_name		要創(chuàng)建的表的名稱（可指定架構(gòu)Schema）。
OF type_name		創(chuàng)建一個類型化表，該表從指定的復(fù)合類型（可選地稱為架構(gòu)限定）中獲取其結(jié)構(gòu)。類型化表與其類型相關(guān)聯(lián);例如，如果刪除類型（with ）。DROP TYPE … CASCADE 創(chuàng)建類型化表時，列的數(shù)據(jù)類型由基礎(chǔ)復(fù)合類型確定，而不是由命令指定。但該命令可以向表添加默認值和約束，并可以指定存儲參數(shù)。CREATE TABLE
column_name		要在新表中創(chuàng)建的列的名稱。
data_type		列的數(shù)據(jù)類型。這可以包括數(shù)組說明符。
COLLATE collation		該子句為列分配排序規(guī)則（該列必須是可合并的數(shù)據(jù)類型）。如果未指定，則使用列數(shù)據(jù)類型的默認排序規(guī)則。
STORAGE { PLAIN / EXTERNAL / EXTENDED / MAIN / DEFAULT }		此窗體設(shè)置列的存儲模式。這將控制此列是內(nèi)聯(lián)保存還是在輔助 TOAST 表中保存，以及是否應(yīng)壓縮數(shù)據(jù)。必須用于固定長度的值。如inline, uncompressed. is for inline, compressible。用于外部未壓縮數(shù)據(jù)，也用于外部壓縮數(shù)據(jù)。寫入將存儲模式設(shè)置為列數(shù)據(jù)類型的默認模式。是大多數(shù)支持非存儲的數(shù)據(jù)類型的默認值。使用將使子字符串操作變得非常大，并且值運行得更快，但代價是增加了存儲空間。
COMPRESSION compression_method		該子句設(shè)置列的壓縮方法。壓縮僅支持可變寬度數(shù)據(jù)類型，并且僅當列的存儲模式為或時才使用壓縮。
INHERITS ( parent_table [, … ] )		可選子句指定一個新表自動從中繼承所有列的表列表。父表可以是普通表，也可以是外表。使用 of 會在新的子表與其父表之間創(chuàng)建持久關(guān)系。對父表的架構(gòu)修改通常也會傳播到子表，默認情況下，子表的數(shù)據(jù)包含在父表的掃描中。如果多個父表中存在相同的列名，則會報告錯誤，除非每個父表中的列的數(shù)據(jù)類型匹配。如果沒有沖突，則將合并重復(fù)的列以在新表中形成單個列。如果新表的列名列表包含也繼承的列名，則數(shù)據(jù)類型也必須與繼承的列匹配，并且列定義將合并為一個列。如果新表顯式指定了列的默認值，則此默認值將覆蓋從該列的繼承聲明中繼承的任何默認值。否則，任何為列指定默認值的父級都必須全部指定相同的默認值，否則將報告錯誤。 CHECK約束的合并方式與列的合并方式基本相同：如果多個父表和/或新表定義包含名稱相同的約束，則這些約束必須具有相同的檢查表達式，否則將報告錯誤。具有相同名稱和表達式的約束將合并到一個副本中。在父項中標記的約束將不予考慮。請注意，新表中的未命名約束永遠不會合并，因為將始終為其選擇唯一名稱，CHECKNO INHERITCHECK。列設(shè)置也是從父表復(fù)制的（STORAGE）。如果父表中的列是標識列，則不會繼承該屬性。如果需要，可以將子表中的列聲明為標識列。
PARTITION BY { RANGE / LIST / HASH } ( { column_name / ( expression ) } [ opclass ] [, …] )		可選子句指定對表進行分區(qū)的策略。這樣創(chuàng)建的表稱為分區(qū)表。帶括號的列或表達式列表構(gòu)成表的分區(qū)鍵。使用范圍或哈希分區(qū)時，分區(qū)鍵可以包含多個列或表達式（最多 32 個，但在構(gòu)建 PostgreSQL 時可以更改此限制），但對于列表分區(qū)，分區(qū)鍵必須包含單個列或表達式。范圍和列表分區(qū)需要 btree 運算符類，而哈希分區(qū)需要哈希運算符類。如果未顯式指定運算符類，則將使用相應(yīng)類型的默認運算符類;如果不存在默認運算符類，則會引發(fā)錯誤。使用哈希分區(qū)時，使用的運算符類必須實現(xiàn)支持函數(shù)。分區(qū)表被劃分為子表（稱為分區(qū)），這些子表是使用單獨的命令創(chuàng)建的。分區(qū)表本身是空的。插入到表中的數(shù)據(jù)行將根據(jù)分區(qū)鍵中的列或表達式的值路由到分區(qū)。如果沒有現(xiàn)有分區(qū)與新行中的值匹配，則會報告錯誤。CREATE TABLE 分區(qū)表不支持約束;但是，您可以在單個分區(qū)上定義這些約束。
PARTITION OF parent_table { FOR VALUES partition_bound_spec / DEFAULT }		將表創(chuàng)建為指定父表的分區(qū)?？梢允褂?將表創(chuàng)建為特定值的分區(qū)，也可以使用創(chuàng)建為默認分區(qū)。父表中存在的任何索引、約束和用戶定義的行級觸發(fā)器都將在新分區(qū)上克隆。 partition_bound_spec必須與父表的分區(qū)方法和分區(qū)鍵相對應(yīng)，并且不得與該父表的任何現(xiàn)有分區(qū)重疊。表單 with 用于列表分區(qū)，表單 with 用于范圍分區(qū)，表單 with 用于哈希分區(qū)。 partition_bound_expr 是任何無變量的表達式（不允許使用子查詢、窗口函數(shù)、聚合函數(shù)和集合返回函數(shù)）。其數(shù)據(jù)類型必須與相應(yīng)分區(qū)鍵列的數(shù)據(jù)類型匹配。表達式在表創(chuàng)建時計算一次，因此它甚至可以包含可變表達式，例如 .CURRENT_TIMESTAMP 創(chuàng)建列表分區(qū)時，可以指定該分區(qū)以表示該分區(qū)允許分區(qū)鍵列為 null。但是，對于給定的父表，不能有多個這樣的列表分區(qū)。不能為范圍分區(qū)指定。創(chuàng)建范圍分區(qū)時，指定的下限是非獨占邊界，而指定的上限是獨占邊界。也就是說，列表中指定的值是此分區(qū)的相應(yīng)分區(qū)鍵列的有效值，而列表中的值則不是。請注意，必須根據(jù)逐行比較規(guī)則來理解此語句（第 9.24.5 節(jié)）。例如，給定，分區(qū)綁定允許 any 、 any 非 null 和 any .FROMTOFROMTOPARTITION BY RANGE (x,y)FROM (1, 2) TO (3, 4)x=1y>=2x=2yx=3y<4 在創(chuàng)建范圍分區(qū)時，可以使用特殊值和來指示列的值沒有下限或上限。例如，使用定義的分區(qū)允許任何小于 10 的值，而使用定義的分區(qū)允許任何大于或等于 10 的值。MINVALUEMAXVALUEFROM (MINVALUE) TO (10)FROM (10) TO (MAXVALUE) 創(chuàng)建涉及多列的范圍分區(qū)時，將其用作下限的一部分和上限的一部分也是有意義的。例如，使用定義的分區(qū)允許第一個分區(qū)鍵列大于 0 且小于或等于 10 的任何行。同樣，使用定義的分區(qū)允許第一個分區(qū)鍵列以“a”開頭的任何行。MAXVALUEMINVALUEFROM (0, MAXVALUE) TO (10, MAXVALUE)FROM (‘a’, MINVALUE) TO (‘b’, MINVALUE) 請注意，如果 or 用于分區(qū)邊界的一列，則必須對所有后續(xù)列使用相同的值。例如，不是有效的邊界;你應(yīng)該寫.MINVALUEMAXVALUE(10, MINVALUE, 0)(10, MINVALUE, MINVALUE)另請注意，某些元素類型（例如）具有“無窮大”的概念，這只是另一個可以存儲的值。這與和不同，它們不是可以存儲的真實值，而是表示值是無限的。可以認為大于任何其他值，包括“無窮大”，也小于任何其他值，包括“減去無窮大”。因此，該范圍不是空范圍;它只允許存儲一個值——“無窮大”。br>timestampMINVALUEMAXVALUEMAXVALUEMINVALUEFROM (‘infinity’) TO (MAXVALUE) 如果指定，則該表將創(chuàng)建為父表的默認分區(qū)。此選項不適用于哈希分區(qū)表。不適合給定父級的任何其他分區(qū)的分區(qū)鍵值將被路由到默認分區(qū)。當表具有現(xiàn)有分區(qū)并向其添加新分區(qū)時，必須掃描默認分區(qū)以驗證它是否不包含任何正確屬于新分區(qū)的行。如果默認分區(qū)包含大量行，則此操作可能會很慢。如果默認分區(qū)是外表，或者它有一個約束，證明它不能包含應(yīng)該放在新分區(qū)中的行，則將跳過掃描。創(chuàng)建哈希分區(qū)時，必須指定模數(shù)和余數(shù)。模數(shù)必須為正整數(shù)，余數(shù)必須為小于模數(shù)的非負整數(shù)。通常，在最初設(shè)置哈希分區(qū)表時，應(yīng)選擇與分區(qū)數(shù)相等的模數(shù)，并為每個表分配相同的模數(shù)和不同的余數(shù)（請參閱下面的示例）。但是，并不要求每個分區(qū)都具有相同的模數(shù)，只是要求哈希分區(qū)表的分區(qū)之間出現(xiàn)的每個模數(shù)都是下一個較大模數(shù)的因子。這允許以增量方式增加分區(qū)數(shù)，而無需一次移動所有數(shù)據(jù)。例如，假設(shè)您有一個具有 8 個分區(qū)的哈希分區(qū)表，每個分區(qū)的模數(shù)為 8，但發(fā)現(xiàn)有必要將分區(qū)數(shù)增加到 16。您可以分離其中一個模數(shù) 8 分區(qū)，創(chuàng)建兩個新的模數(shù) 16 分區(qū)，覆蓋鍵空間的相同部分（一個分區(qū)的余數(shù)等于分離分區(qū)的余數(shù)，另一個分區(qū)的余數(shù)等于該值加 8），然后用數(shù)據(jù)重新填充它們。然后，您可以對每個模數(shù) 8 分區(qū)重復(fù)此操作（也許稍后），直到?jīng)]有剩余的分區(qū)。雖然這可能仍然涉及每個步驟的大量數(shù)據(jù)移動，但仍然比必須創(chuàng)建一個全新的表并一次移動所有數(shù)據(jù)要好。分區(qū)必須具有與其所屬的分區(qū)表相同的列名和類型。對分區(qū)表的列名或類型的修改將自動傳播到所有分區(qū)。每個分區(qū)都會自動繼承約束，但單個分區(qū)可以指定其他約束;與父約束具有相同名稱和條件的其他約束將與父約束合并?？梢詾槊總€分區(qū)單獨指定默認值。但請注意，在分區(qū)表中插入元組時，不應(yīng)用分區(qū)的默認值。插入到分區(qū)表中的行將自動路由到正確的分區(qū)。如果不存在合適的分區(qū)，則會發(fā)生錯誤。通常影響表及其所有繼承子級的操作將級聯(lián)到所有分區(qū)，但也可以在單個分區(qū)上執(zhí)行。請注意，使用創(chuàng)建分區(qū)需要對父分區(qū)表進行鎖定。同樣，刪除分區(qū)需要對父表進行鎖定。可以使用 ALTER TABLE ATTACH/DETACH PARTITION 在較弱的鎖下執(zhí)行這些操作，從而減少對分區(qū)表上的并發(fā)操作的干擾。PARTITION OFACCESS EXCLUSIVEDROP TABLEACCESS EXCLUSIVE
LIKE source_table [ like_option … ]		該子句指定一個表，新表會自動從中復(fù)制所有列名、其數(shù)據(jù)類型及其非 null 約束。與此不同的是，新表和原始表在創(chuàng)建完成后是完全解耦的。對原始表的更改不會應(yīng)用于新表，并且無法在原始表的掃描中包含新表的數(shù)據(jù)。此外，復(fù)制的列和約束不會與名稱相似的列和約束合并。如果顯式指定了相同的名稱或在另一個子句中指定了相同的名稱，則會發(fā)出錯誤信號。可選的 like_option 子句指定要復(fù)制的原始表的哪些附加屬性。指定將復(fù)制該屬性，指定將省略該屬性。是默認值。如果為同一類型的對象制作了多個規(guī)范，則使用最后一個規(guī)范。
	INCLUDING COMMENTS	將復(fù)制復(fù)制的列、約束和索引的注釋。默認行為是排除注釋，這會導(dǎo)致新表中復(fù)制的列和約束沒有注釋。
	INCLUDING COMPRESSION	將復(fù)制列的壓縮方法。默認行為是排除壓縮方法，導(dǎo)致列具有默認壓縮方法。
	INCLUDING CONSTRAINTS	CHECK約束將被復(fù)制。列約束和表約束之間沒有區(qū)別。非空約束總是被復(fù)制到新表中。
	INCLUDING DEFAULTS	復(fù)制的列定義的默認表達式將被復(fù)制。否則，不會復(fù)制默認表達式，從而導(dǎo)致新表中復(fù)制的列的默認值為空。注意，復(fù)制調(diào)用數(shù)據(jù)庫修改函數(shù)的默認值，例如，可能會在原始表和新表之間創(chuàng)建功能鏈接
	INCLUDING GENERATED	復(fù)制的列定義的任何生成表達式都將被復(fù)制。默認情況下，新列將是常規(guī)基列。
	INCLUDING IDENTITY	復(fù)制的列定義的任何標識規(guī)范都將被復(fù)制。為新表的每個標識列創(chuàng)建一個新的序列，與與舊表關(guān)聯(lián)的序列分開。
	INCLUDING INDEXES	原表上的索引、、和約束將在新表上創(chuàng)建。根據(jù)默認規(guī)則選擇新索引和約束的名稱，而不管原始索引和約束是如何命名的。(這種行為避免了新索引可能出現(xiàn)的重復(fù)名稱失敗。)主要KEY UNIQUE EXCLUDE
	INCLUDING STATISTICS	擴展統(tǒng)計信息被復(fù)制到新表中。
	INCLUDING STORAGE	復(fù)制的列定義的存儲設(shè)置將被復(fù)制。默認行為是排除設(shè)置，導(dǎo)致新表中復(fù)制的列具有特定于類型的默認設(shè)置。
	INCLUDING ALL	包括所有是選擇所有可用的單個選項的縮寫形式。(在選擇除某些特定選項外的所有選項后編寫單獨的子句可能會很有用。) EXCLUDING INCLUDING 所有該子句還可用于從視圖、外表或復(fù)合類型復(fù)制列定義。不適用的選項(例如，從視圖)被忽略。LIKE INCLUDING 索引
CONSTRAINT constraint_name		列或表約束的可選名稱。如果違反了約束，則會在錯誤消息中顯示約束名稱，因此可以使用約束名稱向客戶機應(yīng)用程序傳遞有用的約束信息。(需要雙引號來指定包含空格的約束名稱。)如果不指定約束名稱，系統(tǒng)將生成一個名稱。冷必須是正的
NOT NULL		列不允許包含空值。
NULL		列允許包含空值。這是默認值。
CHECK ( expression ) [ NO INHERIT ]		子句指定一個表達式，該表達式產(chǎn)生一個布爾結(jié)果，新行或更新行必須滿足該結(jié)果，插入或更新操作才能成功。求值為TRUE或UNKNOWN的表達式成功。如果插入或更新操作的任何一行產(chǎn)生FALSE結(jié)果，則會引發(fā)錯誤異常，并且插入或更新不會更改數(shù)據(jù)庫。指定為列約束的檢查約束應(yīng)該只引用該列的值，而出現(xiàn)在表約束中的表達式可以引用多個列。當一個表有多個約束時，在檢查約束之后，將按照名稱的字母順序?qū)γ恳恍羞M行測試。
DEFAULT default_expr		該子句為其列定義出現(xiàn)的列分配一個默認數(shù)據(jù)值。該值是任何與變量無關(guān)的表達式(特別是，不允許交叉引用當前表中的其他列)。子查詢也不允許。默認表達式的數(shù)據(jù)類型必須與列的數(shù)據(jù)類型匹配。默認表達式將用于未為列指定值的任何插入操作。如果列沒有默認值，則默認值為空。
GENERATED ALWAYS AS ( generation_expr ) STORED		該子句將該列創(chuàng)建為生成的列。不能寫入該列，讀取時將返回指定表達式的結(jié)果。需要這個關(guān)鍵字來表示該列將在寫時計算，并將存儲在磁盤上。生成表達式可以引用表中的其他列，但不能引用其他生成的列。使用的任何函數(shù)和操作符都必須是不可變的。不允許引用其他表。
GENERATED { ALWAYS / BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]		該子句將列創(chuàng)建為標識列。它將有一個隱式序列附加到它，并且新行的列將自動具有分配給它的序列中的值。這樣的列是隱式的。子句和確定在和命令中如何顯式地處理用戶指定的值。在命令中，如果選中if，則只有在語句指定的情況下才接受用戶指定的值。如果選中，則優(yōu)先使用用戶指定的值。有關(guān)詳細信息，請參見INSERT。(在該命令中，無論如何設(shè)置，都將使用用戶指定的值。)默認情況下，在覆蓋系統(tǒng)值時插入總路徑在命令中，如果選擇了該選項，則將拒絕將列更新為除該值之外的任何值。選中后，可以正常更新列。(該命令沒有子句。) 可選的sequence_options子句可用于覆蓋序列的選項。
UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint) UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint) #		約束指定表的一個或多個列組成的組只能包含唯一值。唯一表約束的行為與唯一列約束的行為相同，并具有跨多個列的額外功能。因此，約束強制任何兩行必須在這些列中的至少一個列中不同。為了惟一約束的目的，空值不被認為是相等的，除非指定。null不可區(qū)分每個唯一約束應(yīng)該命名一組列，這些列與為表定義的任何其他唯一鍵約束或主鍵約束命名的列不同。(否則，多余的唯一約束將被丟棄。) 在為多級分區(qū)層次結(jié)構(gòu)建立唯一約束時，目標分區(qū)表的分區(qū)鍵中的所有列以及它的所有后代分區(qū)表的分區(qū)鍵中的列都必須包含在約束定義中。添加唯一約束將自動在約束中使用的列或列組上創(chuàng)建唯一的b樹索引。可選子句將一個或多個列添加到該索引中，這些列只是“有效負載”:對它們不強制惟一性，并且不能基于這些列搜索索引。但是，它們可以通過僅索引掃描來檢索。請注意，盡管沒有對包含的列強制執(zhí)行約束，但它仍然依賴于它們。因此，對這些列(例如)的某些操作可能會導(dǎo)致級聯(lián)約束和索引刪除。
PRIMARY KEY (column constraint) PRIMARY KEY ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint)		約束指定表的一個或多個列只能包含唯一(非重復(fù))、非空值。一個表只能指定一個主鍵，無論是作為列約束還是表約束。主鍵約束應(yīng)該命名一組列，這些列與為同一表定義的任何唯一約束命名的列不同。(否則，唯一約束是多余的，將被丟棄。) PRIMARY KEY強制與的組合相同的數(shù)據(jù)約束。但是，將一組列標識為主鍵還提供了關(guān)于模式設(shè)計的元數(shù)據(jù)，因為主鍵意味著其他表可以依賴這組列作為行的唯一標識符。UNIQUE NOT 0 當將約束放置在分區(qū)表中時，約束將共享前面描述的約束。添加約束將自動在約束中使用的列或列組上創(chuàng)建唯一的b-tree索引。可選子句將一個或多個列添加到該索引中，這些列只是“有效負載”:對它們不強制惟一性，并且不能基于這些列搜索索引。但是，它們可以通過僅索引掃描來檢索。請注意，盡管沒有對包含的列強制執(zhí)行約束，但它仍然依賴于它們。因此，對這些列(例如)的某些操作可能會導(dǎo)致級聯(lián)約束和索引刪除
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, … ] ) index_parameters [ WHERE ( predicate ) ]		子句定義了一個排除約束，它保證如果使用指定的操作符在指定的列或表達式上比較任意兩行，則不是所有這些比較都將返回。如果所有指定的操作符都測試是否相等，則這相當于一個約束，盡管普通的唯一約束會更快。但是，排除約束可以指定比簡單相等更一般的約束。排除約束是使用索引實現(xiàn)的，因此每個指定的操作符必須與索引訪問方法index_method的適當操作符類相關(guān)聯(lián)。運算符必須是可交換的。每個exclude_element都可以指定操作符類和/或排序選項; 目前這意味著不能使用GIN。盡管這是允許的，但在排除約束下使用b樹或哈希索引沒有什么意義，因為這沒有普通唯一約束做得更好的事情。謂詞允許您在表的子集上指定排除約束;這在內(nèi)部創(chuàng)建了一個部分索引。注意，謂語周圍需要括號。
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)		這些子句指定了一個外鍵約束，該約束要求新表的一組或多列必須只包含與被引用表的某些行的引用列中的值匹配的值。如果省略refcolumn列表，則使用reftable的主鍵。引用的列必須是被引用表中不可延遲的唯一鍵約束或主鍵約束的列。用戶必須對被引用的表(整個表或特定的引用列)具有權(quán)限。添加外鍵約束需要在引用表上加一個鎖。注意，外鍵約束不能在臨時表和永久表之間定義。插入到引用列中的值將使用給定的匹配類型與引用表和引用列的值進行匹配。有三種匹配類型:、和(這是默認的)。不允許多列外鍵中的一列為空，除非所有外鍵列都為空;如果它們都為空，則不要求行在引用表中有匹配。允許任何外鍵列為空;如果它們中的任何一個為空，則不需要在引用表中有匹配。尚未實現(xiàn)。(當然，可以將約束應(yīng)用于引用列，以防止出現(xiàn)這些情況。)匹配完整匹配部分匹配simplematch完整匹配簡單匹配部分不為空此外，當引用列中的數(shù)據(jù)發(fā)生更改時，將對該表列中的數(shù)據(jù)執(zhí)行某些操作。子句指定當被引用表中的被引用行被刪除時要執(zhí)行的操作。同樣，該子句指定當被引用表中的被引用列被更新為新值時要執(zhí)行的操作。如果行更新了，但引用的列實際上沒有更改，則不執(zhí)行任何操作。不能延遲檢查以外的引用操作，即使將約束聲明為可延遲的。每個子句有以下可能的操作:ON DELETEON UPDATENO ACTION
	NO ACTION	產(chǎn)生一個錯誤，指出刪除或更新將創(chuàng)建一個違反外鍵約束的錯誤。如果延遲約束，如果仍然存在任何引用行，則在約束檢查時將產(chǎn)生此錯誤。這是默認操作。
	RESTRICT	產(chǎn)生一個錯誤，指出刪除或更新將創(chuàng)建一個違反外鍵約束的錯誤。除了不可延期外，這與except相同。不采取行動
	CASCADE	刪除引用已刪除行的所有行，或者分別將引用列的值更新為被引用列的新值。
	SET NULL [ ( column_name [, … ] ) ]	將所有引用列或引用列的指定子集設(shè)置為空。列的子集只能為操作指定。在刪除
	SET DEFAULT [ ( column_name [, … ] ) ]	將所有引用列或引用列的指定子集設(shè)置為其默認值。列的子集只能為操作指定。(如果默認值不為空，則引用表中必須有一行與默認值匹配，否則操作將失敗。)
DEFERRABLE NOT DEFERRABLE		這控制是否可以延遲約束。不可延遲的約束將在每個命令之后立即檢查。對可延遲約束的檢查可以推遲到事務(wù)結(jié)束(使用SET constraints命令)。是默認值。目前，只有、、和(外鍵)約束接受此子句。約束是不可推遲的。請注意，可延遲約束不能在包含子句的語句中用作沖突仲裁器。
INITIALLY IMMEDIATE INITIALLY DEFERRED		如果約束是可延遲的，則此子句指定檢查約束的默認時間。如果約束是，則在每個語句之后檢查它。這是默認值。如果約束是，則僅在事務(wù)結(jié)束時檢查它?？梢允褂肧ET CONSTRAINTS命令更改約束檢查時間。默認是立即檢查或者是延遲檢查
USING method		這個可選子句指定用于存儲新表內(nèi)容的表訪問方法;該方法需要是類型的訪問方法。
WITH ( storage_parameter [= value] [, … ] )		此子句為表或索引指定可選的存儲參數(shù);為了向后兼容，表的子句還可以包括指定新表的行不應(yīng)包含oid(對象標識符)的子句，不再支持。WITHOIDS = FALSEOIDS = TRUE
WITHOUT OIDS		這是向后兼容的聲明表語法，不再支持創(chuàng)建表。without oid，with oid
ON COMMIT		臨時表在事務(wù)塊末尾的行為可以使用。ON COMMIT有三個選項
	PRESERVE ROWS	在事務(wù)結(jié)束時不采取任何特殊操作。這是默認行為。
	DELETE ROWS	臨時表中的所有行將在每個事務(wù)塊結(jié)束時被刪除。實際上，每次提交時都會自動執(zhí)行TRUNCATE。在分區(qū)表上使用時，不會級聯(lián)到它的分區(qū)。
	DROP	臨時表將在當前事務(wù)塊結(jié)束時被刪除。在分區(qū)表上使用此操作時，將刪除其分區(qū);在具有繼承子表上使用此操作時，將刪除依賴子表。
TABLESPACE tablespace_name	tablespace_name是要創(chuàng)建新表的表空間的名稱。如果未指定，則查詢default_tablespace，如果是臨時表，則查詢temp_tablespaces。對于分區(qū)表，由于表本身不需要存儲，所以當沒有顯式指定其他表空間時，指定的表空間將作為默認表空間覆蓋任何新創(chuàng)建的分區(qū)
USING INDEX TABLESPACE	該子句允許選擇創(chuàng)建與、或約束關(guān)聯(lián)的索引的表空間。如果未指定，則查詢default_tablespace，如果是臨時表，則查詢temp_tablespaces。

總結(jié)

到此這篇關(guān)于PostgreSQL表操作之表的創(chuàng)建及表基礎(chǔ)語法的文章就介紹到這了,更多相關(guān)PostgreSQL表創(chuàng)建及基礎(chǔ)語法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: