PostgreSQL 笔记

ou

PostgreSQL 是一个采用 BSD 许可证分发的跨平台的对象关系模型数据库，最早可以追溯到 1982 年在伯克利开始的 Ingres 计划。PostgreSQL 出现的时间比 MySQL 要早，之前也听过 PostgreSQL 的大名，不过看到相关的讨论比较少。由于这里的实验需求远未达到两者的极限，所以也没什么评测，网上的评价就不搬运过来了，有兴趣的可以搜一下。这里的实验环境是 Debian 6，PostgreSQL 版本是 9.1。

初始化

不同于 MySQL 在安装的时候会被要求输入 root 的密码，安装 PostgreSQL 的时候什么提示都没有，装完之后也不知道该干嘛……在网上搜了一把，照着参考资料 [1] 操作了一遍，基本算是入门了。

由于没有 root 用户，默认有 root 权限的用户名是 postgres，也有同名的数据库。网上有很多介绍初始化的方法都是新建一个 postgres 的用户，然后切换到该用户登录后再修改密码。但是觉得为了初始化新建一个用户有点小题大作了，于是上网搜了下，发现可以通过修改配置文件达到目的。

首先修改 /etc/postgresql/9.1/main/pg_hba.conf，把其中的

local all postgres peer

一行最后的“peer”认证方式改为“trust”，表示信任从本机登录的用户，重启数据库之后就能用命令

psql -U postgres

直接登录了。登录后的第一件事就是用

\password postgres

改个密码，然后再把刚才修改的那行内容改回原来的“peer”认证方式，然后重启，再然后就可以使用命令

psql -U postgres -d postgres -h 127.0.0.1

然后输入密码登录了，默认监听的端口是 5432。

更一般的登录命令是

psql -h <dbhost> -p <dbport> -U <sbuser> -d <dbname>

密码好像不能通过命令行指定，只能手动输入（帮助说明中说可以把密码写在 ~/.pgpass 里，但是配置文件的具体格式不了解）。

基本操作命令

PostgreSQL 支持标准的 SQL 语法，创建/删除/更改表和插入/删除数据等操作都和其它数据库类似，只是在一些没有标准规定的命令和其它数据库不同：

  • \?：查看可用的命令列表（知道这个命令就行了，其它的用到再查）；
  • \h：查看 SQL 语句的解释，比如\h select；
  • \l：列出所有数据库，相当于 MySQL 的“show databases”；
  • \c [dbname]：使用某个数据库，相当于 MySQL 的“use ”；
  • \d：列出当前数据库的所有表，相当于 MySQL 的“show tables”；
  • \d [table_name]：列出某一张表格的结构，相当于 MySQL 的“desc table”；
  • \e：打开编辑器编写 SQL 语句，保存后退出编辑器，其中的 SQL 语句就会被执行，相当好的一个功能；
  • \conninfo：列出当前数据库和连接的信息。

基本编程接口

在 Debian 中的 C 开发包是 libpq-dev，函数使用例子可以从参考资料 [2] 中找到。

#include <libpq-fe.h>

int main(void)
{
    PGconn* conn;
    PGresult* res;
    const char* conninfo = "hostaddr = 127.0.0.1 port = 5432 user = postgres password = postgres dbname = postgres connect_timeout = 2";

    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) == CONNECTION_OK)
        fprintf(stderr, "connect ok\n");
    else
        fprintf(stderr, "login failed: %s\n", PQerrorMessage(conn));

    res = PQexec(conn, "select * from test");

    ExecStatusType status = PQresultStatus(res);
    fprintf(stderr, "exec status: %s.\n", PQresStatus(status));

    if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
        fprintf(stderr, "error: %s", PQresultErrorMessage(res));
    else {
        int i, j;
        int nr_row = PQntuples(res);
        int nr_col = PQnfields(res);

        for (i = 0; i < nr_row; ++i) {
            printf("----------------------------\n");
            for (j = 0; j < nr_col; ++j)
                printf("%s: %s (datalen: %d)\n", PQfname(res, j),
                       PQgetvalue(res, i, j), PQgetlength(res, i, j));
        }
    }

    PQclear(res);

    PQfinish(conn);

    return 0;
}

要编译这个程序需要包含头文件 libpq-fe.h，和加上链接选项 -lpq。

首先需要调用函数

PGconn *PQconnectdb(const char *conninfo);

建立到数据库的连接。函数的参数比较奇怪，只有一个字符串，其中是一些形如“key = value”的配置项（等号两边的空格可选），如程序中所写的那样。其中支持的配置项可以从参考资料 [3] 中找到，如果 value 中含有空格则需要加单引号。另外还有一个指定部分参数的版本：

PGconn *PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty,
                     const char *dbName, const char *login, const char *pwd);

其它没有指定的参数使用默认值。在实验中发现，如果没有指定超时间隔，并且如果一直连接不上数据库的话会阻塞着不退出，因此建议还是用 PQconnectdb() 这个接口，这样可以指定超时间隔。

有一些接口可以获得解析后的参数：

char *PQdb(const PGconn *conn);
char *PQuser(const PGconn *conn);
char *PQpass(const PGconn *conn);
char *PQhost(const PGconn *conn);
char *PQport(const PGconn *conn);
char *PQtty(const PGconn *conn);
char *PQoptions(const PGconn *conn);

PQconnectdb() 的返回值是一个 PGconn 类型的指针。要注意的是，无论连接是否成功这个指针都不为空，需要用函数

ConnStatusType PQstatus(const PGconn *conn);

判断返回状态。在同步的情况下函数会返回 CONNECTION_OK 或者 CONNECTION_BAD 表示连接是否可用，在异步连接的情况下还有其它返回值，可以看看参考资料 [3]。

要测试一个连接是否有效，可以使用

PGPing PQping(const char *conninfo);

返回值的含义如下：

PQPING_OK
The server is running and appears to be accepting connections.

PQPING_REJECT
The server is running but is in a state that disallows connections (startup, shutdown, or crash recovery).

PQPING_NO_RESPONSE
The server could not be contacted. This might indicate that the server is not running, or that there is something wrong with the given connection parameters (for example, wrong port number), or that there is a network connectivity problem (for example, a firewall blocking the connection request).

PQPING_NO_ATTEMPT
No attempt was made to contact the server, because the supplied parameters were obviously incorrect or there was some client-side problem (for example, out of memory).

连接成功后可以用函数

PGresult *PQexec(PGconn *conn, const char *command);

来执行 SQL 语句。同样地，不管执行是否成功，PGresult 都不为空。要判断执行情况，可以用函数

ExecStatusType PQresultStatus(const PGresult *res);
char *PQresultErrorMessage(const PGresult *res);

获取返回值及相应的错误信息。返回值及表示的意义如下：

PGRES_EMPTY_QUERY
The string sent to the server was empty.

PGRES_COMMAND_OK
Successful completion of a command returning no data.

PGRES_TUPLES_OK
Successful completion of a command returning data (such as a SELECT or SHOW).

PGRES_COPY_OUT
Copy Out (from server) data transfer started.

PGRES_COPY_IN
Copy In (to server) data transfer started.

PGRES_BAD_RESPONSE
The server's response was not understood.

PGRES_NONFATAL_ERROR
A nonfatal error (a notice or warning) occurred.

PGRES_FATAL_ERROR
A fatal error occurred.

PGRES_COPY_BOTH
Copy In/Out (to and from server) data transfer started. This is currently used only for streaming replication.

可以看到，如果执行成功，但是根据执行的 SQL 是否有返回值，相应的返回状态是不一样的。如果执行的 SQL 语句有返回值，可以使用函数

int PQntuples(const PGresult *res);

来获得返回的记录数量；使用

int PQnfields(const PGresult *res);

来获得返回值有多少个域；使用函数

char *PQfname(const PGresult *res, int column_number);

来获得指定域的名称；函数

Oid PQftype(const PGresult *res, int column_number);

可以获得指定域的类型。返回值 Oid 表示的含义可以从数据库的系统表 pg_type 中查到，也可以从数据库源代码的头文件 src/include/catalog/pg_type.h 中找到。

PostgreSQL 没有提供像 MySQL 那样的对输入数据的转义函数，而是用了另一种方法，就是在插入内容的两边分别加上“$$”，在这两对“$$”之间的内容将会被原样插入到数据库中。例如要插入带单引号的数据：

update test set name = $$ou'online$$ where id = 5

由于返回的结果有若干个，每个结果有若干个域，整个结果集有点像一个二维数组，可以用函数

char *PQgetvalue(const PGresult *res, int row_number, int column_number);
int PQgetlength(const PGresult *res, int row_number, int column_number);

获得第 row_number 条记录的第 column_number 个域的内容及长度。如果某个域为空，PQgetvalue() 会返回一个空的字符串，而不是一个 NULL。要判断某个域是否为空可以使用函数

int PQgetisnull(const PGresult *res, int row_number, int column_number);

上面和 PGresult 相关的函数的返回值都不需要手动释放，只要在用完 PGresult 后调用函数

void PQclear(PGresult *res);

释放即可。

程序结束时需要用

void PQfinish(PGconn *conn);

来释放资源。

上面介绍的都是同步操作的接口，PostgreSQL 还支持异步操作，在参考资料 [2] 中可以找到。这里是一个 PostgreSQL 和 MySQL 的编程接口的功能对应表：

+===========================================+===========================================================+
|               PostgreSQL                  |                           MySQL                           |
+===========================================+===========================================================+
| PQconnectdb()                             | mysql_init(), mysql_real_connect()                        |
+-------------------------------------------+-----------------------------------------------------------+
| PQfinish()                                | mysql_close()                                             |
+-------------------------------------------+-----------------------------------------------------------+
| PQsetClientEncoding()                     | mysql_set_character_set()                                 |
+-------------------------------------------+-----------------------------------------------------------+
| PQerrorMessage()                          | mysql_error()                                             |
+-------------------------------------------+-----------------------------------------------------------+
| PQping()                                  | mysql_ping()                                              |
+-------------------------------------------+-----------------------------------------------------------+
| PQexec()                                  | mysql_query(), mysql_real_query(), mysql_store_result()   |
+-------------------------------------------+-----------------------------------------------------------+
| PQnfields()                               | mysql_num_fields()                                        |
+-------------------------------------------+-----------------------------------------------------------+
| PQfname()                                 | mysql_fetch_field_direct(), mysql_fetch_fields()          |
+-------------------------------------------+-----------------------------------------------------------+
| PQntuples()                               | mysql_num_rows()                                          |
+-------------------------------------------+-----------------------------------------------------------+
| PQgetvalue(), PQgetlength()               | mysql_fetch_row(), mysql_row_seek(), mysql_row_tell()     |
+-------------------------------------------+-----------------------------------------------------------+
| PQresultStatus(), PQresultErrorMessage()  | mysql_error()                                             |
+-------------------------------------------+-----------------------------------------------------------+
| PQclear()                                 | mysql_free_result()                                       |
+===========================================+===========================================================+

除了可以使用一般的接口操作数据库，PostgreSQL 还提供了一种称为“ECPG”的方法，可以直接在程序中编写 SQL 语句（见参考资料 [4]）。

其它

除了正常执行 SQL 语句外，psql 还提供了一些有趣的设定，例如可以指定结果显示的边界风格：

\pset border 2

表示使用风格 2，和 MySQL 的一样。

就编程接口使用感受来说，MySQL 的更简洁更傻瓜化，而 PostgreSQL 提供了更细的控制粒度和更详细的信息，作为一个程序员的我自然是喜欢能提供更精细控制的 PostgreSQL 多一点。

最后写了一个 Lua 操作 PostgreSQL 的扩展，放在 这里，和之前写的 lua-mysql 接口基本一致。

参考资料

[1] PostgreSQL新手入门
[2] PostgreSQL 9.1.14 Documentation
[3] 31.1. Database Connection Control Functions
[4] Chapter 33. ECPG - Embedded SQL in C

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注