PostgreSql与sqlserver对比杂记

警告

表访问权限并不会自动继承。因此，要么同时具有访问父表与所有子表的权限，要么必须使用 ONLY 表示法。所以在添加新子表的时候，请注意给它赋予适当的权限。

继承的一个严重局限性是索引(包括唯一约束)和外键约束只能用于单个表，而不能包括它们的子表(不管对引用表还是被引用表都是如此)，因此，在上面的例子里：

• 即使我们声明 cities.name 为 UNIQUE 或 PRIMARY KEY 也不会阻止 capitals 表拥有重复名字的 cities 数据行。并且这些重复的行在查询 cities 表的时候会显示出来。实际上，缺省时 capitals 将完全没有唯一约束，因此可能包含带有同名的多个行。你应该给 capitals 增加唯一约束，但即使这样做也不能避免与 cities的重复。

• 类似的，即使我们声明 cities.name 参照(REFERENCES)某些其它的表，这个约束也不会自动传播到 capitals 表。在这种条件下，你可以通过手工给 capitals 表增加同样的 REFERENCES 约束来做到这点。

• 声明一个其它表的字段为 REFERENCES cities(name) 将允许其它表包含城市名，但是不包含首府名。这种情况下没有很好的绕开办法。

这些缺点很可能在将来的版本中修补，但同时你也需要考虑一下，继承是否对你的问题真正有用。

PostgreSql  列名标识符长度

系统使用不超过 NAMEDATALEN-1 个字符作为标识符；你可以在命令中写更长的名字，但它们会被截断。NAMEDATALEN 的缺省值是 64 ，因此标识符最大长度是 63 。如果觉得这个限制有问题，那么你可以在 src/include/postgres_ext.h 里修改 NAMEDATALEN 来改变它。

引号包围的标识符可以包含编码不等于零的任意字符(要包含一个双引号，可以写两个相连的双引号)。这样我们就可以构造那些原本是不允许的表名或者字段名，比如那些包含空白或与号(&)的名字。但长度限制依旧。

把一个标识符用引号包围起来同时也令它大小写相关，而没有引号包围起来的名字总是转成小写。比如，我们认为标识符 FOO, foo, "foo" 是等价的 PostgreSQL 名字，但 "Foo" 和 "FOO" 与上面三个以及它们之间都是不同的。PostgreSQL 里对未加引号的名子总是转换成小写，这和 SQL 标准是不兼容的，SQL 标准要求未用引号包围起来的名字总是转成大写。因此根据标准，foo 等于 "FOO" 但不等于 "foo" 。如果你想编写可移植的程序，那么我们建议你要么就总是用引号包围某个名字，要么就从来不引。

表名长度：

IBM DB2 8.1=128;MS SQL SERVER 2000 =128;ORACLE9i =30

列名长度：

IBM DB2 8.1=30;MS SQL SERVER 2000 =128;ORACLE9i =30

约束名长度：

IBM DB2 8.1=18;MS SQL SERVER 2000 =128;ORACLE9i =30

索引名长度：

IBM DB2 8.1=128;MS SQL SERVER 2000 =128;ORACLE9i =30

表列数：

IBM DB2 8.1=255;MS SQL SERVER 2000 =1023;ORACLE9i =1000

count(*) 生成输入行的总数；count(f1) 生成 f1 不为 NULL 的输入行数；count(distinct f1) 生成 f1 唯一且非 NULL 的行数。

数组构造器

一个数组构造器是一个表达式，它从自身成员元素上构造一个数组值。一个简单的数组构造器由关键字 ARRAY 、一个左方括弧 [ 、一个或多个表示数组元素值的表达式(用逗号分隔)、一个右方括弧 ] 组成。比如

SELECT ARRAY[1,2,3+4];

  array

---------

{1,2,7}

行构造器

 行构造器是一个从提供给它的成员字段数值中构造行值(也叫复合类型值)的表达式。一个行构造器由关键字 ROW 、一个左圆括弧、零个或多个作为行字段值的表达式(用逗号分隔)、一个右圆括弧组成。比如：

SELECT ROW(1,2.5,'this is a test');

表达式计算规则

子表达式的计算顺序是没有定义的。特别要指出的是，一个操作符或者函数的输入并不一定是按照从左向右的顺序或者以某种特定的顺序进行计算的。

另外，如果一个表达式的结果可以通过只判断它的一部分就可以得到，那么其它子表达式就可以完全不计算了。比如，如果我们这么写

SELECT true OR somefunc();

那么 somefunc() 就(可能)根本不会被调用。即使像下面这样写也是一样

SELECT somefunc() OR true;

请注意这和某些编程语言里从左向右"短路"是不一样的。

因此，拿有副作用的函数作为复杂表达式的一部分是不明智的。在 WHERE 和 HAVING 子句里依赖副作用或者是计算顺序是特别危险的，因为这些子句都是作为生成一个执行规划的一部分进行了大量的再处理。在这些子句里的布尔表达式(AND/OR/NOT 的组合)可以用布尔代数运算律允许的任何方式进行识别。

如果需要强制计算顺序，那么可以使用 CASE 构造(参阅节9.13)。比如，下面是一种企图避免在 WHERE 子句里被零除的不可靠方法：

SELECT ... WHERE x <> 0 AND y/x > 1.5;

但是下面这个是安全的：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

这种风格的 CASE 构造会阻止优化，因此应该只在必要的时候才使用。在这个特殊的例子里，毫无疑问写成 y > 1.5*x 更好。

缺省值

CREATE TABLE test

(

NAMES VARCHAR(300) DEFAULT('1234')--sql

)

CREATE TABLE products (

    product_no integer,

    name text,

    price numeric

DEFAULT 9.99);

外键

外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中出现的数值。我们把这个行为称为两个相关表之间的参照完整性。

假设你有个产品表，我们可能使用了好几次：

CREATE TABLE products (

    product_no integer PRIMARY KEY,

    name text,

    price numeric

);

假设你有一个存储这些产品的订单的表。我们想保证订单表只包含实际存在的产品。因此我们在订单表中定义一个外键约束引用产品表：

CREATE TABLE orders (

    order_id integer PRIMARY KEY,

    product_no integer

REFERENCES products (product_no)

,

    quantity integer

);

现在，我们不能创建任何其 product_no 没有在产品表中出现的订单。

在这种情况下我们把订单表叫做引用表，而产品表叫做被引用表。同样，也有引用字段和被引用字段。

你也可以把上面的命令简写成

CREATE TABLE orders (

    order_id integer PRIMARY KEY,

    product_no integer

REFERENCES products

,

    quantity integer

);

因为如果缺少字段列表的话，就会引用被引用表的主键。

一个外键也可以约束和引用一组字段。同样，也需要写成表约束的形式。下面是一个捏造出来的语法例子：

CREATE TABLE t1 (

  a integer PRIMARY KEY,

  b integer,

  c integer,

 

FOREIGN KEY (b, c) REFERENCES other_table (c1, c2));

当然，被约束的字段数目和类型需要和被引用字段数目和类型一致。

和平常一样，你也可以给外键约束赋予自定义的名字。

一个表可以包含多于一个外键约束。这个特性用于实现表之间的多对多关系。比如你有关于产品和订单的表，但现在你想允许一个订单可以包含多种产品(上面那个结构是不允许这么做的)，你可以使用这样的结构：

CREATE TABLE products (

    product_no integer PRIMARY KEY,

    name text,

    price numeric

);

 

CREATE TABLE orders (

    order_id integer PRIMARY KEY,

    shipping_address text,

    ...

);

 

CREATE TABLE order_items (

    product_no integer REFERENCES products,

    order_id integer REFERENCES orders,

    quantity integer,

    PRIMARY KEY (product_no, order_id)

);

注意最后的表的主键和外键是重叠的。

我们知道外键不允许创建和任何产品都无关的订单。但是如果一个订单创建之后其引用的产品被删除了怎么办？SQL 也允许你处理这个问题。简单说，我们有几种选择：

• 不允许删除一个被引用的产品

• 同时也删除订单

• 其它的?

为了说明这个问题，我们对上面的多对多关系制定下面的策略：如果有人想删除一种仍然被某个订单引用的产品(通过 order_items)，那么就不允许这么做。如果有人删除了一个订单，那么订单项也被删除。

CREATE TABLE products (

    product_no integer PRIMARY KEY,

    name text,

    price numeric

);

 

CREATE TABLE orders (

    order_id integer PRIMARY KEY,

    shipping_address text,

    ...

);

 

CREATE TABLE order_items (

    product_no integer REFERENCES products

ON DELETE RESTRICT

,

    order_id integer REFERENCES orders

ON DELETE CASCADE

,

    quantity integer,

    PRIMARY KEY (product_no, order_id)

);

限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。NO ACTION 的意思是如果在检查约束的时候还存在任何引用行，则抛出错误；如果你不声明任何东西，那么它就是缺省的行为。这两个选择的实际区别是：NO ACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。CASCADE 声明在删除一个被引用的行的时候，所有引用它的行也会被自动删除掉。在外键字段上的动作还有两个选项：SET NULL 和 SET DEFAULT ，它们导致在被引用行删除的时候，将引用它们的字段分别设置为 NULL 和缺省值。请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SET DEFAULT ，但是缺省值并不能满足外键，那么该动作就会失败。

与 ON DELETE 类似的还有 ON UPDATE 选项，它是在被引用字段修改(更新)的时候调用的，可用的动作是一样的。

修改字段的数据类型

把一个字段转换成另外一种数据类型，使用下面的命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有在字段里现有的每个项都可以隐含的转换城新类型时才可能成功。如果需要更复杂的转换，你可以增加一个 USING 子句，它声明如何从旧值里计算新值。

PostgreSQL 将试图把字段的缺省值(如果存在)转换成新的类型，还有涉及该字段的任何约束。但是这些转换可能失败，或者可能生成奇怪的结果。在修改某字段类型之前，你最好删除那些约束，然后再把合适的约束添加上去。

重命名字段

重命名一个字段：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

重命名一个表：

ALTER TABLE products RENAME TO items;

 

权限

如果你创建了一个数据库对象，那么你就成为它的所有者。缺省时，只有对象的所有者可以在对象上做任何事情。为了允许其它用户使用它，我们必须赋予他们权限。不过超级用户总是可以操作任何对象。

有好多种不同的权限：SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE 。适用于特定对象的权限因对象类型(表/函数等)不同而不同。有关 PostgreSQL 所支持的不同类型的权限的完整信息，请参考 GRANT 的手册页。下面的章节将为你展示如何利用这些权限。

修改或者删除一个对象的权限永远是所有者独有的权限。

【注意】要改变一个表、索引、序列、或者视图的所有者，使用 ALTER TABLE 命令，以及对应其它对象类型的 ALTER 命令。

使用 GRANT 命令赋予权限。因此，如果 joe 是一个已经存在的用户，而 accounts 是一个已经存在的表，更新表的权限可以用下面的命令赋予：

GRANT UPDATE ON accounts TO joe;

在权限的位置写上 ALL 则赋予所有与该对象类型相关的权限。

名为 PUBLIC 的特殊"用户"可以用于将权限赋予系统中的所有用户。另外，还可以使用"组"角色来帮助管理一群用户的权限，细节可参见章18。

可以使用 REVOKE 命令撤销权限：

REVOKE ALL ON accounts FROM PUBLIC;

对象所有者的特殊权限(也就是 DROP, GRANT, REVOKE 等权限)总是隐含地属于所有者，并且不能赋予或者撤销。但是对象所有者可以选择撤销自己的普通权限，比如把一个表做成对自己和别人都是只读的。

最初，只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是，我们可以赋予一个"with grant option"权限，这样就允许接受权限的人将该权限转授他人。如果授权选项后来被撤销，那么所有那些从这个接受者接受了权限的用户(直接或间级)都将失去该权限。细节详见 GRANT 和 REVOKE 手册页。

依赖性跟踪

如果你创建了一个包含许多表，并且带有外键约束、视图、触发器、函数等复杂的数据库结构。那么你就会在对象之间隐含地创建了一个依赖性的网络。比如，一个带有外键约束的表依赖于它所引用的表。

为了保证整个数据库结构的完整性，PostgreSQL 保证你无法删除那些还被其它对象依赖的对象。比如，试图删除在节5.3.5里被订单表所依赖的产品表是不能成功的，会有类似下面的错误信息出现：

DROP TABLE products;

 

NOTICE:  constraint orders_product_no_fkey on table orders depends on table products

ERROR:  cannot drop table products because other objects depend on it

HINT:  Use DROP ... CASCADE to drop the dependent objects too.

这个错误信息包含一个有用的提示：如果你不想麻烦的分别删除所有依赖对象，你可以运行

DROP TABLE products CASCADE;

然后所有被依赖的对象都将被删除(并不删除订单表，只是删除外键约束)。如果你想检查 DROP ... CASCADE 会干什么，运行不带 CASCADE 的 DROP 然后阅读 NOTICE 信息。

PostgreSQL 里的所有删除命令都支持声明 CASCADE 。当然，具体的依赖性实体取决于对象的类型。你也可以写 RESTRICT 而不是 CASCADE 以获取缺省的行为(仅限于删除那些其它对象所依赖的对象)。

【注意】根据 SQL 标准，要求至少声明 RESTRICT 或 CASCADE 中的一个。实际上没有哪种数据库系统强制这一点，但是缺省的行为是 RESTRICT 还是 CASCADE则因系统而异。

【注意】在 PostgreSQL 7.3之前的外键约束依赖性和序列字段依赖性在升级过程中都不会得到维护或者创建。所有其它的依赖性类型 在从7.3版本以前的数据库升级过程中都将得到恰当的创建。

组合查询

可以对两个查询的结果进行集合操作(并、交、差)。语法是：

query1 UNION [ALL] query2query1 INTERSECT [ALL] query2query1 EXCEPT [ALL] query2

query1 和 query2 可以是讨论过的所有查询。集合操作也可以嵌套和级连，比如：

query1 UNION query2 UNION query3

它实际上等价于

(query1 UNION query2) UNION query3

UNION 把 query2 的结果附加到 query1 的结果上(不过我们不能保证这就是这些行实际的返回顺序)，并且像 DISTINCT 那样删除结果中所有重复的行(除非声明了 UNION ALL)。

INTERSECT 返回那些同时存在于 query1 和 query2 结果中的行，除非声明了 INTERSECT ALL ，否则所有重复行都被删除。

EXCEPT 返回所有在 query1 结果中但是不在 query2 结果中的行(有时侯这叫做两个查询的差)。除非声明了 EXCEPT ALL ，否则所有重复行都被删除。

为了能够计算两个查询的并、交、差，这两个查询必须是"并集兼容的"，也就是它们都返回同样数量的列，并且对应的列有兼容的数据类型，就像节10.5里描述的那样。

LIMIT 和 OFFSET（用来分页不是很爽？）

LIMIT 和 OFFSET 子句允许你只取出查询结果中的一部分数据行：

SELECT select_list    FROM table_expression    [ORDER BY sort_expression1 [ASC | DESC] [, sort_expression2 [ASC | DESC] ...]

]

    [

LIMIT { number | ALL }] [OFFSET number]

如果给出了一个 LIMIT 计数，那么将返回不超过该数字的行(也可能更少些，因为可能查询本身生成的总行数就比较少)。LIMIT ALL 和省略 LIMIT 子句是一样的。

OFFSET 指明在开始返回行之前忽略多少行。OFFSET 0 和省略 OFFSET 子句是一样的。如果 OFFSET 和 LIMIT 都出现了，那么在计算 OFFSET 之前先忽略 LIMIT 指定的行数。

使用 LIMIT 的同时使用 ORDER BY 子句把结果行约束成一个唯一的顺序是一个好主意。否则你就会的到一个不可预料的子集。你要的可能是第十到二十行，但以什么顺序的十到二十？除非你声明了 ORDER BY ，否则顺序是未知的。

查询优化器在生成查询规划的时候会考虑 LIMIT ，因此如果你给 LIMIT 和 OFFSET 的值不同，那么你很可能得到不同的规划(产生不同的行顺序)。因此，使用不同的LIMIT/OFFSET 值选择不同的子集将生成不一致的结果，除非你用 ORDER BY 强制一个可预料的顺序。这可不是臭虫，而是一个很自然的结果，因为 SQL 没有许诺把查询的结果按照任何特定的顺序发出，除非用了 ORDER BY 来约束顺序。

OFFSET 子句忽略的行仍然需要在服务器内部计算；因此，一个很大的 OFFSET 可能还是不够有效率。

序列号类型

serial 和 bigserial 类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。类似其它一些数据库中的 AUTO_INCREMENT 属性。在目前的实现中，下面一个语句：

CREATE TABLE tablename

(

 

colname

SERIAL

);

等价于声明下面几个语句：

CREATE SEQUENCE tablename_colname

_seq;

CREATE TABLE

tablename

(

 

colname integer NOT NULL DEFAULT nextval('tablename_colname

_seq')

);

ALTER SEQUENCE

tablename_colname_seq OWNED BY tablename.colname;

因此，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器读取。应用了一个 NOT NULL 约束以确保 NULL 不会被插入。在大多数情况下你可能还希望附加一个 UNIQUE 或 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动的。最后，将序列发生器"从属于"那个字段，这样当该字段或表被删除的时候也一并删除它。

【注意】PostgreSQL 7.3以前，serial 隐含 UNIQUE 。但现在不再如此。如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

要在 serial字段中插入序列中的下一个数值，主要是要注意 serial字段应该赋予缺省值。我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现，也可以通过使用 DEFAULT 关键字来实现。

类型名 serial 和 serial4 是等效的：两者都创建 integer 字段。类型名 bigserial 和 serial8 也一样，只不过它创建一个 bigint 字段。如果你预计在表的生存期中使用的标识数目可能超过 2³¹ 个，那么你应该使用 bigserial 。

一个 serial 类型创建的序列在所属的字段被删除的时候自动删除。你可以只删除序列而不删除字段，不过这将删除该字段的缺省值表达式。