PostgresQL 学习记录之与oracle区别1
oracle经典架构图：
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。如果要降表空间大小，就要用另一种回收方式做VACUUM full
这时相当于把表进行重建了，这时也会锁表哦，而且是表级锁哦。我感觉就像是Oracle要降水位
线一样的。这样操作后，表的空间就会显示只有10M了呢。
（1）注意增加约束时的写法，和ORACLE略有不同
Oracle：
ALTER TABLE SCHEMA.PREFIX_INFO ADD (
CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID));
PostgresQL：
alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);
（2）系统默认的最大值与ORACLE不同
Oracle：
CREATE SEQUENCE PREFIX_INFO_SEQUENCE
INCREMENT BY 1
START WITH 582
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
CACHE 20
NOORDER;
PostgresQL：
CREATE SEQUENCE schema.prefix_info_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20;
（3）PostgresQL中的 || 用法与其他数据库不同：
select a||b from table1;
当a或b其中一个为null时，该查询返回null，
（4）PostgresQL中没有concat函数，且由于||用法的问题，无法使用||替换，解决方法为在
public schema中创建函数concat
create or replace function concat(text, text)
returns text as
$body$select coalesce($1,'') || coalesce($2,'')$body$
language 'sql' volatile;
alter function concat(text, text) owner to postgres;
--无需特殊授权即可在其他schema中使用
（4）PostgresQL中没
有dual虚拟表，为保证程序兼容性，可创建伪视图（view）替代：
CREATE OR REPLACE VIEW dual AS
SELECT NULL::"unknown"
WHERE 1 = 1;
ALTER TABLE dual OWNER TO postgres;
GRANT ALL ON TABLE dual TO postgres;
GRANT SELECT ON TABLE dual TO public;
必须授权public以select权限
（5）关联查询用法区别
ORACLE:
简单外连接：
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B
WHERE 1 = 1
AND A.COL2 = B.COL2(+)
AND A.COL3 > 0
AND A.COL4 = '1'
超级变态外连接：
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B,SCHEMA.PREFIX_TABLE3
C,SCHEMA.PREFIX_TABLE4 D
WHERE 1 = 1
AND A.COL2 = B.COL2
AND A.COL3 = C.COL3(+)
AND A.COL4 = D.COL4(+)
AND A.COL5 > 0
AND A.COL6 = '1'
POSTGRESQL:
简单外连接：
select count(l1)) as rcount from
schema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 1
l3 > 0
l4 = '1'
超级变态外连接：
select count(l1)) as rcount from
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)
left outer join schema.prefix_table3 c on (a.col3 = c.col3)
left outer join schema.prefix_table4 d on (a.col4 = d.col4)
where 1 = 1
l5 > 0
l6 = '1'
（6）PostgresQL中子查询较为规范，子查询结果集必须拥有alias
ORACLE:
SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1
) WHERE X=1 ORDER BY COL2
) WHERE Y=2 ORDER BY COL3
POSTGRESQL:
SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1 ALIAS1
) WHERE X=1 ORDER BY COL2 ALIAS2
) WHERE Y=2 ORDER BY COL3
（7） PostgresQL中没有rownum，无法使用where rownum < = X的方法进行分页，取而代
之的是limit X，offset Y方法，而ORACLE中不允许使用LIMIT X的方法
ORACLE:
SELECT * FROM ( SELECT * FROM (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY
COL1 DESC,COL2 ASC) where ROWNUM <= 50 ORDER BY COL3 ASC,COL4 DESC)
WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;
POSTGRES:
select * from ( select * from (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1
DESC,COL2 ASC) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20;
--注意！！limit必须用于order by之后
-
-例：取1到50条数据
select * from VOIP_FEE_RATE temp offset 0 limit 50
（8）序列使用的区别
ORACLE:
SELECT SCHEMA.PREFIX_TABLE1_SEQUENCE.NEXTVAL AS nCode FROM DUAL
POSTGRES:
SELECT NEXTVAL('SCHEMA.PREFIX_TABLE1_SEQUENCE') AS nCode FROM DUAL
--注意，此方法前提是dual视图已建立，如没有，可省略FROM DUAL
PostgresQL 学习记录之与oracle区别2
（9）字段取别名必须用as
Oracle -- SELECT A.COL1 A_COL1，A.COL2 A_COL2 FROM A_TABLE A
truncate的区别
Postgresql---- SELECT A.COL1 AS A_COL1，A.COL2 AS A_COL2 FROM A_TABLE A
（10）NVL用法
Oracle --SELECT NVL(SUM(VALUE11),0) FS_VALUE1, NVL(SUM(VALUE21),0) FS_VALUE2
FROM FIELD_SUM
Postgresql--SELECT COALESCE(SUM(VALUE11),0) AS
FS_VALUE1,COALESCE(SUM(VALUE21),0) AS FS_VALUE2
FROM FIELD_SUM
（11）TO_NUMBER用法
Oracle -- SELECT COL1 FROM A_TABLE ORDER BY TO_NUMBER(COL1)
Postgresql- select TO_NUMBER(COL1,'99G999D9S') from A_TABLE [注：'999999' ---- 6位
数为COL1字段的长度]
（12）DECODE用法
Oracle -- SELECT DECODE(ENDFLAG,'1','A','B') ENDFLAGFROM TEST
Postgresql- SELECT (CASE ENDFLAG WHEN '1' THEN 'A'ELSE 'B' END) AS ENDFLAG FROM TEST
select (case when check_type='01' then '1' when check_type='02' then '4' when check_type='03' then '3' else '' end) check_type from tb_msc_intr_loana_check;
select (case check_type when '01' then '1' when '02' then '4' when '03' then '3' else '' end) check_type from tb_msc_intr_loana_check;
（13）统计 相关 用法
Oracle -- SELECT ROUND(AVG(SUM(BASICCNT1))) BASICCNT FROM
ACCESS_INFO_SUM1_V
WHERE YEARCODE BETWEEN '200305' AND '200505' GROUP BY SCCODE
Postgresql-- SELECT ROUND(AVG(AIV.BASICCNT)) AS BASICCNT FROM ( SELECT
SUM(BASICCNT1) AS BASICCNT
FROM ACCESS_INFO_SUM1_V WHERE YEARCODE BETWEEN '200305' AND '200505'
GROUP BY sccode ) AIV
（14）时间计算 用法
Oracle -- SELECT CEIL(SYSDATE - TO_DATE('20051027 14:56:10','YYYYMMDD
HH24:MI:SS')) AS DAYS
FROM DUAL
Postgresql-- SELECT EXTRACT(DAY FROM
(TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS') -
TO_TIMESTAMP('2005-10-27 14:56:10','YYYY-MM-DD-HH24-MI-SS') ))+1 AS DAYS
FROM DUAL
Oracle -- add_months(date, int)
Postgresql--创建函数来解决
CREATE FUNCTION add_months(date, int)
RETURNS date AS
'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
LANGUAGE 'sql'
PostgresQL与oracle区别 3
与oracle的区别
1、数据类型区别
Oracle
PostgresQL
注释
VARCHAR2
VARCHAR （character varying）
Long、 CLOB
TEXT
DATE
DATE/TIME/TIMESTAMP
DATE仅包含日期、TIME仅包含时间、TIMESTAMP均包含，通常使用DATE
NUMBER
SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION
通常可用NUMERIC
BLOB
BYTEA
sysdate
now()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、
CURRENT_TIMESTAMP
取系统当前时间
--PostgresQL中字段名称区分大小写，为保证兼容性，强烈建议脚本中的字符均用小写，这样
在SQL语句中将忽略大小写
--PostgresQL中字段类型的括号中只能出现数字
UNION、EXCEPT 以及 INTERSECT
1、用集合运算符组合查询
UNION、EXCEPT 以及 INTERSECT 集合运算符使您能够将两个或更多外层查询组合成单个
查询。执行用这些集合运算符连接的每个查询并组合各个查询的结果。根据运算符不同，产生不
同的结果。
2、UNION 运算符
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行
而
派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。两种情
况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
3、EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生
出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
4、INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出
一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
5、当使用 UNION、EXCEPT 以及 INTERSECT 运算符时，记住下列事项:
运算符的查询选择列表中的所有对应项必须是相容的。有关更多信息，参见 SQL Reference
中的数据类型相容性表。
ORDER BY 子句(如果使用该子句的话)必须放在最后一个带有集合运算符的查询后面。对于
每个运算符来说，如果列的名称与查询的选择列表中对应项的名称相同，则该列名只能在
ORDER BY 子句中使用。
各个表的数据量统计
1、
create or replace function count_rows(table_name in varchar2,
owner in varchar2 default null)
return number
authid current_user
IS
num_rows number;
stmt varchar2(2000);
begin
if owner is null then
stmt := 'select count(*) from "'||table_name||'"';
else
stmt := 'select count(*) from "'||owner||'"."'||table_name||'"';
end if;
execute immediate stmt into num_rows;
return num_rows;
end;
2、然后通过计算函数进行统计
select table_name, count_rows(table_name) nrows from user_tables order by nrows
desc
select * from pg_user;
select lkind from pg_class pg;
select * from pg_class pg lkind = 'r' and relnamespace = '';--查看中所有表
select * from pg_proc where pronamespace = '41613';--查看中所有函数或存储过程
select 'truncate table '||relname||';' from pg_class pg lkind = 'r' and relnamespace = '';
/**超级用户下执行*/
drop ROLE batchloan;
-- Role: batchloan
-- DROP ROLE batchloan;
CREATE ROLE batchloan LOGIN
ENCRYPTED PASSWORD 'batchloan'
SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
drop TABLESPACE batchloan;
-
- Tablespace: batchloan
-- DROP TABLESPACE batchloan
CREATE TABLESPACE batchloan
OWNER batchloan
LOCATION 'D:\PostgreSQL\batchloan\';
/**错误: 无法为目录 "D:/PostgreSQL/batchloan" 的设置权限: Permission denied
********** 错误 **********
错误: 无法为目录 "D:/PostgreSQL/batchloan" 的设置权限: Permission denied
SQL 状态: 42501*/
drop DATABASE batchloan;
-- Database: batchloan
-
- DROP DATABASE batchloan;
CREATE DATABASE batchloan
WITH OWNER = batchloan
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'zh_HK.utf8'
LC_CTYPE =
'zh_HK.utf8'
CONNECTION LIMIT = -1;
drop VIEW dual;
CREATE OR REPLACE VIEW dual AS
SELECT NULL::"unknown"
WHERE 1 = 1;
ALTER TABLE dual OWNER TO batchloan;
GRANT ALL ON TABLE dual TO batchloan;
GRANT SELECT ON TABLE dual TO public;
drop sequence id_sequence;
create sequence id_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20;
select nextval('id_sequence');--序列用法和Oracle不同(select val from dual;)
select currval('id_sequence');
select * from dual offset 0 limit 50;
select coalesce(null,0);--Oracle中函数nvl
select to_number('12','99G999D9S');
--PostgreSQL日期运算
select now()+interval '1' year;
select now()+interval '2' month;
select now()+interval '3' day;
select now()+interval '4' hour;
select now()+interval '5' minute;
select now()+interval '6' second;
select now()+interval '7' ms;
select now()+interval '1 years 2 month 3 day 4 hour 5 minute 6 second 7ms';
--oracle日期运算
select sysdate+interval '1' year from dual;
select sysdate+interval '1' month from dual;
select sysdate+3 from dual;
select sysdate+interval '1' hour from dual;
select sysdate+interval '1' minute from dual;
select sysdate+interval '1' second from dual;
-
-表修改操作 tb_test_table：表名、desccription：字段名
alter table tb_test_table add column desccription varchar(10);--新增字段
alter table tb_test_table drop column desccription;--删除字段
alter table tb_test_table alter column desccription type varchar(10);--修改字段类型
alter table tb_test_table rename column id to tt_id;--修改字段名称
alter table tb_test rename to tb_test_table;--修改表名
ALTER TABLE tb_i_lon_loan ADD PRIMARY KEY (loan_id);--添加主键
--通过copy抽取数据生成文件
psql -h ${hostname} -d ${database} -U ${username} <<EOF > ${logFile} 2>&1
\encoding 'gb18030'
\copy ${table_name}${column_name} to '${BASEDATDIR}/${from_to}_${table_name}_${runDate}.csv' with CSV QUOTE '"' DELIMITER '|' null AS '' ESCAPE '\'
\q
EOF
--通过copy把文件中的数据加载到表中
psql -h ${hostname} -d ${database} -U ${username} <<EOF > ${logFile} 2>&1
truncate table ${table_name};
\encoding 'gb18030'
\copy ${table_name}${column_name} from '${BASEDATDIR}/${from_to}_${table_name}_${runDate}.csv' with CSV QUOTE '"' DELIMITER '|' null AS '' ESCAPE '\'
\q
EOF
--调用无返回值存储过程或函数
PERFORM pro_name('参数1'，'参数2');
PERFORM function_name('参数1'，'参数2');
--调用有返回值存储过程或函数
select pro_name('参数1'，'参数2') into 返回值;
--获取上次操作更新记录条数（在存储过程或者函数中使用）
GET DIAGNOSTICS COUT = ROW_COUNT;
--打印日志（在存储过程或者函数中使用）
RAISE NOTICE '存储过程<%>开始执行, 执行步骤为:%', O_PRO_NAME, V_STEP ;
-- now() 与 clock_timestamp() 函数区别
在一个存储过程中 now() 函
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论