postgresql 创建数据表。临时表

发布时间：2020-12-13 17:18:34 所属栏目：百科来源：网络整理

导读：CREATE TABLE Name CREATE TABLE--定义一个新表 Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ DEFAULT default_expr ] [ column_constraint [,... ] ] | table_constraint | LIKE parent_tab

CREATE TABLE

Name

CREATE TABLE--定义一个新表

Synopsis

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [,... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }  [,... ]
)
[ INHERITS ( parent_table [,... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

这里 column_constraint 可以是：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  UNIQUE [ USING INDEX TABLESPACE tablespace ] |
  PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

而 table_constraint 可以是：

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [,... ] ) [ USING INDEX TABLESPACE tablespace ] |
  PRIMARY KEY ( column_name [,... ] ) [ USING INDEX TABLESPACE tablespace ] |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [,... ] ) REFERENCES reftable [ ( refcolumn [,... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

描述

CREATE TABLE将在当前数据库创建一个新的，初始为空的表。该表将由发出此命令的用户所有。

如果给出了模式名（比如，CREATE TABLE myschema.mytable ...），那么表是在指定模式中创建的。否则它在当前模式中创建。临时表存在于一个特殊的模式里，因此创建临时表的时候不能给出模式名。表名字必需是在同一模式中其他表，序列，索引或者视图名字中唯一的。

CREATE TABLE还自动创建一个数据类型，该数据类型代表对应该表一行的复合类型。因此，表不能和同模式中的现有数据类型同名。

可选的约束子句声明约束（测试），新行或者更新的行必须满足这些约束才能成功插入或更新。约束是一个它是一个 SQL 对象，它以多种方式协助我们协助我们在表上定义有效的数值集合。

定义约束又两种方法：表约束和列约束。一个列约束是作为一个列定义的一部分定义的。而表约束并不和某个列绑在一起，它可以作用于多于一个列上。每个列约束也可以写成表约束；如果某个约束只影响一个列，那么列约束只是符号上的简洁方式而已。

参数

TEMPORARY或 TEMP

如果声明了此参数，则该表创建为临时表。临时表在会话结束时自动删除，或者是（可选）在当前事务的结尾（参阅下面的ON COMMIT）。现有同名永久表在临时表存在期间在本会话过程中是不可见的，除非它们是用模式修饰的名字引用的。任何在临时表上创建的索引也都会自动删除。

我们可以选择在TEMPORARY或TEMP前面放上GLOBAL或者LOCAL。这样对PostgreSQL没有任何区别，可以参阅兼容性.

table_name

要创建的表的名字（可以用模式修饰）。

column_name

在新表中要创建的字段名字。

data_type

该字段的数据类型。它可以包括数组说明符。有关PostgreSQL支持的数据类型的更多信息，请参考Chapter 8。

DEFAULTdefault_expr

DEFAULT子句给它所出现的字段一个缺省数值。该数值可以是任何不含变量的表达式（不允许使用子查询和对本表中的其它字段的交叉引用）。缺省表达式的数据类型必须和字段类型匹配。

缺省表达式将被用于任何未声明该字段数值的插入操作。如果字段上没有缺省值，那么缺省是 NULL。

INHERITS (parent_table[,... ] )

可选的INHERITS子句声明一列表，这个新表自动从这列表中继承所有字段。

使用INHERITS在新的子表和其父表之间创建一个永久的关系。对父表结构的修改通常也会传播到子表，缺省时，扫描父表的时候也会扫描子表。

如果在多于一个父表中存在同名的字段，那么就会报告一个错误，除非这些字段的数据类型在每个父表里都是匹配的。如果没有冲突，那么重复的字段在新表中融合成一个字段。如果新表的字段名列表中包括和继承的字段名同名的，那么它的数据类型也必须和上面一样与继承字段匹配，并且这些字段定义会融合成一个。不过，同名的继承和新字段声明可以声明不同的约束：所有的继承过来的约束以及声明的约束都融合到一起，并且全部应用于新表。如果新表为该字段明确的声明了一个缺省数值，那么此缺省数值覆盖任何来自继承字段声明的缺省值。否则，任何为该字段声明了缺省数值的父表都必须声明相同的缺省，否则就会报告一个错误。

LIKEparent_table[ { INCLUDING | EXCLUDING } DEFAULTS ]

LIKE子句声明一个表，新表自动从这个表里面继承所有字段名，他们的数据类型，以及非空约束。

和INHERITS不同，新表与原来的表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会施加到新表中，并且也不可能在扫描源表的时候包含新表的数据。

字段缺省表达式只有在声明了INCLUDING DEFAULTS之后才会包含进来。缺省是排除缺省表达式，结果是新表中所有字段缺省都是空。

WITH OIDS WITHOUT OIDS

这个可选的子句声明新表中的行是否应该拥有赋予它们的 OID （对象标识）。如果既没有声明WITH OIDS也没有声明WITHOUT OIDS，那么缺省值取决于配置参数default_with_oids。（如果新表从任何有 OID 的表继承而来，那么就算这条命令说了WITHOUT OIDS，也会强制WITH OIDS。）

如果声明或者隐含了WITHOUT OIDS，新表就不会存储 OID，并且插入数据行的时候也不会赋予 OID。通常认为这么做是值得的，因为这样可以减少 OID 消耗并且推迟 32 位 OID 计数器的回卷。一旦该计数器重叠，那么就不能再假设 OID 的唯一，这样它的实用性就大打折扣。另外，把 OID 从一个表中排除还会减少在磁盘上存储每行的空间，（在大多数机器上）每行减少 4 字节，因此也可以改进性能。

要在一个表创建之后从中删除 OID，使用ALTER TABLE。

CONSTRAINTconstraint_name

列或表约束的可选名字。如果没有声明，则由系统生成一个名字。

NOT NULL

字段不允许包含 NULL 数值。

NULL

该字段允许包含 NULL 数值。这是缺省。

这个子句的存在只是为和那些非标准 SQL 数据库兼容。我们不建议在新应用中使用它。

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

UNIQUE声明一个规则，表示一个表里的一个或者多个字段组合的分组只能包含唯一的数值。表的唯一约束的行为和列约束的一样，只不过多了跨多行的能力。

对于唯一约束的用途而言，系统认为 NULL 数值是不相等的。

每个唯一表约束都必须命名一个字段的集合，该集合必须和其它唯一约束命名字段集合或者该表定义的主键约束不同。（否则就只是同样的约束写了两次。）

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

主键约束表明表中的一个或者一些字段只能包含唯一（不重复）非 NULL 的数值。从技术上讲，PRIMARY KEY只是UNIQUE和NOT NULL的组合，不过把一套字段标识为主键同时也体现了模式设计的元数据，因为主键意味着其它表可以拿这套字段用做行的唯一标识。

一个表只能声明一个主键，不管是作为字段约束还是表约束。

主键约束应该定义在同个表上的一个与其它唯一约束所定义的不同的字段集合上。

CHECK (expression)

CHECK约束声明一个生成布尔结果的子句，一次插入或者更新操作若想成功则里面的新行或者被更新的行必须满足这个条件。值结果为真或者未知时成功。如果有任何插入或者更新的操作生成假的结果，那么都会抛出一个例外，而插入或更新动作不会影响数据库。声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

目前，CHECK表达式不能包含子查询也不能引用除当前行字段之外的变量。

REFERENCESreftable[ (refcolumn) ] [ MATCHmatchtype] [ ON DELETEaction] [ ON UPDATEaction](column constraint) FOREIGN KEY (column[,... ] ) REFERENCESreftable[ (refcolumn[,... ] ) ] [ MATCHmatchtype] [ ON DELETEaction] [ ON UPDATEaction](table constraint)

这些子句声明一个外键约束，外键约束要求新表中一列或者多列组成的组应该只包含匹配被引用的表中对应引用的字段中的数值。如果省略refcolumn，则使用reftable的主键。被引用字段必须是被引用表中的唯一字段或者主键。

向引用字段插入的数值将使用给出的匹配类型与被引用表中被引用列的数值进行匹配。有三种匹配类型：MATCH FULL，MATCH PARTIAL，和MATCH SIMPLE，它也是缺省匹配类型。MATCH FULL将不允许一个多字段外键的字段为 NULL，除非所有外键字段都为 NULL。MATCH SIMPLE允许某些外键字段为 NULL 而外键的其它部分不是 NULL。MATCH PARTIAL还没实现。

另外，当被参考字段中的数据改变的时候，那么将对本表的字段中的数据执行某种操作。ON DELETE子句声明当被参考表中的被参考行将被删除的时候要执行的操作。类似，ON UPDATE子句声明被参考表中被参考字段更新为新值的时候要执行的动作。如果该行被更新，但被参考的字段实际上没有变化，那么就不会有任何动作。除了NO ACTION检查之外的参考动作不能推迟，即使该约束声明为可以推迟的也如此。下面是每个子句的可能的动作：

NO ACTION: 生成一个错误，表明删除或者更新将产生一个违反外键约束的动作。如果该约束是可推迟的，并且如果还存在任何引用行，那么这个错误将在检查约束的时候生成。它是缺省动作。
RESTRICT: 生成一个表明删除或更新将导致违反外键约束的错误。和NO ACTION一样，只是动作不可推迟。
CASCADE: 删除任何引用了被删除行的行，或者分别把引用行的字段值更新为被参考字段的新数值。
SET NULL: 把引用行设置为 NULL。
SET DEFAULT: 把引用字段设置为它们的缺省值。

如果被参考字段经常更新，那么我们给外键字段增加一个索引可能是合适的，这样与外键字段相关联的引用动作可以更有效地执行。

DEFERRABLE NOT DEFERRABLE

这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可以推迟的约束检查可以推迟到事务结尾（使用SET CONSTRAINTS命令）。缺省是NOT DEFERRABLE。目前只有外键约束接受这个子句。所有其它约束类型都是不可推迟的。

INITIALLY IMMEDIATE INITIALLY DEFERRED

如果约束是可推迟的，那么这个子句声明检查约束的缺省时间。如果约束是INITIALLY IMMEDIATE，那么每条语句之后就检查它。这个是缺省。如果约束是INITIALLY DEFERRED，那么只有在事务结尾才检查它。约束检查的时间可以用SET CONSTRAINTS命令修改。

ON COMMIT

我们可以用ON COMMIT控制临时表在事务块结尾的行为。这三个选项是：

PRESERVE ROWS: 在事务结尾不发生任何特定的动作。这是缺省行为。
DELETE ROWS: 临时表的所有行在每次事务结尾都被删除。实际上，在每次提交的时候都自动TRUNCATE。
DROP: 在当前事务块的结尾，临时表将被删除。

TABLESPACEtablespace

tablespace是新表将要创建所在的表空间。如果没有声明，将使用default_tablespace，如果default_tablespace是空，那么将使用数据库的缺省表空间。

USING INDEX TABLESPACEtablespace

这个子句允许选择与一个UNIQUE或者PRIMARY KEY约束相关的所以创建时所在的表空间。如果没有提供这个子句，这个索引将在该表的同一个表空间中创建。

注意

我们不建议在新应用中使用 OID，可能情况下，更好的选择是使用一个SERIAL或者其它序列发生器做表的主键。如果一个应用使用了 OID 标识表中的特定行，那么我们建议在该表的oid字段上创建一个唯一约束，以确保该表的 OID 即使在计数器重叠之后也是唯一的。如果你需要一个整个数据库范围的唯一标识，那么就要避免假设 OID 是跨表唯一的，你可以用tableoid和行 OID 的组合来实现这个目的。

提示:对那些没有主键的表，我们不建议使用WITHOUT OIDS，因为如果既没有 OID 又没有唯一数据键字，那么就很难标识特定的行。

PostgreSQL自动为每个唯一约束和主键约束创建一个索引以确保唯一性。因此，我们不必为主键字段明确的创建索引。（参阅CREATE INDEX获取更多信息。）

唯一约束和主键在目前的实现里是不能继承的。这样，如果把继承和唯一约束组合在一起会导致无法运转。

一个表不能超过 1600 个字段。（实际上，实际的限制比这个更低，因为还有远祖长度限制。）

例子

创建表films和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 DEFAULT nextval('serial'),name   varchar(40) NOT NULL CHECK (name <> '')
);

创建一个带有 2 维数组的表：

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

为表films定义一个唯一表约束。唯一表约束可以在表的一个或多个字段上定义：

CREATE TABLE films (
    code        char(5),title       varchar(40),did         decimal(3),len         interval hour to minute,CONSTRAINT production UNIQUE(date_prod)
);

定义一个检查列约束：

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

定义一个检查表约束：

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

为表films定义一个主键表约束。主键表约束可以定义在表上的一个或多个字段。

CREATE TABLE films (
    code        char(5),did         integer,CONSTRAINT code_title PRIMARY KEY(code,title)
);

为表distributors定义一个主键约束。下面两个例子是等效的，第一个例子使用了表约束语法，第二个使用了列约束语法。

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

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

下面这个例子给字段name赋予了一个文本常量缺省值，并且将字段did的缺省值安排为通过选择序列对象的下一个值生成。modtime的缺省值将是该行插入的时候的时间。

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

在表distributors上定义两个NOT NULL列约束，其中之一明确给出了名字：

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

为name字段定义一个唯一约束：

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

上面的和下面这样作为一个表约束声明是一样的：

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

在表空间diskvol1里创建表cinemas：

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

兼容性

CREATE TABLE遵循 SQL-92 和 SQL:1999 的一个子集，一些例外情况在下面列出。

临时表

尽管CREATE TEMPORARY TABLE的语法和 SQL 标准的类似，但是效果是不同的。在标准里，临时表只是定义一次并且自动存在（从空内容开始）于任何需要它们的会话中。PostgreSQL要求每个会话为它们使用的每个临时表发出它们自己的CREATE TEMPORARY TABLE命令。这样就允许不同的会话将相同的临时表名字用于不同的目的，而标准的实现方法则把一个临时表名字约束为具有相同的表结构。

标准定义的临时表的行为被广泛地忽略了。PostgreSQL在这方面上的行为类似于许多其它 SQL 数据库

标准中在全局和局部地临时表之间的区别在PostgreSQL里不存在，因为这种区别取决于模块的概念，而PostgreSQL没有这个概念。出于兼容考虑，PostgreSQL将接受临时表声明中的GLOBAL和LOCAL关键字，但是他们没有作用。

临时表的ON COMMIT子句也类似于 SQL 标准，但是有些区别。如果忽略了ON COMMIT子句，SQL 声明缺省的行为是ON COMMIT DELETE ROWS。但是PostgreSQL里的缺省行为是ON COMMIT PRESERVE ROWS。在 SQL 里不存在ON COMMIT DROP。

字段检查约束

SQL 标准说CHECK字段约束只能引用他们施用的字段；只有CHECK表约束才能引用多个字段。PostgreSQL并不强制这个限制；它把字段和表约束看作相同的东西。

`NULL`"约束"

NULL"约束"（实际上不是约束）是PostgreSQL对 SQL 标准的扩展，包括它是为了和其它一些数据库系统兼容（以及为了和NOT NULL约束对称）。因为它是任何字段的缺省，所以它的出现只是噪音而已。

继承

通过INHERITS子句的多重继承是PostgreSQL语言的扩展。 SQL:1999（但不包括 SQL-92）使用不同的语法和语义定义了单继承。 SQL:1999 风格的继承还没有在PostgreSQL中实现。

对象 ID

PostgreSQL的 OID 的概念不是标准。

零行表

PostgreSQL允许创建没有字段的表（比如，CREATE TABLE foo();）。这是对 SQL 标准的扩展，标准不允许存在零字段表。零字段表本身没什么用，但是禁止他们会给ALTER TABLE DROP COLUMN带来很奇怪的情况，所以，这个时候忽视标准的限制概念非常清楚。

表空间

PostgreSQL的表空间概念不是标准的东西。因此TABLESPACE和USING INDEX TABLESPACE都是扩展。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!