PostgreSQL手册学习笔记
最近,项目要从Oracle 迁移到PostgreSQL上(主要针对Java server端查询语句可能遇到问题的一些思考)。
这里没有牵扯到 表结构,存数过程,函数,Job的迁移
早上把PostgreSQL8.0 手册 1 2 部分看了一遍,总结了一些知识点(主要是PostgreSQL自己的特殊的部分和我自己认为重要的部分):
1.创建一个数据库
createdb db_name;
2.删除一个数据库
dropdb db_name;
3.查询数据库版本
系统信息函数
名字返回类型描述
current_database()name当前数据库的名字
current_schema()name当前模式的名字
current_schemas(boolean)name[]在搜索路径中的模式名字
current_username目前执行环境下的用户名
inet_client_addr()inet连接的远端址
inet_client_port()int4连接的远端端口
inet_server_addr()inet连接的本地地址
inet_server_port()int4连接的本地端口
session_username会话用户名
username等于 current_user
version()textPostgreSQL 版本信息
例:
select version();
4.切换数据库 或 切换用户
\c mydb
\c - hjzheng
5.查询当前客户端编码
\encoding
6.列出所有数据库
\l
7.列出所有表空间
\db
8.列出所有的table, index, sequence, view or 系统表
\d
\d{t|i|s|v|S} 后面的字母代表 table, index, sequence, view or 系统表
9.查看表结构
\d name (name可以是table, index, sequence, view or 系统表的name)
有时会遇到的一个错误
ERROR: column "reltriggers" does not exist at character 41
LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr...
Google说是数据库版本的问题
10.类型转换
type 'string'
'string'::type
CAST ( 'string' AS type ) 标准sql
typename ( 'string' )
在字串常量的文本将传递给那种叫 type 的类型的输入转换过程。 结果是这种类型的一个常量
例
select cast(1 as varchar);
11.组合查询 (与Oracle一样)
query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2
12.分页查询
SELECT select_list
FROM table_expression
[LIMIT { number | ALL }] [OFFSET number]
12.Serial(序号)类型
serial 和 bigserial 类型不是真正的类型, 只是为在表中设置唯一标识做的概念上的便利。(类似其它一些数据库中的 AUTO_INCREMENT 属性)。 在目前的实现中,下面一句话:
CREATE TABLE tablename (
colname SERIAL
);
等价于声明下面几句话:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);
因此,我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。 应用了一个 NOT NULL 约束以确保空值不会被明确地插入。 在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值,但这个不是自动发生的。
13.一些常用的数学函数(Oracle中都有)round(v,s) (参看 postgreSQL 8.0 手册 9.3. 数学函数和操作符)
14.一些字符串处理 (Oracle中基本也有)(参看 postgreSQL 8.0 手册 9.4. 字符串函数和操作符)
15.正则表达式
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]
例:
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a'false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false
16.时间处理 (参看 postgreSQL 8.0 手册 9.9时间/日期函数和操作符)
17.项目server端的SQL语句 修改
1.大量关于时间处理
trunc 时间
Oracle trunc(systimestamp,'hh24');
PostgreSQL date_trunc('hour',now()); 注意两个参数的位置刚好相反
时间的加减法
Oracle systimestamp + 1/24 (表示加一小时)
当然也可以使用interval语法 参看相应的文档
PostgreSQL 只有current_date 支持 current_date + 24 (加24天)
current_timestamp
current_time
now()
这三个必须使用interval方法
例:
hjzhengdb=# select current_timestamp + interval '1 day';
?column?
-------------------------------
2011-07-07 14:45:11.092478+08
(1 row)
字符串转时间 函数(用法一样) 参见 postgreSQL 8.0 手册 9.8. 数据类型格式化函数
Oracle to_timestamp
select to_timestamp('2001-10-2','yyyy-mm-dd') from dual;
PostgreSQL to_timestamp
select to_timestamp('2001-10-2','yyyy-mm-dd');
PostgreSQL 没有Oracle中的decode方法,可能要改成case when
2.大量的case when语句(这个PostgreSQL 和 Oracle 差异不大)参见 postgreSQL 8.0 手册 9.13条件表达式
3.其他的基本是标准sql
参考资料:
http://man.chinaunix.net/database/postgresql_8.0_CN/
2011/7/13 补充
18.测试sql语句性能
EXPLAIN ANALYZE
explain analyze select * from test_data;
19.如何使用sql语句查询表结构 (\d table_name 命令失效的情况)
select a.attnum, a.attname, t.typname, a.attlen
from pg_class c, pg_attribute a, pg_type t
where c.relname = 'table_name' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid
20.使用JDBC 调用Postgresql的函数
分为有返回值的和没有返回值两种:
没有返回值的按Oracle的存储过程调用方式调用。
有返回值的按Oracle中调用函数的方式调用。
参见JDBC Document
The syntax for invoking a stored procedure using the JDBC API is shown here. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.
{call procedure_name[(?, ?, ...)]}
The syntax for a procedure that returns a result parameter is:
{? = call procedure_name[(?, ?, ...)]}
The syntax for a stored procedure with no parameters would look like this:
{call procedure_name}
http://blog.donews.com/stewchicken/archive/2005/04/24/348170.aspx
存储过程的使用的例子(详见 Postgresql 7.4手册 Chapter 31. JDBC 接口)
http://www.pgsqldb.org/pgsqldoc-7.4/jdbc-callproc.html
21.事务
Postgresql中 每一条单独的sql语句就是一个事务,当sql语句执行成功,事务结束,自动commit,执行失败,自动回滚。
Postgresql函数内部无法手动控制事务,所以必须在外部进行手动控制。
start transaction;
select function();
commit;
Table 9-25. Date/Time Operators
Operator | Example | Result |
---|---|---|
+ | date '2001-09-28' + integer '7' | date '2001-10-05' |
+ | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' |
+ | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00' |
+ | time '03:00' + date '2001-09-28' | timestamp '2001-09-28 03:00' |
+ | interval '1 day' + interval '1 hour' | interval '1 day 01:00' |
+ | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' |
+ | time '01:00' + interval '3 hours' | time '04:00' |
+ | interval '3 hours' + time '01:00' | time '04:00' |
- | - interval '23 hours' | interval '-23:00' |
- | date '2001-10-01' - date '2001-09-28' | integer '3' |
- | date '2001-10-01' - integer '7' | date '2001-09-24' |
- | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' |
- | time '05:00' - time '03:00' | interval '02:00' |
- | time '05:00' - interval '2 hours' | time '03:00' |
- | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00' |
- | interval '1 day' - interval '1 hour' | interval '23:00' |
- | interval '2 hours' - time '05:00' | time '03:00' |
- | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00' |
* | double precision '3.5' * interval '1 hour' | interval '03:30' |
* | interval '1 hour' * double precision '3.5' | interval '03:30' |
/ | interval '1 hour' / double precision '1.5' | interval '00:40' |
Table 9-26. Date/Time Functions
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
age (timestamp) | interval | Subtract from today | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
age (timestamp, timestamp) | interval | Subtract arguments | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
current_date | date | Today's date; see Section 9.8.4 | ||
current_time | time with time zone | Time of day; see Section 9.8.4 | ||
current_timestamp | timestamp with time zone | Date and time; see Section 9.8.4 | ||
date_part (text, timestamp) | double precision | Get subfield (equivalent to extract ); see Section 9.8.1 | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_part (text, interval) | double precision | Get subfield (equivalent to extract ); see Section 9.8.1 | date_part('month', interval '2 years 3 months') | 3 |
date_trunc (text, timestamp) | timestamp | Truncate to specified precision; see also Section 9.8.2 | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 |
extract (field from timestamp) | double precision | Get subfield; see Section 9.8.1 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract (field from interval) | double precision | Get subfield; see Section 9.8.1 | extract(month from interval '2 years 3 months') | 3 |
isfinite (timestamp) | boolean | Test for finite time stamp (not equal to infinity) | isfinite(timestamp '2001-02-16 21:28:30') | true |
isfinite (interval) | boolean | Test for finite interval | isfinite(interval '4 hours') | true |
localtime | time | Time of day; see Section 9.8.4 | ||
localtimestamp | timestamp | Date and time; see Section 9.8.4 | ||
now () | timestamp with time zone | Current date and time (equivalent to current_timestamp ); see Section 9.8.4 | ||
timeofday() | text |
感谢整理资料的人