Database migration oracle to postgres : Data-types

Few tips - Database migration oracle to postgres : Datatypes

Oracle vs PostgreSQL
Oracle	Postgres
Data Types
clob	text
number	numeric
varchar2	character varying
sysdate	current_date
Value()	COALESCE()
XML / CLOB Synthesis
xmlTypeVariable.getClobVal()	cast(xmlTypeVariable AS TEXT)          or         xmlTypeVariable::TEXT
xml.extract('/Val1/Val2/@Val3').genumericval()	tempNum := (xpath('/Val1/Val2/@Val3/text()',xml))[1];                     cast(tempNum AS NUMERIC);
xml.extract('/Val1/Val2/@Val3').getstringval()	(xpath('/Val1/Val2/@Val3/text()',xml)[1];
xml.extract('/Val1/').getstringval()	(xpath('/Val1/node()',xml)[1];
dbms_output.put_line('text' || variablename)	Raise notice 'text %',variablename; Raise debug 'text %',variablename;
TYPE Conversion and Functions
to_char(var1)	cast(var1 AS TEXT) or var1::character varying
replace('string', 'text')	replace('string', 'text', '')
DECODE(PT.ISNUMERIC,0,'No','Yes')	(select (   CASE WHEN PT.ISNUMERIC = 0 THEN 'No'             ELSE 'Yes' END))
to_number(stuff)	stuff::numeric
instr(varchar, varchar)	"user"."INSTR" (character varying, character varying)
instr(varchar, varchar, integer)	"user"."INSTR" (character varying, character varying, integer)
instr(varchar, varchar, integer, integer)	"user"."INSTR" (character varying, character varying, integer, integer)
Sequence Related
SOME_SEQ.nextval	nextval('"user"."SOME_SEQ"')
select SOME_SEQ.nextval INTO v_Varriable from DUAL;	v_Varriable = nextval('"user"."SOME_SEQ"');
sequence_name.currentval	currval('"user".sequence_name')
PL/SQL and Exception Handling
Exception       When Others Then         Dbms_Output.Put_line(Sqlcode || Sqlerrm);	exception when others then raise notice 'EXCEPTION: (%)',SQLSTATE;
Dbms_Output.put_line(Sqlcode || Sqlerrm );	RAISE NOTICE 'Truncating table test_table: % %', SQLSTATE, SQLERRM;
IF SQL%ROWCOUNT = 0 THEN //stuff	IF NOT FOUND THEN          or         GET DIAGNOSTICS integer_var = ROW_COUNT;         IF integer_var = 0 THEN
MINUS	EXCEPT
REGEXP_LIKE(CHARACTER VARYING , NUMERIC, NUMERIC, PATTERN) e.g.REGEXP_LIKE(SUBSTR(ag.agreementname, 1, 1), '[:0123456789ABCDEF:]')	(CHARACTER VARYING) LIKE (PATTERN) (SUBSTR(ag.agreementname, 1, 1) LIKE '[:STUVWXYZ:]')
NULL processing
nvl(stuff, 0)	COALESCE(stuff, '0')::numeric
nvl(val1, val2)	coalesce(val1,val2)
nvl2(PEM.CONTRACT_FROM::character varying,'DIRECT','CONTRACT')	COALESCE(stuff as date type::character varying,'DIRECT','CONTRACT')
	How to check for null:         IF var1 IS NULL THEN          //stuff         END IF;
Working with Rownum
where rownum <= 25 order by [column_name];	order by [column_name] limit 25;
rownum 5	order by [column_name] limit 1 offset 5
SELECT var1 FROM table1 WHERE thing1 = thing2 AND thing3 = thing4 AND rownum = 1	SELECT var1 FROM table1 ORDER BY var1 LIMIT 1 WHERE thing1 = thing2 AND thing3 = thing4 AND
Working with DATEs
TRUNC(SYSDATE)	DATE_TRUNC('day',CURRENT_DATE)
Last_day(date )	"user"."LAST_DAY"(date)
ADD_MONTHS(DATE, MONTHS)	"user"."ADD_MONTHS"(DATE, MONTHS)
months_between(d1,d2)	date_part('Year',age(d1, d2)) * 12 + date_part('Month',age(d1, d2));
JOINS
select a.field1, b.field2 from a, b where a.item_id = b.item_id(+)	select a.field1, b.field2 from a, b where a.item_id = b.item_id UNION select a.field1, NULL as field2 from a where 0= (select count(*) from b where b.item_id=a.item_id)