PostgreSQLºÍOracleÓ÷¨ÉϵÄһЩÇø±ð

£¨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(distinct(a.col1)) as rcount from
schema.prefix_table1 a left ou