开发者

PostgreSQL表操作之表的创建及表基础语法总结

开发者 https://www.devze.com 2024-08-10 12:42 出处：网络 作者： DBA圈小圈
目录PostgreSQL表类型创建表Create Table命令：修改表命令 Alter Table命令删除表命令命令查看表列表命令搜索表命令查看表详情命令参数总结 PostgreSQL表类型
相关专题：pg数据库查看所有表postgresql表创建postgresql表基础语法
目录
  • PostgreSQL表类型
  • 创建表Create Table
    • 命令：
  • 修改表命令 Alter Table
    • 命令
  • 删除表命令
    • 命令
    • 查看表列表命令
  • 搜索表
    • 命令
  • 查看表详情
    • 命令
  • 参数
    • 总结 

      PostgreSQL表类型

      PostgreSQL支持以下几种表类型：

      • 普通表（Regular Table）：这是最常用的表类型，用于存储数据。
      • 临时表（Temporary Table）：这些表只在当前会话中存在，并在会话结束后自动删除。临时表通常用于存储中间结果或临时数据。
      • 视图（View）：视图是一种虚拟的表，它是通过查询已存在的表或其他视图来创建的。视图提供了一种简化数据访问的方式，并可以用作数据的过滤器。
      • 外部表（External Table）：这些表实际上并不存储数据，而是与外部数据源（如文件或远程数据库）建立连接，以便读取和查询数据。
      • 分区表（Partitioned Table）：分区表将数据划分为多个较小的子表，每个子表称为一个分区。每个分区可以独立查询和管理，从而提高查询性能。
      • 显式锁定表（Explicitly Locked Table）：这种类型的表在执行某些操作时会被锁定，以防止其他会话对其进行读取或修改。锁定表用于处理并发访问的问题。
      • 复制表（Replicated Table）：复制表是通过将数据复制到多个节点来提供数据冗余和高可用性的表。

      这些表类型提供了不同的功能和用途，可以根据具体需求选择合适的表类型来存储和管理数据。

      创建表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 | ID编程客栈ENTITY | 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 ]http://www.devze.com [ 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 
----php-----------+---------+-----------+----------+---------
 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=#

      参数

      参数子参数说明
      TEMPORARY or TEMP如果指定，则该表将创建为临时表。临时表在会话结束时自动删除，或者在当前事务结束时自动删除（见下文）。默认search_path首先包括临时架构，因此，当临时表存在时，不会为新计划选择名称相同的现有永久表，除非使用架构限定的名称引用它们。在临时表上创建的任何索引也自动成为临时索引。ON COMMIT

      autovacuum 守护程序无法访问，因此无法对临时表进行清空或分析。因此，应通过会话 SQL 命令执行适当的真空和分析操作。例如，如果要在复杂查询中使用临时表，则最好在填充临时表后对临时表运行该表。ANALYZE

      可选，or 可以写在 或 之前。这目前在 PostgreSQL 中没有区别，并且已被弃用;请参阅下面的兼容性。GLOBAL \ LOCAL TEMP OR ARYTEMP

      UNLOGGED如果指定，则该表将创建为未记录的表。写入未记录表的数据不会写入预写日志，这使得它们比普通表快得多。但是，它们不是崩溃安全的：在崩溃或不干净的关闭后，未记录的表会自动截断。未记录表的内容也不会复制到备用服务器。在未记录的表上创建的任何索引也会自动取消记录。

      如果指定此项，则与未记录表（对于标识或序列列）一起创建的任何序列也会创建为未记录。

      IF NOT EXISTS如果已存在同名关系，则不要引发错误。在这种情况下，将发出通知。请注意，不能保证现有关系与将要创建的关系类似。
      table_name要创建的表的名称（可指定架构Schema）。
      OF type_name创建一个类型化表，该表从指定的复合类型（可选地称为架构限定）中获取其结构。类型化表与其类型相关联;例如，如果删除类型 （with ）。DROP TYPE … CASCADE

      创建类型化表时，列的数据类型由基础复合类型确定，而不是由命令指定。但该命令可以向表添加默认值和约束，并可以指定存储参数。CREATE TABLE

      column_name要在新表中创建的列的名称。
      data_type列的数据类型。这可以包括数组说明符。
      COLLATE collation该子句为列分配排序规则（该列必须是可合并的数据类型）。如果未指定，则使用列数据类型的默认排序规则。
      STORAGE { PLAIN / EXTERNAL / EXTENDED / MAIN / DEFAULT }此窗体设置列的存储模式。这将控制此列是内联保存还是在辅助 TOAST 表中保存，以及是否应压缩数据。 必须用于固定长度的值。如inline, uncompressed. is for inline, compressible。用于外部未压缩数据，也用于外部压缩数据。写入将存储模式设置为列数据类型的默认模式。 是大多数支持非存储的数据类型的默认值。使用 将使子字符串操作变得非常大，并且值运行得更快，但代价是增加了存储空间。
      COMPRESSION compression_method该子句设置列的压缩方法。压缩仅支持可变宽度数据类型，并且仅当列的存储模式为 或 时才使用压缩。
      INHERITS ( parent_table [, … ] )可选子句指定一个新表自动从中继承所有列的表列表。父表可以是普通表，也可以是外表。

      使用 of 会在新的子表与其父表之间创建持久关系。对父表的架构修改通常也会传播到子表，默认情况下，子表的数据包含在父表的扫描中。

      如果多个父表中存在相同的列名，则会报告错误，除非每个父表中的列的数据类型匹配。如果没有冲突，则将合并重复的列以在新表中形成单个列。如果新表的列名列表包含也继承的列名，则数据类型也必须与继承的列匹配，并且列定义将合并为一个列。如果新表显式指定了列的默认值，则此默认值将覆盖从该列的继承声明中继承的任何默认值。否则，任何为列指定默认值的父级都必须全部指定相同的默认值，否则将报告错误。

      CHECK约束的合并方式与列的合并方式基本相同：如果多个父表和/或新表定义包含名称相同的约束，则这些约束必须具有相同的检查表达式，否则将报告错误。具有相同名称和表达式的约束将合并到一个副本中。在父项中标记的约束将不予考虑。请注意，新表中的未命名约束永远不会合并，因为将始终为其选择唯一名称，CHECKNO INHERITCHECK。列设置也是从父表复制的（STORAGE）。

      如果父表中的列是标识列，则不会继承该属性。如果需要，可以将子表中的列声明为标识列。

      PARTITION BY { RANGE / LIST / HASH } ( { column_name / ( expression ) } [ opclass ] [, …] )可选子句指定对表进行分区的策略。这样创建的表称为分区表。带括号的列或表达式列表构成表的分区键。使用范围或哈希分区时，分区键可以包含多个列或表达式（最多 32 个，但在构建 PostgreSQL 时可以更改此限制），但对于列表分区，分区键必须包含单个列或表达式。

      范围和列表分区需要 btree 运算符类，而哈希分区需要哈希运算符类。如果未显式指定运算符类，则将使用相应类型的默认运算符类;如果不存在默认运算符类，则会引发错误。使用哈希分区时，使用的运算符类必须实现支持函数。

      分区表被划分为子表（称为分区），这些子表是使用单独的命令创建的。分区表本身是空的。插入到表中的数据行将根据分区键中的列或表达式的值路由到分区。如果没有现有分区与新行中的值匹配，则会报告错误。CREATE TABLE

      分区表不支持约束;但是，您可以在单个分区上定义这些约束。

      PARTITION OF parent_table { FOR VALUES partition_bound_spec / DEFAULT }将表创建为指定父表的分区。可以使用 将表创建为特定值的分区，也可以使用 创建为默认分区。父表中存在的任何索引、约束和用户定义的行级触发器都将在新分区上克隆。

      partition_bound_spec必须与父表的分区方法和分区键相对应，并且不得与该父表的任何现有分区重叠。表单 with 用于列表分区，表单 with 用于范围分区，表单 with 用于哈希分区。

      partition_bound_expr 是任何无变量的表达式（不允许使用子查询、窗口函数、聚合函数和集合返回函数）。其数据类型必须与相应分区键列的数据类型匹配。表达式在表创建时计算一次，因此它甚至可以包含可变表达式，例如 .CURRENT_TIMESTAMP

      创建列表分区时，可以指定该分区以表示该分区允许分区键列为 null。但是，对于给定的父表，不能有多个这样的列表分区。 不能为范围分区指定。

      创建范围分区时，指定的下限是非独占边界，而指定的上限是独占边界。也就是说，列表中指定的值是此分区的相应分区键列的有效值，而列表中的值则不是。请注意，必须根据逐行比较规则来理解此语句（第 9.24.5 节）。例如，给定 ，分区绑定允许 any 、 any 非 null 和 any .FROMTOFROMTOPARTITION BY RANGE (x,y)FROM (1, 2) TO (3, 4)x=1y>=2x=2yx=3y<4

      在创建范围分区时，可以使用特殊值 和 来指示列的值没有下限或上限。例如，使用 定义的分区允许任何小于 10 的值，而使用 定义的分区允许任何大于或等于 10 的值。MINVALUEMAXVALUEFROM (MINVALUE) TO (10)FROM (10) TO (MAXVALUE)

      创建涉及多列的范围分区时，将其用作下限的一部分和上限的一部分也是有意义的。例如，使用定义的分区允许第一个分区键列大于 0 且小于或等于 10 的任何行。同样，使用 定义的分区允许第一个分区键列以“a”开头的任何行。MAXVALUEMINVALUEFROM (0, MAXVALUE) TO (10, MAXVALUE)FROM (‘a’, MINVALUE) TO (‘b’, MINVALUE)

      请注意，如果 or 用于分区边界的一列，则必须对所有后续列使用相同的值。例如，不是有效的边界;你应该写.MINVALUEMAXVALUE(10, MINVALUE, 0)(10, MINVALUE, MINVALUE)另请注意，某些元素类型（例如 ）具有“无穷大”的概念，这只是另一个可以存储的值。这与 和 不同，它们不是可以存储的真实值，而是表示值是无限的。 可以认为大于任何其他值，包括“无穷大”，也小于任何其他值，包括“减去无穷大”。因此，该范围不是空范围;它只允许存储一个值——“无穷大”。br>timestampMINVALUEMAXVALUEMAXVALUEMINVALUEFROM (‘infinity’) TO (MAXVALUE)

      如果指定，则该表将创建为父表的默认分区。此选项不适用于哈希分区表。不适合给定父级的任何其他分区的分区键值将被路由到默认分区。

      当表具有现有分区并向其添加新分区时，必须扫描默认分区以验证它是否不包含任何正确属于新分区的行。如果默认分区包含大量行，则此操作可能会很慢。如果默认分区是外表，或者它有一个约束，证明它不能包含应该放在新分区中的行，则将跳过扫描。

      创建哈希分区时，必须指定模数和余数。模数必须为正整数，余数必须为小于模数的非负整数。通常，在最初设置哈希分区表时，应选择与分区数相等的模数，并为每个表分配相同的模数和不同的余数（请参阅下面的示例）。但是，并不要求每个分区都具有相同的模数，只是要求哈希分区表的分区之间出现的每个模数都是下一个较大模数的因子。这允许以增量方式增加分区数，而无需一次移动所有数据。例如，假设您有一个具有 8 个分区的哈希分区表，每个分区的模数为 8，但发现有必要将分区数增加到 16。您可以分离其中一个模数 8 分区，创建两个新的模数 16 分区，覆盖键空间的相同部分（一个分区的余数等于分离分区的余数，另一个分区的余数等于该值加 8），然后用数据重新填充它们。然后，您可以对每个模数 8 分区重复此操作（也许稍后），直到没有剩余的分区。虽然这可能仍然涉及每个步骤的大量数据移动，但仍然比必须创建一个全新的表并一次移动所有数据要好。分区必须具有与其所属的分区表相同的列名和类型。对分区表的列名或类型的修改将自动传播到所有分区。 每个分区都会自动继承约束，但单个分区可以指定其他约束;与父约束具有相同名称和条件的其他约束将与父约束合并。可以为每个分区单独指定默认值。但请注意，在分区表中插入元组时，不应用分区的默认值。

      插入到分区表中的行将自动路由到正确的分区。如果不存在合适的分区，则会发生错误。通常影响表及其所有继承子级的操作将级联到所有分区，但也可以在单个分区上执行。

      请注意，使用 创建分区需要对父分区表进行锁定。同样，删除分区需要对父表进行锁定。可以使用 ALTER TABLE ATTACH/DETACH PARTITION 在较弱的锁下执行这些操作，从而减少对分区表上的并发操作的干扰。PARTITION OFACCESS EXCLUSIVEDROP TABLEACCESS EXCLUSIVE

      LIKE source_table [ like_option … ]该子句指定一个表，新表会自动从中复制所有列名、其数据类型及其非 null 约束。

      与此不同的是，新表和原始表在创建完成后是完全解耦的。对原始表的更改不会应用于新表，并且无法在原始表的扫描中包含新表的数据。

      此外，复制的列和约束不会与名称相似的列和约束合并。如果显式指定了相同的名称或在另一个子句中指定了相同的名称，则会发出错误信号。

      可选的 like_option 子句指定要复制的原始表的哪些附加属性。指定将复制该属性，指定将省略该属性。 是默认值。如果为同一类型的对象制作了多个规范，则使用最后一个规范。

      INCLUDING COMMENTS将复制复制的列、约束和索引的注释。默认行为是排除注释，这会导致新表中复制的列和约束没有注释。
      INCLUDING COMPRESSION将复制列的压缩方法。默认行为是排除压缩方法，导致列具有默认压缩方法。
      INCLUDING CONSTRAINTSCHECK约束将被复制。列约束和表约束之间没有区别。非空约束总是被复制到新表中。
      INCLUDING DEFAULTS复制的列定义的默认表达式将被复制。否则，不会复制默认表达式，从而导致新表中复制的列的默认值为空。注意，复制调用数据库修改函数的默认值，例如，可能会在原始表和新表之间创建功能链接
      INCLUDING GENERATED复制的列定义的任何生成表达式都将被复制。默认情况下，新列将是常规基列。
      INCLUDING IDENTITY复制的列定义的任何标识规范都将被复制。为新表的每个标识列创建一个新的序列，与与旧表关联的序列分开。
      INCLUDING INDEXES原表上的索引、、和约束将在新表上创建。根据默认规则选择新索引和约束的名称，而不管原始索引和约束是如何命名的。(这种行为避免了新索引可能出现的重复名称失败。)主要KEY UNIQUE EXCLUDE
      INCLUDING STATISTICS扩展统计信息被复制到新表中。
      INCLUDING STORAGE复制的列定义的存储设置将被复制。默认行为是排除设置，导致新表中复制的列具有特定于类型的默认设置。
      INCLUDING ALL包括所有是选择所有可用的单个选项的缩写形式。(在选择除某些特定选项外的所有选项后编写单独的子句可能会很有用。) EXCLUDING INCLUDING 所有

      该子句还可用于从视图、外表或复合类型复制列定义。不适用的选项(例如，从视图)被忽略。LIKE INCLUDING 索引

      CONSTRAINT constraint_name列或表约束的可选名称。如果违反了约束，则会在错误消息中显示约束名称，因此可以使用约束名称向客户机应用程序传递有用的约束信息。(需要双引号来指定包含空格的约束名称。)如果不指定约束名称，系统将生成一个名称。冷必须是正的
      NOT NULL列不允许包含空值。
      NULL列允许包含空值。这是默认值。
      CHECK ( expression ) [ NO INHERIT ]子句指定一个表达式，该表达式产生一个布尔结果，新行或更新行必须满足该结果，插入或更新操作才能成功。求值为TRUE或UNKNOWN的表达式成功。如果插入或更新操作的任何一行产生FALSE结果，则会引发错误异常，并且插入或更新不会更改数据库。指定为列约束的检查约束应该只引用该列的值，而出现在表约束中的表达式可以引用多个列。

      当一个表有多个约束时，在检查约束之后，将按照名称的字母顺序对每一行进行测试。

      DEFAULT default_expr该子句为其列定义出现的列分配一个默认数据值。该值是任何与变量无关的表达式(特别是，不允许交叉引用当前表中的其他列)。子查询也不允许。默认表达式的数据类型必须与列的数据类型匹配。

      默认表达式将用于未为列指定值的任何插入操作。如果列没有默认值，则默认值为空。

      GENERATED ALWAYS AS ( generation_expr ) STORED该子句将该列创建为生成的列。不能写入该列，读取时将返回指定表达式的结果。

      需要这个关键字来表示该列将在写时计算，并将存储在磁盘上。

      生成表达式可以引用表中的其他列，但不能引用其他生成的列。使用的任何函数和操作符都必须是不可变的。不允许引用其他表。

      GENERATED { ALWAYS / BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]该子句将列创建为标识列。它将有一个隐式序列附加到它，并且新行的列将自动具有分配给它的序列中的值。这样的列是隐式的。

      子句和确定在和命令中如何显式地处理用户指定的值。

      在命令中，如果选中if，则只有在语句指定的情况下才接受用户指定的值。如果选中，则优先使用用户指定的值。有关详细信息，请参见INSERT。(在该命令中，无论如何设置，都将使用用户指定的值。)默认情况下，在覆盖系统值时插入总路径

      在命令中，如果选择了该选项，则将拒绝将列更新为除该值之外的任何值。选中后，可以正常更新列。(该命令没有子句。)

      可选的sequence_options子句可用于覆盖序列的选项。

      UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint)

      UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint) #

      		约束指定表的一个或多个列组成的组只能包含唯一值。唯一表约束的行为与唯一列约束的行为相同，并具有跨多个列的额外功能。因此，约束强制任何两行必须在这些列中的至少一个列中不同。

      为了惟一约束的目的，空值不被认为是相等的，除非指定。null不可区分

      每个唯一约束应该命名一组列，这些列与为表定义的任何其他唯一键约束或主键约束命名的列不同。(否则，多余的唯一约束将被丢弃。)

      在为多级分区层次结构建立唯一约束时，目标分区表的分区键中的所有列以及它的所有后代分区表的分区键中的列都必须包含在约束定义中。

      添加唯一约束将自动在约束中使用的列或列组上创建唯一的b树索引。

      可选子句将一个或多个列添加到该索引中，这些列只是“有效负载”:对它们不强制惟一性，并且不能基于这些列搜索索引。但是，它们可以通过仅索引扫编程客栈描来检索。请注意，尽管没有对包含的列强制执行约束，但它仍然依赖于它们。因此，对这些列(例如)的某些操作可能会导致级联约束和索引删除。

      PRIMARY KEY (column constraint)

      PRIMARY KEY ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint)

      		约束指定表的一个或多个列只能包含唯一(非重复)、非空值。一个表只能指定一个主键，无论是作为列约束还是表约束。

      主键约束应该命名一组列，这些列与为同一表定义的任何唯一约束命名的列不同。(否则，唯一约束是多余的，将被丢弃。)

      PRIMARY KEY强制与的组合相同的数据约束。但是，将一组列标识为主键还提供了关于模式设计的元数据，因为主键意味着其他表可以依赖这组列作为行的唯一标识符。UNIQUE NOT 0

      当将约束放置在分区表中时，约束将共享前面描述的约束。

      添加约束将自动在约束中使用的列或列组上创建唯一的b-tree索引。

      可选子句将一个或多个列添加到该索引中，这些列只是“有效负载”:对它们不强制惟一性，并且不能基于这些列搜索索引。但是，它们可以通过仅索引扫描来检索。请注意，尽管没有对包含的列强制执行约束，但它仍然依赖于它们。因此，对这些列(例如)的某些操作可能会导致级联约束和索引删除

      EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, … ] ) index_parameters [ WHERE ( predicate ) ]子句定义了一个排除约束，它保证如果使用指定的操作符在指定的列或表达式上比较任意两行，则不是所有这些比较都将返回。如果所有指定的操作符都测试是否相等，则这相当于一个约束，尽管普通的唯一约束会更快。但是，排除约束可以指定比简单相等更一般的约束。

      排除约束是使用索引实现的，因此每个指定的操作符必须与索引访问方法index_method的适当操作符类相关联。运算符必须是可交换的。每个exclude_element都可以指定操作符类和/或排序选项;

      目前这意味着不能使用GIN。尽管这是允许的，但在排除约束下使用b树或哈希索引没有什么意义，因为这没有普通唯一约束做得更好的事情。

      谓词允许您在表的子集上指定排除约束;这在内部创建了一个部分索引。注意，谓语周围需要括号。

      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的主键。引用的列必须是被引用表中不可延迟的唯一键约束或主键约束的列。用户必须对被引用的表(整个表或特定的引用列)具有权限。添加外键约束需要在引用表上加一个锁。注意，外键约束不能在临时表和永久表之间定义。

      插入到引用列中的值将使用给定的匹配类型与引用表和引用列的值进行匹配。有三种匹配类型:、和(这是默认的)。不允许多列外键中的一列为空，除非所有外键列都为空;如果它们都为空，则不要求行在引用表中有匹配。允许任何外键列为空;如果它们中的任何一个为空，则不需要在引用表中有匹配。尚未实现。(当然，可以将约束应用于引用列，以防止出现这些情况。)匹配完整匹配部分匹配simplematch完整匹配简单匹配部分不为空

      此外，当引用列中的数据发生更改时，将对该表列中的数据执行某些操作。子句指定当被引用表中的被引用行被删除时要执行的操作。同样，该子句指定当被引用表中的被引用列被更新为新值时要执行的操作。如果行更新了，但引用的列实际上没有更改，则不执行任何操作。不能延迟检查以外的引用操作，即使将约束声明为可延迟的。每个子句有以下可能的操作:ON DELETEON UPDATENO ACTION

      NO ACTION产生一个错误，指出删除或更新将创建一个违反外键约束的错误。如果延迟约束，如果仍然存在任何引用行，则在约束检查时将产生此错误。这是默认操作。
      RESTRICT产生一个错误，指出删除或更新将创建一个违反外键约束的错误。除了不可延期外，这与except相同。不采取行动
      CASCADE删除引用已删除行的所有行，或者分别将引用列的值更新为被引用列的新值。
      SET NULL [ ( column_name [, … ] ) ]将所有引用列或引用列的指定子集设置为空。列的子集只能为操作指定。在删除
      SET DEFAULT [ ( column_name [, … ] ) ]将所有引用列或引用列的指定子集设置为其默认值。列的子集只能为操作指定。(如果默认值不为空，则引用表中必须有一行与默认值匹配，否则操作将失败。)
      DEFERRABLE

      NOT DEFERRABLE

      		这控制是否可以延迟约束。不可延迟的约束将在每个命令之后立即检查。对可延迟约束的检查可以推迟到事务结束(使用SET constraints命令)。是默认值。目前，只有、、和(外键)约束接受此子句。约束是不可推迟的。请注意，可延迟约束不能在包含子句的语句中用作冲突仲裁器。
      INITIALLY IMMEDIATE

      INITIALLY DEFERRED

      		如果约束是可延迟的，则此子句指定检查约束的默认时间。如果约束是，则在每个语句之后检查它。这是默认值。如果约束是，则仅在事务结束时检查它。可以使用SET CONSTRAINTS命令更改约束检查时间。默认是立即检查或者是延迟检查
      USING method这个可选子句指定用于存储新表内容的表访问方法;该方法需要是类型的访问方法。
      WITH ( storage_parameter [= value] [, … ] )此子句为表或索引指定可选的存储参数;为了向后兼容，表的子句还可以包括指定新表的行不应包含oid(对象标识符)的子句，不再支持。WITHOIDS = FALseoIDS = TRUE
      WITHOUT OIDS这是向后兼容的声明表语法，不再支持创建表。without oid，with oid
      ON COMMIT临时表在事务块末尾的行为可以使用。ON COMMIT有三个选项
      PRESERVE ROWS在事务结束时不采取任何特殊操作。这是默认行为。
      DELETE ROWS临时表中的所有行将在每个事务块结束时被删除。实际上，每次提交时都会自动执行TRUNCATE。在分区表上使用时，不会级联到它的分区。
      DROP临时表将在当前事务块结束时被删除。在分区表上使用此操作时，将删除其分区;在具有继承子表上使用此操作时，将删除依赖子表。
      TABLESPACE tablespace_nametablespace_name是要创建新表的表空间的名称。如果未指定，则查询default_tablespace，如果是临时表，则查询temp_tablespaces。对于分区表，由于表本身不需要存储，所以当没有显式指定其他表空间时，指定的表空间将作为默认表空间覆盖任何新创建的分区
      USING INDEX TABLESPACE该子句允许选择创建与、或约束关联的索引的表空间。如果未指定，则查询default_tablespace，如果是临时表，则查询temp_tablespaces。

      总结 

      到此这篇关于PostgreSQL表操作之表的创建及表基础语法的文章就介绍到这了,更多相关PostgreSQL表创建及基础语法内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

      0

      上一篇:

      :下一篇

      更多 数据库 相关资讯：

      精彩评论

      暂无评论...
      登录 注册
      Qedev.com
      验证码 换一张
      取 消

        关注公众号

        热门标签

        图文推荐

        Redis数据一致性详解

        Redis数据一致性详解
        基于Redis实现API接口访问次数限制

        基于Redis实现API接口访问次数限制
        RedisTemplate序列化设置的流程和具体步骤

        RedisTemplate序列化设置的流程和具体步骤
        Redis高并发缓存设计问题与性能优化

        Redis高并发缓存设计问题与性能优化
        Navicat最新版安装详细教程(超简单)

        Navicat最新版安装详细教程(超简单)
        数据库rank()分组排序函数的具体使用

        数据库rank()分组排序函数的具体使用
        数据库中row_number() 分组排序函数的具体使用

        数据库中row_number() 分组排序函数的具体使用
        单机离线部署OceanBase3.1.5详解

        单机离线部署OceanBase3.1.5详解

        开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

        法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

        Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9