PostgresQL与oracle区别

PostgresQL学习记录之与oracle区别1oracle经典架构图：PostgreSQL架构图：可以看出来Oracle和pg的架构很相似的哦，不过pg里面没有了，Oracle中undo日志的概念了，而Oracle的redo日志在pg里面叫wal日志额，还有我理解oracle中一个connection可以创建对个session的，但在pg中connection和session应该是一个级别的了（这点望大牛们指教）。大家都熟悉Oracle的process结构，来看看pg的咯：再来对比下他们的存储结构：pg的存储结构简单多了只有文件和block的概念了，我理解这里文件应该还是一个逻辑概念，一般一个表对应一个到多个segment文件,一个segment文件包含多个block，一般一个segment为1G，block为8k(但仍那可以自己调整设置)。其实这里条有的话就没有Oracle那么复杂了，但是也会导致存储没有Oracle那么科学了。最后看看如何实现多版本的：这个图很重要啊，两个数据的实现事务性的方式不一样啊，pg中的update是只想一个文件里面插数据，然后把以前的数据打上失效的标记。到一定的量时进行垃圾回收。回收有两种一种analysis，这个只是把以前标记为不可用的空间设为空闲，那么在有新数据插入时可以复写这些空间，但是analysis后表的存储大小不变，表内可能只有10M数据，但是原来有10M垃圾被标记为空闲，这是表大小还是20M。如果要降表空间大小，就要用另一种回收方式做VACUUMfull这时相当于把表进行重建了，这时也会锁表哦，而且是表级锁哦。我感觉就像是Oracle要降水位线一样的。这样操作后，表的空间就会显示只有10M了呢。（1）注意增加约束时的写法，和ORACLE略有不同Oracle：ALTERTABLESCHEMA.PREFIX_INFOADD(CONSTRAINTPK_PREFIX_INFOPRIMARYKEY(INFO_ID));PostgresQL：altertableschema.prefix_infoaddconstraintprefix_info_pkeyprimarykey(info_id);（2）系统默认的最大值与ORACLE不同Oracle：CREATESEQUENCEPREFIX_INFO_SEQUENCEINCREMENTBY1STARTWITH582MINVALUE1MAXVALUE9999999999999999999999999999NOCYCLECACHE20NOORDER;PostgresQL：CREATESEQUENCEschema.prefix_info_sequenceincrement1minvalue1maxvalue9223372036854775807start582cache20;（3）PostgresQL中的||用法与其他数据库不同：selecta||bfromtable1;当a或b其中一个为null时，该查询返回null，（4）PostgresQL中没有concat函数，且由于||用法的问题，无法使用||替换，解决方法为在publicschema中创建函数concatcreateorreplacefunctionconcat(text,text)returnstextas$body$selectcoalesce($1,'')||coalesce($2,'')$body$language'sql'volatile;alterfunctionconcat(text,text)ownertopostgres;--无需特殊授权即可在其他schema中使用（4）PostgresQL中没有dual虚拟表，为保证程序兼容性，可创建伪视图（view）替代：CREATEORREPLACEVIEWdualASSELECTNULL::unknownWHERE1=1;ALTERTABLEdualOWNERTOpostgres;GRANTALLONTABLEdualTOpostgres;GRANTSELECTONTABLEdualTOpublic;必须授权public以select权限（5）关联查询用法区别ORACLE:简单外连接：SELECTCOUNT(DISTINCT(A.COL1))ASRCOUNTFROMSCHEMA.PREFIX_TABLE1A,SCHEMA.PREFIX_TABLE2BWHERE1=1ANDA.COL2=B.COL2(+)ANDA.COL30ANDA.COL4='1'超级变态外连接：SELECTCOUNT(DISTINCT(A.COL1))ASRCOUNTFROMSCHEMA.PREFIX_TABLE1A,SCHEMA.PREFIX_TABLE2B,SCHEMA.PREFIX_TABLE3C,SCHEMA.PREFIX_TABLE4DWHERE1=1ANDA.COL2=B.COL2ANDA.COL3=C.COL3(+)ANDA.COL4=D.COL4(+)ANDA.COL50ANDA.COL6='1'POSTGRESQL:简单外连接：selectcount(distinct(a.col1))asrcountfromschema.prefix_table1aleftouterjoinschema.prefix_table2bon(a.col2=b.col2)where1=1anda.col30anda.col4='1'超级变态外连接：selectcount(distinct(a.col1))asrcountfromschema.prefix_table1ainnerjoinschema.prefix_table2bon(a.col2=b.col2)leftouterjoinschema.prefix_table3con(a.col3=c.col3)leftouterjoinschema.prefix_table4don(a.col4=d.col4)where1=1anda.col50anda.col6='1'（6）PostgresQL中子查询较为规范，子查询结果集必须拥有aliasORACLE:SELECT*FROM(SELECT*FROM(SELECT*FROMSCHEMA.PREFIX_TABLEORDERBYCOL1)WHEREX=1ORDERBYCOL2)WHEREY=2ORDERBYCOL3POSTGRESQL:SELECT*FROM(SELECT*FROM(SELECT*FROMSCHEMA.PREFIX_TABLEORDERBYCOL1ALIAS1)WHEREX=1ORDERBYCOL2ALIAS2)WHEREY=2ORDERBYCOL3（7）PostgresQL中没有rownum，无法使用whererownum=X的方法进行分页，取而代之的是limitX，offsetY方法,而ORACLE中不允许使用LIMITX的方法ORACLE:SELECT*FROM(SELECT*FROM(SELECT*FROMSCHEMA.PREFIX_TABLE1ORDERBYCOL1DESC,COL2ASC)whereROWNUM=50ORDERBYCOL3ASC,COL4DESC)WHEREROWNUM=20ORDERBYCOL5DESC,COL6ASC;POSTGRES:select*from(select*from(SELECT*FROMSCHEMA.PREFIX_TABLE1ORDERBYCOL1DESC,COL2ASC)selborderbycol3asc,col4desclimit50)selaorderbycol5desc,col6asclimit20;--注意！！limit必须用于orderby之后--例：取1到50条数据select*fromVOIP_FEE_RATEtempoffset0limit50（8）序列使用的区别ORACLE:SELECTSCHEMA.PREFIX_TABLE1_SEQUENCE.NEXTVALASnCodeFROMDUALPOSTGRES:SELECTNEXTVAL('SCHEMA.PREFIX_TABLE1_SEQUENCE')ASnCodeFROMDUAL--注意，此方法前提是dual视图已建立，如没有，可省略FROMDUALPostgresQL学习记录之与oracle区别2（9）字段取别名必须用asOracle--SELECTA.COL1A_COL1，A.COL2A_COL2FROMA_TABLEAPostgresql----SELECTA.COL1ASA_COL1，A.COL2ASA_COL2FROMA_TABLEA（10）NVL用法Oracle--SELECTNVL(SUM(VALUE11),0)FS_VALUE1,NVL(SUM(VALUE21),0)FS_VALUE2FROMFIELD_SUMPostgresql--SELECTCOALESCE(SUM(VALUE11),0)ASFS_VALUE1,COALESCE(SUM(VALUE21),0)ASFS_VALUE2FROMFIELD_SUM（11）TO_NUMBER用法Oracle--SELECTCOL1FROMA_TABLEORDERBYTO_NUMBER(COL1)Postgresql-selectTO_NUMBER(COL1,'99G999D9S')fromA_TABLE[注：'999999'----6位数为COL1字段的长度]（12）DECODE用法Oracle--SELECTDECODE(ENDFLAG,'1','A','B')ENDFLAGFROMTESTPostgresql-SELECT(CASEENDFLAGWHEN'1'THEN'A'ELSE'B'END)ASENDFLAGFROMTESTselect(casewhencheck_type='01'then'1'whencheck_type='02'then'4'whencheck_type='03'then'3'else''end)check_typefromtb_msc_intr_loana_check;select(casecheck_typewhen'01'then'1'when'02'then'4'when'03'then'3'else''end)check_typefromtb_msc_intr_loana_check;（13）统计相关用法Oracle--SELECTROUND(AVG(SUM(BASICCNT1)))BASICCNTFROMACCESS_INFO_SUM1_VWHEREYEARCODEBETWEEN'200305'AND'200505'GROUPBYSCCODEPostgresql--SELECTROUND(AVG(AIV.BASICCNT))ASBASICCNTFROM(SELECTSUM(BASICCNT1)ASBASICCNTFROMACCESS_INFO_SUM1_VWHEREYEARCODEBETWEEN'200305'AND'200505'GROUPBYsccode)AIV（14）时间计算用法Oracle--SELECTCEIL(SYSDATE-TO_DATE('2005102714:56:10','YYYYMMDDHH24:MI:SS'))ASDAYSFROMDUALPostgresql--SELECTEXTRACT(DAYFROM(TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS')-TO_TIMESTAMP('2005-10-2714:56:10','YYYY-MM-DD-HH24-MI-SS')))+1ASDAYSFROMDUALOracle--add_months(date,int)Postgresql--创建函数来解决CREATEFUNCT