Oracle migration to PostgreSQL

The public no. :WarmSmile

preface

Due to a recent data governance project, the company needed to migrate from its existing Oracle database to a PostgreSQL database.

The main complex changes are to views, functions, stored procedures, and Mapper files in the project.

Therefore, I sorted out the problems encountered in the process of migration, so that the future can be used for reference.

Tool migration

If the views, functions, stored procedures, and Mapper files used in the project are not too complex,

You can use the open source tool Ora2pg

Manual migration

• Oracle vs. PostgreSQL

  type	Oracle	PostgreSQL
  function	Sysdate, systimestamp	Current_date, current_time, current_timestamp, now
  	sys_guid	There is a function uuid_generate_V4 similar to sys_guid, but need to install, create extension “uuID-oosp “.
  	nvl	coalesce
  	rownum	row_number() over()
  	WORKER_NODE_ID_SEQ.NEXTVAL	NEXTVAL(‘WORKER_NODE_ID_SEQ’)
  	to_date	to_timestamp
  	DATE_FORMAT	TO_CHAR
  	REGEXP_SUBSTR	regexp_replace
  	DECODE	(case when field = 0 then result 1 else result 2 end)
  	list_agg	string_agg
  	SYS_CONNECT_BY_PATH	concat_ws
  grammar	SELECT … START WITH conditional 1 CONNECT BY PRIOR conditional 2	WITH RECURSIVE TEMP AS ( SELECT … Where 1 UNION ALL SELECT… SELECT * FROM TEMP
  	ROWNUM = 1	LIMIT 1
  	“xx”	‘xx’ (double quotation marks for fields)

• Problems you might encounter

  The problem	To solve
  Caused by: org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias	Sub-query tables need aliases
  recursive query “t” column 7 has type character varying(300) in non-recursive term but type character varying overall	Use the CAST(field as type) function
  START WITH CONNECT BY PRIOR	Use WITH RECURSIVE
  Need to display type conversions	Use the cast function or the field :: type

conclusion

At the beginning of the migration will be difficult to start with, a lot of syntax do not understand, and the amount of business SQL code is relatively large, some SQL almost hundreds of lines.

But as you get started, you can change very quickly, mainly because it takes a long time to understand what SQL does.

Look at the specific needs of their own projects, I may not be very complete here.

Past is the past. Today is a new day. The past is the past. Today is a new day.