最近一个项目用到PostgresSQl数据库，有些不同于oracle数据库，尤其时间处理上，结合PostgreSQL手册以及网上转载些资料，如下，方便开发中用到

最新推荐文章于 2024-04-23 10:32:10 发布
最新推荐文章于 2024-04-23 10:32:10 发布
阅读量1.1k 收藏 2
点赞数
文章标签： 数据库 postgresql oracle date integer table

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

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;

 

Table 9-25. Date/Time Operators

 

OperatorExampleResult
+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

FunctionReturn TypeDescriptionExampleResult
age(timestamp)intervalSubtract from todayage(timestamp          '1957-06-13')43 years 8 mons 3 days
age(timestamp,          timestamp)intervalSubtract argumentsage('2001-04-10', timestamp          '1957-06-13')43 years 9 mons 27 days
current_datedateToday's date; see Section          9.8.4  
current_timetime with time zoneTime of day; see Section          9.8.4  
current_timestamptimestamp with time zoneDate and time; see Section          9.8.4  
date_part(text,          timestamp)double precisionGet subfield (equivalent to extract); see Section          9.8.1date_part('hour', timestamp          '2001-02-16 20:38:40')20
date_part(text,          interval)double precisionGet subfield (equivalent to extract); see Section          9.8.1date_part('month', interval '2          years 3 months')3
date_trunc(text,          timestamp)timestampTruncate to specified precision; see also Section          9.8.2date_trunc('hour', timestamp          '2001-02-16 20:38:40')2001-02-16 20:00:00
extract(field from timestamp)double precisionGet subfield; see Section          9.8.1extract(hour from timestamp          '2001-02-16 20:38:40')20
extract(field from interval)double precisionGet subfield; see Section          9.8.1extract(month from interval '2          years 3 months')3
isfinite(timestamp)booleanTest for finite time stamp (not equal to          infinity)isfinite(timestamp '2001-02-16          21:28:30')true
isfinite(interval)booleanTest for finite intervalisfinite(interval '4          hours')true
localtimetimeTime of day; see Section          9.8.4  
localtimestamptimestampDate and time; see Section          9.8.4  
now()timestamp with time zoneCurrent date and time (equivalent to current_timestamp); see Section          9.8.4  
timeofday()text

 

 

感谢整理资料的人

 

guangfei1987
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Postgres数据库SQL注入手册1
08-08
Postgres数据库SQL注入手册1
ERROR: column c.relhasoids does not exist
热门推荐
小白一个
12-12 1万+
背景： 链接 PostgreSQL显示如下错误： 解释： 你当前的数据库版本（PostgreSQL）和你链接工具（Navicat 或者 IDEA）版本不一致造成的，往往来说，是链接工具版本过低，或者是数据库版本过高造成的。 解决： 首先查看数据库的版本是多少？ 解决办法A：降低PostgrelSQL版本，如你之前的是12版本，可以换成11版本； 解决办法B：提高链接工...
【PostgreSQL】PostgreSQL实现Oracle的decode函数功能
tttzzzqqq2018的博客
01-09 1103
【代码】【PostgreSQL】PostgreSQL实现Oracle的decode函数功能。
Postgresql 类似oracle的decode
liubl2011的专栏
11-10 1万+
oracle的decode大家都很熟悉，现在用postgresql的数据库好像没有类似的方法，在网上找了下有种写法也类似。 [code="sql"] select (case when order_count = 0 then 1 else order_count end) as ordercount from order [/code] case when 当数据...
Postgre 遇到 column “xxxxxx“ does not exist解决办法
zkh136的博客
08-17 5961
今天在写PostgreSQL语句时候发现运行这个SQL总是报错 PostgreSQL会认为双引号是“名称”，如表名，字段名等， 而被单引号括起来的就表示值。 于是 改成单引号就可以了 完美解决 正常运行！ ...
postgresql学习
weixin_33742618的博客
03-30 120
如何查询数据库表的大小playstore_log=> select pg_size_pretty(pg_relation_size('playstore_logs_browse')); pg_size_pretty ---------------- 15 GB(1 row)查询数据库的大小playstore_log=> select pg_size_pretty(...
记一次 删除 PostgresSql 数据库 报错：有 N 个其它会话正在使用数据库 的解决方案
12-14
一、数据库搭建 1、yum 指定目录安装 https://blog.csdn.net/llwy1428/article/details/105143053 2、yum 直接安装 https://blog.csdn.net/llwy1428/article/details/102486414 3、编译安装 ...4、PostgreSql 基本操作 ...
IDEA连接postgressql数据库操作
09-07
主要介绍了IDEA连接postgressql数据库操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
PostGresSQL数据库
09-27
PostGresSQL数据库 window64位 版本是15.4-1
SQLPro for Postgres 2023.10 PostgreSQL数据库管理客户端
06-04
SQLPro Postgres是先进但轻量级的Postgres数据库管理工作室。它允许快速和简单的访问Postgres SQL服务器，采用先进的节省时间的功能。 特征 语法高亮 智能感知 标签式界面 支持同时执行多个查询 快速访问表、列和...
PostgreSQL和Oracle用法上的一些区别
朝着梦想 渐行前进
04-22 5650
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE
postgre定时备份
permike的专栏
11-10 476
需要切换用户postgres, pg_dump命令 ----- 脚本： #!/bin/bash # /backup.sh cur-time=`date +'%Y%m%d'` pg_dump > /backup.dump echo 'postgre backup finished' ---- 使用crontab定时 crontab -e编辑配置文件 0 0 * * *
Shell脚本定时清理Postgres数据库中历史数据
wssy2581456696的专栏
12-07 4618
Shell脚本定时清理Postgres数据库中历史数据 在系统日常运行中，会产生大量的日志，日志表会越来越加庞大。特别是对于云服务器使用者来说，每一块的空间都是很宝贵的。所以定时清理掉无用的历史日志，就显得很有意义了。其实实现数据库的日志清楚，有很多方法，最简单的就是配置一个数据库定时任务，定时删除旧数据就行。但是数据库定时器缺乏灵活性，如果需要修改时间节点。就需要重新修改编译定时器，对于维护人...
postgresql pgAgent 定时任务
懒猫的专栏
04-27 6727
postgresql 安装pgAgent后，新建作业，一直是失败，查看作业统计量，显示 问题： Couldn't get a connection to the database 搜索发现，作业连接串需要 读取 pgpass.conf 中的用户名及密码。我查看我的文件发现有两个（我安装2个版本的postgresQL）： 下面配置文件中缺少记录，添加所用的连接的用户名和密码，
【kettle001】访问国产达梦数据库并处理数据至execl文件
kngines
04-22 256
一直以来想写下基于kettle的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。熟悉、梳理、总结下达梦（DM）关系型数据库相关知识体系。
mysql和Nosql到底有什么区别，分别应用与什么场景？
m0_60388871的博客
04-20 624
MySQL 和 NoSQL 是两种不同类型的数据库技术，它们各有其特点和适用场景。了解它们之间的区别和应用场景可以帮助选择合适的技术来支持特定的应用需求。
mysql-connector-java和spring-boot-starter-jdbc和mybatis-spring-boot-start
最新发布
xzy的博客
04-23 463
JDBC是使用java语言操作mysql数据库的规范，java语言必须按照这个规范写才可以操作mysql数据库。
表和索引分片
Sinoregal的博客
04-18 903
是将一个表中的数据分布到不同的dbspace中。SinoDB数据库支持智能化横向的表和索引分区，并将其称之为表和索引分片。分片允许您创建一个表，该表在SQL语句中被视为单个表，但由多个tbspaces组成。正常的分片要求每个 dbspace 中有一个片段。这有效地将较大的表划分为若干较小的 tablespaces，因为一个 tablespace 不能跨 dbspace。新增保留关键字分区。则允许来自一个分片表的多个片段共存于同一个 dbspace。
我要把postgres的数据库复制到另一个服务器上
07-11
您可以使用以下步骤将 PostgreSQL 数据库复制到另一个服务器上： 1. 在源服务器上创建数据库备份： ``` pg_dump -U <用户名> -h <源服务器IP> -p <端口号> -F c -b -v -f <备份文件名> <数据库名> ``` 这将创建...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值