PostgreSQL文档阅读笔记(一)


图片来自:http://permalink.gmane.org/gmane.comp.db.postgresql.advocacy/10889

安装服务

1
2
 pg_ctl register   [-N SERVICENAME] [-U USERNAME] [-P PASSWORD] [-D DATADIR]
                   [-S START-TYPE] [-w] [-t SECS] [-o "OPTIONS"]

卸载服务

1
pg_ctl unregister [-N SERVICENAME]

启动

1
2
3
pg_ctl -D "D:/software/pgsql/data" -l "D:/software/pgsql/data/logfile" start
::或
net start pgsql

停止

net stop pgsql
::或

1
2
3
4
echo finding server listening or not?
netstat -ano | findstr "5432"

pg_ctl -D "D:/software/pgsql/data" -l "D:/software/pgsql/data/logfile" stop

重启

1
pg_ctl -D "D:/software/pgsql/data" -l "D:/software/pgsql/data/logfile" restart

“serial number” (序号)

如:

1
2
3
4
CREATE  TABLE  products  (
product_no  INTEGER  DEFAULT  NEXTVAL('products_product_no_seq'),
...
);

where the nextval() function supplies successive values from a sequence object (see Section 9.15).
This arrangement is sufficiently common that there’s a special shorthand for it:

1
2
3
4
CREATE  TABLE  products  (
product_no  SERIAL,
...
);

The SERIAL shorthand is discussed further in Section 8.1.4.

约束

1
2
3
4
5
6
7
8
9
CREATE  TABLE  products  (
product_no  INTEGER,
name  text,
price  NUMERIC,
CHECK  (price  >  0),
discounted_price  NUMERIC,
CHECK  (discounted_price  >  0),
CONSTRAINT  valid_discount  CHECK  (price  >  discounted_price)
);

唯一约束

Unique Constraints
Unique constraints ensure that the data contained in a column or a group of columns is unique with
respect to all the rows in the table. The syntax is:

1
2
3
4
5
CREATE  TABLE  products  (
product_no  INTEGER  UNIQUE,
name  text,
price  NUMERIC
);

when written as a column constraint, and:

1
2
3
4
5
6
CREATE  TABLE  products  (
product_no  INTEGER,
name  text,
price  NUMERIC,
UNIQUE  (product_no)
);

when written as a table constraint.

1
2
3
4
5
6
CREATE  TABLE  example  (
a  INTEGER,
b  INTEGER,
c  INTEGER,
UNIQUE  (a,  c)
);

NULL 和 NOT NULL约束

1
2
3
4
5
CREATE  TABLE  products  (
product_no  INTEGER  NOT  NULL,
name  text  NOT  NULL,
price  NUMERIC  NOT  NULL  CHECK  (price  >  0)
);

5.3.4. Primary Keys (主键)

从技术上来说,主键约束是只是简单的唯一约束和 not-null约束的结合。因此下面两个表的定义完全接受相同的数据:

1
2
3
4
5
6
7
8
9
10
CREATE  TABLE  products  (
product_no  INTEGER  UNIQUE  NOT  NULL,
name  text,
price  NUMERIC
);
CREATE  TABLE  products  (
product_no  INTEGER  PRIMARY  KEY,
name  text,
price  NUMERIC
);

主键也可以包含超过一个字段,语法类似unique约束:

1
2
3
4
5
6
CREATE  TABLE  example  (
a  INTEGER,
b  INTEGER,
c  INTEGER,
PRIMARY  KEY  (a,  c)
);

主键表明一个或多个字段(列)可以用于唯一地标识表中的一行(row)数据。(This is a direct consequence of the definition of a primary key. 注意:unique约束本身不提供唯一标识,因为它没有排除 null 值。)
添加一个主键将会自动生成定义为主键的列(可以是一个或多个列)的一个唯一 btree 索引。每个表最多只允许有一个主键。
关系数据库理论表明,每个表必须有一个主键。PostgreSQL并不强制要求用户遵循这条规则,但是最好还是遵守的好。

5.3.5. Foreign Keys (外键约束)

Say you have the product table that we have used several times already:

1
2
3
4
5
6
7
8
9
10
11
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 不存在于products表中的 orders 了。
在这种情况下,我们把 orders 这个表叫做the referencing table(参考表),把products表叫做 the
referenced table (被参考表)。类似地,还有参考列和被参考列。

参考一组字段:

1
2
3
4
5
6
CREATE  TABLE  t1  (
a  INTEGER  PRIMARY  KEY,
b  INTEGER,
c  INTEGER,
FOREIGN  KEY  (b,  c)  REFERENCES  other_table  (c1,  c2)
);

多个外键的情况:
A table can contain more than one foreign key constraint. This is used to implement many-to-many
relationships between tables. Say you have tables about products and orders, but now you want to
allow one order to contain possibly many products (which the structure above did not allow). You
could use this table structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE  TABLE  products  (
product_no  INTEGER  PRIMARY  KEY,
name  text,
price  NUMERIC
);
CREATE  TABLE  orders  (
order_id  INTEGER  PRIMARY  KEY,
shipping_address  text,
...
);
54CREATE  TABLE  order_items  (
product_no  INTEGER  REFERENCES  products,
order_id  INTEGER  REFERENCES  orders,
quantity  INTEGER,
PRIMARY  KEY  (product_no,  order_id)
);

注意最后一个表中,主键和外键是重叠的。

我们知道,外键约束现在阻止与任何 products(产品) 无关联的 orders (订单)。但是,假如在一个订单创建之后,它所参考的 product (产品 )被删除了怎么办?SQL 能让你处理好这种情况。
直观地看,我们有下面几个选选择:
• 禁止删除被参考的 product (产品)
• 连同 order (订单)一起删除了
• 其它 ?

为举例说明这个, 我们就上面的多对多关系的例子实行如下策略:
当有人想删除一个被某订单(order)参考的产品(product) (通过 order_items) 时,我们禁止它。
如果有人要删除一个订单,我们让 order_items 同时被删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
);

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of
a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is
checked, an error is raised; this is the default behavior if you do not specify anything. (The essential
difference between these two choices is that NO ACTION allows the check to be deferred until later
in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is
deleted, row(s) referencing it should be automatically deleted as well. There are two other options:
SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default
values, respectively, when the referenced row is deleted. Note that these do not excuse you from
observing any constraints. For example, if an action specifies SET DEFAULT but the default value
would not satisfy the foreign key, the operation will fail.
Analogous to(类似于) ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same.

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. Because this is not always needed, and there are many choices available on
how to index, declaration of a foreign key constraint does not automatically create an index on the
referencing columns.
More information about updating and deleting data is in Chapter 6.
Finally, we should mention that a foreign key must reference columns that either are a primary key or
form a unique constraint.
If the foreign key references a unique constraint, there are some additional
possibilities regarding how null values are matched. These are explained in the reference documenta-
tion for CREATE TABLE.

5.4. System Columns (系统列)

在pgsql中,每个表中都有一些由数据库系统定义的列。用户定义的列名不能与之雷同。为避免麻烦,最好了解下它们的名字。

1
2
3
4
5
6
7
oid
tableoid
xmin
cmin
xmax
cmax
ctid

修改表

添加列:
ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ADD COLUMN description text CHECK (description <> ”);
删除列
ALTER TABLE products DROP COLUMN description;
ALTER TABLE products DROP COLUMN description CASCADE;
添加约束:
To add a constraint, the table constraint syntax is used. For example:
ALTER TABLE products ADD CHECK (name <> ”);
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

移除约束:
ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

改变列的默认值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

改变列的数据类型:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

重命名列
ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表
ALTER TABLE products RENAME TO items;

5.6. Privileges 权限

SELECT, INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

授权 (accounts为表名)
GRANT UPDATE ON accounts TO joe;

The special “user” name PUBLIC can be used to grant a privilege to every user on the system. Also,
“group” roles can be set up to help manage privileges when there are many users of a database — for
details see Chapter 20.

取消授权
REVOKE ALL ON accounts FROM PUBLIC;

5.7. Schemas 模式

A PostgreSQL database cluster contains one or more named databases. Users and groups of users are
shared across the entire cluster, but no other data is shared across databases. Any given client con-
nection to the server can access only the data in a single database, the one specified in the connection
request.
Note: Users of a cluster do not necessarily have the privilege to access every database in the
cluster. Sharing of user names means that there cannot be different users named, say, joe in
two databases in the same cluster; but the system can be configured to allow joe access to only
some of the databases.
A database contains one or more named schemas, which in turn contain tables. Schemas also contain
other kinds of named objects, including data types, functions, and operators. The same object name
can be used in different schemas without conflict; for example, both schema1 and myschema can
contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access
objects in any of the schemas in the database he is connected to, if he has privileges to do so.

使用模式的好处:
There are several reasons why one might want to use schemas:
• 允许多个用户使用同一个数据库而不会妨碍彼此。
• 把数据库对象分配到逻辑组以便于管理.
• 第三方应用可以被放在不同的模式下因而不会与其它对象的名称发生冲突。

模式有点类似于操作系统层面上的目录,不过它不可以嵌套。

5.7.1. Creating a Schema 创建模式

CREATE SCHEMA myschema;

存取数据时 通过 schema.table 或 database.schema.table 访问。

创建指定模式下的表:
CREATE TABLE myschema.mytable (

);

To drop a schema if it’s empty (all objects in it have been dropped), use:
DROP SCHEMA myschema;
To drop a schema including all contained objects, use:
DROP SCHEMA myschema CASCADE;

Often you will want to create a schema owned by someone else (since this is one of the ways to
restrict the activities of your users to well-defined namespaces). The syntax for that is:
CREATE SCHEMA schemaname AUTHORIZATION username;

You can even omit the schema name, in which case the schema name will be the same as the user
name. See Section 5.7.6 for how this can be useful.
Schema names beginning with pg_ are reserved for system purposes and cannot be created by users.

5.7.2. The Public Schema

在前面的章节,我们创建表时并没有指定模式。默认情况下,这些表被放到一个称为 public 的模式下面。每个新创建的数据库都包含这样一个模式。
因此,以下语句是等价的:
CREATE TABLE products ( … );

CREATE TABLE public.products ( … );

5.7.3. The Schema Search Path

Qualified names are tedious to write, and it’s often best not to wire a particular schema name into
applications anyway. Therefore tables are often referred to by unqualified names, which consist of
just the table name. The system determines which table is meant by following a search path, which is
a list of schemas to look in. The first matching table in the search path is taken to be the one wanted.
If there is no match in the search path, an error is reported, even if matching table names exist in other
schemas in the database.

通常情况下,我们仅指定表名(而不喜欢加上模式名),数据库系统根据搜索路径自动检测对应名字的表在哪个模式下。最首先匹配的表会被当作是需要的。如果搜索路径下没有匹配的表,则报告错误,即使被用来匹配的表名在该数据库的其它模式下存在。

To show the current search path, use the following command:
SHOW search_path;

To put our new schema in the path, we use:
SET search_path TO myschema,public;

5.7.4. Schemas and Privileges (模式和权限)

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of
the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects
in the schema, additional privileges might need to be granted, as appropriate for the object.
A user can also be allowed to create objects in someone else’s schema. To allow that, the CREATE
privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE
privileges on the schema public. This allows all users that are able to connect to a given database to
create objects in its public schema. If you do not want to allow that, you can revoke that privilege:

1
REVOKE  CREATE  ON  SCHEMA  public  FROM  PUBLIC;

(The first “public” is the schema, the second “public” means “every user”. In the first sense it is an
identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines
from Section 4.1.1.)

5.8. Inheritance (继承)

pgsql 是一个对象数据库系统。如可以这样定义表:

1
2
3
4
5
6
7
8
CREATE  TABLE  cities  (
name text,
population FLOAT,
altitude INT --  in  feet
);
CREATE  TABLE  capitals  (
state CHAR(2)
)  INHERITS  (cities);

For example, the following query finds the names of all cities, including state capitals, that are located
at an altitude over 500 feet:

1
2
3
SELECT  name,  altitude
FROM  cities
WHERE  altitude  >  500;

Given the sample data from the PostgreSQL tutorial (see Section 2.1), this returns:

1
2
3
4
5
name        |  altitude
-----------+----------
Las  Vegas  | 2174
Mariposa    | 1953
Madison      | 845

On the other hand, the following query finds all the cities that are not state capitals and are situated at
an altitude over 500 feet:

1
2
3
SELECT  name,  altitude
FROM  ONLY  cities
WHERE  altitude  >  500;
1
2
3
4
name        |  altitude
-----------+----------
Las  Vegas  | 2174
Mariposa    | 1953

SELECT, UPDATE and DELETE — support the ONLY keyword.

对于插入操作则有一些不同:

1
2
INSERT  INTO  cities  (name,  population,  altitude,  state)
VALUES  ('New  York',  NULL,  NULL,  'NY');

We might hope that the data would somehow be routed to the capitals table, but this does not
happen: INSERT always inserts into exactly the table specified. In some cases it is possible to redirect
the insertion using a rule (see Chapter 37). However that does not help for the above case because the
cities table does not contain the column state, and so the command will be rejected before the
rule can be applied.

5.9. Partitioning 分表

5.12. Dependency Tracking

To ensure the integrity of the entire database structure, PostgreSQL makes sure that you cannot drop
objects that other objects still depend on. For example, attempting to drop the products table we had
considered in Section 5.3.5, with the orders table depending on it, would result in an error message
such as this:
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.
The error message contains a useful hint: if you do not want to bother deleting all the dependent
objects individually, you can run:
DROP TABLE products CASCADE;
and all the dependent objects will be removed. In this case, it doesn’t remove the orders table, it only
removes the foreign key constraint. (If you want to check what DROP … CASCADE will do, run
DROP without CASCADE and read the NOTICE messages.)
All drop commands in PostgreSQL support specifying CASCADE. Of course, the nature of the possible
dependencies varies with the type of the object. You can also write RESTRICT instead of CASCADE to
get the default behavior, which is to prevent the dropping of objects that other objects depend on.
Note: According to the SQL standard, specifying either RESTRICT or CASCADE is required. No
database system actually enforces that rule, but whether the default behavior is RESTRICT or
CASCADE varies across systems.
Note: Foreign key constraint dependencies and serial column dependencies from PostgreSQL
versions prior to 7.3 are not maintained or created during the upgrade process. All other depen-
dency types will be properly created during an upgrade from a pre-7.3 database.

7.2.1.1. Joined Tables

Join Types

Cross join(交叉连接)
T1 CROSS JOIN T2
For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined
table will contain a row consisting of all columns in T1 followed by all columns in T2. If the
tables have N and M rows respectively, the joined table will have N * M rows.
FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2 . It is also equivalent to FROM T1
INNER JOIN T2 ON TRUE (see below).

Qualified joins(限制连接)

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

关键字 INNER 和 OUTER 在所有形式中都是可选的. INNER 是默认的; LEFT, RIGHT, 和 FULL 则是意味着这是一个 outer join (外连接).
The join condition is specified in the ON or USING clause, or implicitly by the word NATURAL.
The join condition determines which rows from the two source tables are considered to “match”,
as explained in detail below.
The ON clause is the most general kind of join condition: it takes a Boolean value expression
of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON
expression evaluates to true for them.
USING is a shorthand notation: it takes a comma-separated list of column names, which the joined
tables must have in common, and forms a join condition specifying equality of each of these pairs
of columns. Furthermore, the output of JOIN USING has one column for each of the equated
pairs of input columns, followed by the remaining columns from each table. Thus, USING (a,
b, c) is equivalent to ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) with the
exception that if ON is used there will be two columns a, b, and c in the result, whereas with
USING there will be only one of each (and they will appear first if SELECT * is used).
Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column
names that appear in both input tables. As with USING, these columns appear only once in the
output table. If there are no common columns, NATURAL behaves like CROSS JOIN.

The possible types of qualified join are:
INNER JOIN
For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join
condition with R1.
LEFT OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join
condition with any row in T2, a joined row is added with null values in columns of T2.
Thus, the joined table always has at least one row for each row in T1.
RIGHT OUTER JOIN
First, an inner join is performed. Then, for each row in T2 that does not satisfy the join
condition with any row in T1, a joined row is added with null values in columns of T1. This
is the converse of a left join: the result table will always have a row for each row in T2.
FULL OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join
condition with any row in T2, a joined row is added with null values in columns of T2.
Also, for each row of T2 that does not satisfy the join condition with any row in T1, a
joined row with null values in the columns of T1 is added.
Joins of all types can be chained together or nested: either or both T1 and T2 can be joined tables.
Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses,
JOIN clauses nest left-to-right.
To put this together, assume we have tables t1:

1
2
3
4
5
6
7
8
9
10
11
num  |  name
-----+------
1  |  a
2  |  b
3  |  c
and t2:
num  |  value
-----+-------
1  |  xxx
3  |  yyy
5  |  zzz

then we get the following results for the various joins:

1
2
3
4
5
6
7
8
9
10
11
12
13
=>  SELECT  *  FROM  t1  CROSS  JOIN  t2;
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
1  |  a        |      3  |  yyy
1  |  a        |      5  |  zzz
2  |  b        |      1  |  xxx
2  |  b        |      3  |  yyy
2  |  b        |      5  |  zzz
3  |  c        |      1  |  xxx
3  |  c        |      3  |  yyy
3  |  c        |      5  |  zzz
(9  ROWS)
1
2
3
4
5
6
=>  SELECT  *  FROM  t1  INNER  JOIN  t2  ON  t1.num  =  t2.num;
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
3  |  c        |      3  |  yyy
(2  ROWS)
1
2
3
4
5
6
=>  SELECT  *  FROM  t1  INNER  JOIN  t2  USING  (num);
num  |  name  |  VALUE
-----+------+-------
1  |  a        |  xxx
3  |  c        |  yyy
(2  ROWS)
1
2
3
4
5
6
=>  SELECT  *  FROM  t1  NATURAL  INNER  JOIN  t2;
num  |  name  |  VALUE
-----+------+-------
1  |  a        |  xxx
3  |  c        |  yyy
(2  ROWS)
1
2
3
4
5
6
7
=>  SELECT  *  FROM  t1  LEFT  JOIN  t2  ON  t1.num  =  t2.num;
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
2  |  b        | |
3  |  c        |      3  |  yyy
(3  ROWS)
1
2
3
4
5
6
7
=>  SELECT  *  FROM  t1  LEFT  JOIN  t2  USING  (num);
num  |  name  |  VALUE
-----+------+-------
1  |  a        |  xxx
2  |  b        |
3  |  c        |  yyy
(3  ROWS)
1
2
3
4
5
6
7
=>  SELECT  *  FROM  t1  RIGHT  JOIN  t2  ON  t1.num  =  t2.num;
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
3  |  c        |      3  |  yyy
| |      5  |  zzz
(3  ROWS)
1
2
3
4
5
6
7
8
=>  SELECT  *  FROM  t1  FULL  JOIN  t2  ON  t1.num  =  t2.num;
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
2  |  b        | |
3  |  c        |      3  |  yyy
| |      5  |  zzz
(4  ROWS)

The join condition specified with ON can also contain conditions that do not relate directly to the join.
This can prove useful for some queries but needs to be thought out carefully. For example:

1
2
3
4
5
6
7
=>  SELECT  *  FROM  t1  LEFT  JOIN  t2  ON  t1.num  =  t2.num  AND  t2.VALUE  =  'xxx';
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
2  |  b        | |
3  |  c        | |
(3  ROWS)

Notice that placing the restriction in the WHERE clause produces a different result:

1
2
3
4
5
=>  SELECT  *  FROM  t1  LEFT  JOIN  t2  ON  t1.num  =  t2.num  WHERE  t2.VALUE  =  'xxx';
num  |  name  |  num  |  VALUE
-----+------+-----+-------
1  |  a        |      1  |  xxx
(1  ROW)

This is because a restriction placed in the ON clause is processed before the join, while a restriction
placed in the WHERE clause is processed after the join.

7.2.1.2. Table and Column Aliases 表和列的别名

To create a table alias, write

1
FROM  table_reference  AS  alias

or

1
FROM  table_reference  alias

AS 关键字是可选的。

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a
table to itself, e.g.:
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
Additionally, an alias is required if the table reference is a subquery (see Section 7.2.1.3).
Parentheses(圆括号) are used to resolve ambiguities. In the following example, the first statement assigns the
alias b to the second instance of my_table, but the second statement assigns the alias to the result of
the join:

1
2
SELECT  *  FROM  my_table  AS  a  CROSS  JOIN  my_table  AS  b  ...
SELECT  *  FROM  (my_table  AS  a  CROSS  JOIN  my_table)  AS  b  ...

7.2.1.3. Subqueries 子查询

如:

1
FROM  (SELECT  *  FROM  table1)  AS  alias_name

A subquery can also be a VALUES list:

1
2
FROM  (VALUES  ('anne',  'smith'),  ('bob',  'jones'),  ('joe',  'blow'))
AS  names(FIRST,  LAST)

7.6. LIMIT and OFFSET

LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of
the query:

1
2
3
4
SELECT  select_list
FROM  table_expression
[  ORDER  BY  ...  ]
[  LIMIT  {  NUMBER  |  ALL  }  ]  [  OFFSET  NUMBER  ]

If a limit count is given, no more than that many rows will be returned (but possibly less, if the query
itself yields less rows). LIMIT ALL is the same as omitting the LIMIT clause.
OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as
omitting the OFFSET clause, and LIMIT NULL is the same as omitting the LIMIT clause. If both
OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows
that are returned.
When using LIMIT, it is important 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? The ordering is
unknown, unless you specified ORDER BY.
The query optimizer takes LIMIT into account when generating query plans, so you are very likely
to get different plans (yielding different row orders) depending on what you give 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.
The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large
OFFSET might be inefficient.
这里说到,如果在指定 LIMIT 和OFFSET 的情况下没有指定 ORDER BY 的放在,返回的结果是不可预知的。因此,使用时必须要注意。
并且,由OFFSET 指定跳过的行也要被服务器计算,因此,一个很大的 OFFSET 值会导致效率低下。

7.7. VALUES Lists

VALUES provides a way to generate a “constant table” that can be used in a query without having to
actually create and populate a table on-disk. The syntax is

1
2
3
VALUES  (  expression  [,  ...]  )  [,  ...]

VALUES  (1,  'one'),  (2,  'two'),  (3,  'three');

7.8. WITH Queries (Common Table Expressions)

WITH 提供了一种简化大查询中的复杂的语句途径。

8.1. Numeric Types

8.1.4. Serial Types

The data types serial and bigserial are not true types, but merely a notational convenience for
creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some
other databases). In the current implementation, specifying:

1
2
3
CREATE  TABLE  tablename  (
colname  SERIAL
);

is equivalent to specifying:

1
2
3
4
5
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;

9.7. Pattern Matching

9.7.1. LIKE

1
2
string  LIKE  pattern  [ESCAPE  escape-CHARACTER]
string  NOT  LIKE  pattern  [ESCAPE  escape-CHARACTER]

If pattern does not contain percent signs or underscores, then the pattern only represents the string
itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for
(matches) any single character; a percent sign (%) matches any sequence of zero or more characters.
Some examples:

1
2
3
4
'abc'  LIKE  'abc'        TRUE
'abc'  LIKE  'a%' TRUE
'abc'  LIKE  '_b_'        TRUE
'abc'  LIKE  'c' FALSE

LIKE pattern matching always covers the entire string. Therefore, to match a sequence anywhere
within a string, the pattern must start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective char-
acter in pattern must be preceded by the escape character. The default escape character is the back-
slash but a different one can be selected by using the ESCAPE clause. To match the escape character
itself, write two escape characters.
The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to
the active locale. This is not in the SQL standard but is a PostgreSQL extension.

9.24. System Administration Functions

Name Return Type Description
current_setting(setting_name) text get current value of setting

set_config(setting_name,new_value, is_local) text set parameter and return new value

The function current_setting yields the current value of the setting setting_name. It corre-
sponds to the SQL command SHOW. An example:

1
SELECT  current_setting('datestyle');
1
2
3
4
current_setting
-----------------
ISO,  MDY
(1  row)

set_config sets the parameter setting_name to new_value. If is_local is true, the new value
will only apply to the current transaction. If you want the new value to apply for the current session,
use false instead. The function corresponds to the SQL command SET. An example:
SELECT set_config(‘log_statement_stats’, ‘off’, false);
set_config
————
off
(1 row)

Table 9-55. Server Signalling Functions

NameReturn TypeDescription
pg_cancel_backend(pid int) booleanCancel a backend’s current query
pg_reload_conf() booleanCause server processes to reload their configuration files
pg_rotate_logfile() booleanRotate server’s log file
pg_terminate_backend(pid int) booleanTerminate a backend

Table 9-56. Backup Control Functions

NameReturn TypeDescription
pg_create_restore_point(name text) textCreate a named point for performing restore (restricted to superusers)
pg_current_xlog_insert_location() textGet current transaction log insert location
pg_current_xlog_location() textGet current transaction log write location
pg_start_backup(label text [, fast boolean ]) textPrepare for performing on-line backup (restricted to superusers or replication roles)
pg_stop_backup() textFinish performing on-line backup (restricted to superusers or replication roles)
pg_switch_xlog() textForce switch to a new transaction log file (restricted to superusers)
pg_xlogfile_name(location text) textConvert transaction log location string to file name
pg_xlogfile_name_offset(location text) text, integerConvert transaction log location string to file name and decimal byte offset within file

Table 9-57. Recovery Information Functions
……

更多
No Responses Post a comment

Leave a Reply

Note: You may use basic HTML in your comments. Your email address will not be published.

Subscribe to this comment feed via RSS