注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

星期五

明天不上班

 
 
 

日志

 
 
关于我

一个特立独行的Java程序员,比较宅,上上网,写博客,听音乐,看电影。

网易考拉推荐

PostgreSQL手册学习笔记  

2011-07-06 20:05:43|  分类: Postgresql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

最近,项目要从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

http://download.oracle.com/javase/6/docs/technotes/guides/jdbc/getstart/callablestatement.html#999651

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;

  评论这张
 
阅读(354)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017