oracle, postgreSQL - migration

	Oracle	postgreSQL
Data Type	NUMBER(p)	SMALLINT - 2 bytes
		INTEGER - 4 bytes
		BIGINT - 8 bytes
	NUMBER(p,s)	NUMERIC(p,s)
		REAL – 4 bytes, 6 decimal places
		DOUBLE PRECISION – 8 bytes, 15 decimal places
	X	SERIAL
	VARCHAR2(size) where maximum size is 4000	CHARACTER VARYING(n) where maximum n is 1 GB VARCHAR(n) is an alias
	CHAR(size) where maximum size is 2000	CHARACTER(n) where maximum n is 1 GB CHAR(n) is an alias It’s suggested that you use TEXT if n > 10 MB
	LONG - Character data of variable length up to 2 GB	TEXT – variable length up to 1 GB
	DATE – holds date and time	TIMESTAMP
	TIMESTAMP
	X	DATE – holds only the date
	X	TIME – holds only the time (00:00:00.00 – 23:59:59.99)
	RAW(size) – binary data of length size bytes (max 2000) LONG RAW – binary data or variable length up to 2GB	BYTEA
	X	BIT(n) – fixed length string of 1’s and 0’s BIT VARYING(n) – variable length string of 1’s and 0’s
	CLOB – character large object (max 4GB)	TEXT (max 1GB)
	BLOB – binary large object (max 4GB)	BYTEA (max 1GB)
	ROWID	X
	X	BOOLEAN
	X	Network address data types: INET, MACADDR, CIDR.
SQL Syntax, Functions, Sequences, Etc.	select sysdate from dual	select ‘now’::datetime
	CREATE SEQUENCE seqname [ INCREMENT BY integer ] [ MINVALUE integer ] [ MAXVALUE integer ] [ START WITH integer ] [ CACHE integer ] [ CYCLE | NOCYCLE ]	CREATE SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ]
	sequence_name.nextval;	nextval(‘sequence_name’);
	select sequence_name.nextval from dual;	select nextval(‘sequence_name’);
	SELECT product_id,        DECODE (warehouse_id, 1, ’Southlake’,                              2, ’San Francisco’,                              3, ’New Jersey’,                              4, ’Seattle’,                              ’Non-domestic’)        quantity_on_hand  FROM inventories	SELECT a,         CASE WHEN a=1 THEN 'one'              WHEN a=2 THEN 'two'              ELSE 'other'         END  FROM test
	select employeeid,        NVL(hire_date, sysdate) from employee where employeeid = 10;	select employeeid,        coalesce(hire_date, 'now'::datetime) from employee  where employeeid = 10;
	Outer join – “(+)”	X
	Hierarchical queries – “CONNECT BY”	X
	SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items;	SELECT product_id FROM inventories EXCEPT SELECT product_id FROM order_items;
	select unique col1, col2 from table1	select distinct col1, col2 from table1
	select id,name from employee where id >    = 10; // There are spaces and tabs between “>” and “=”	select id,name from employee where id >= 10;
	select mod(10,4) from dual;	select 10 % 4;
	select * from employees where rownum < 10 order by name;	select * from employees order by name limit 10;
	select rownum, name from employees order by name;	select oid, name from employees order by name;