| | M | timestamp |
- **Binary**
|Oracle database Data type |Remarks |Migratability |PostgreSQL Data type |Remarks |
| :--- | :--- | :---: | :--- | :--- |
| BFILE | | MR | bytea
Large object | Up to 1 GB (bytea)
Up to 4 TB(Large object) |
| BLOB
BINARY LARGE OBJECT | | MR | bytea
Large object | Up to 1 GB (bytea)
Up to 4 TB(Large object) |
| LONG RAW | | MR | bytea
Large object | Up to 1 GB (bytea)
Up to 4 TB(Large object) |
| RAW | | M | bytea | |
| ROWID | | M | oid |
| UROWID | | N | | |
- **Other**
|Oracle database Data type |Remarks |Migratability |PostgreSQL Data type |Remarks |
| :--- | :--- | :---: | :--- | :--- |
| ANYDATA | | N | | |
| ANYDATASET | | N | | |
| ANYTYPE | | N | | |
| DBUriType | | N | | |
| HTTPUriType | | N | | |
| MLSLABEL | | N | | |
| ORDAudio | | N | | |
| ORDDicom | | N | | |
| ORDDoc | | N | | |
| ORDImage | | N | | |
| ORDVideo | | N | | |
| REF data type | | N | | |
| SDO_GEOMETRY | | N | | |
| SDO_GEORASTER | | N | | |
| SDO_TOPO_GEOMETRY | | N | | |
| SI_AverageColor | | N | | |
| SI_Color | | N | | |
| SI_ColorHistogram | | N | | |
| SI_FeatureList | | N | | |
| SI_PositionalColor | | N | | |
| SI_StillImage | | N | | |
| SI_Texture | | N | | |
| URIFactory package | | N | | |
| URIType | | N | | |
| VARRAY | | M | Array type | |
| XDBUriType | | N | | |
| XMLType | | M | XML type | |
| Object type | | N | | |
| Nested table | | N | | |
Y: Data type can be migrated as is
M: Modified data type can be migrated
N: Cannot be migrated
YR: Data type can be migrated as is with restrictions
MR: Modified data type can be migrated with restrictions
#### 2.2.2 Examples of Migrating Data Types
##### 2.2.2.1 Examples of Migrating General Data Types
**Description of migration**
Refer to "Data type correspondence" and change Oracle database data types to PostgreSQL data types.
**Migration example**
| Oracle database |
PostgreSQL |
CREATE TABLE t1( col1 SMALLINT,
col2 VARCHAR2(10),
col3 NVARCHAR2(10),
col4 DATE,
col5 NUMBER(10),
col6 RAW(2000),
col7 BLOB );
|
CREATE TABLE t1( col1 SMALLINT,
col2 VARCHAR(10),
col3 NCHAR VARYING(10),
col4 TIMESTAMP,
col5 INTEGER,
col6 BYTEA,
col7 BYTEA );
|
##### 2.2.2.2 NUMBER Type
**Functional differences**
- **Oracle database**
- A negative value can be specified in a NUMBER type scale.
Any value that is specified in a scale beyond the number of significant digits is rounded off.
- **PostgreSQL**
- A negative value cannot be specified in a NUMERIC type scale.
Any value that is specified in a scale beyond the number of significant digits is discarded.
**Migration procedure**
Use the following procedure to perform migration:
1. Change DECIMAL scales to 0, and add the number of changed digits to the precision.
2. Create a function that uses the ROUND function to round off the column that was changed in Step (1) above.
3. Create a trigger that executes the function created in Step (2) above when the INSERT statement and UPDATE statement are executed.
**Migration example**
The example below shows how to migrate the NUMBER type.
| Oracle database |
PostgreSQL |
CREATE TABLE t1( col1 SMALLINT,
col2 NUMBER(10,-2) );
INSERT INTO t1 VALUES( 11, 1234567890 );
SELECT * FROM t1;
|
CREATE TABLE t1( col1 SMALLINT,
col2 NUMERIC(12,0) );
CREATE FUNCTION f1() RETURNS TRIGGER AS $$
BEGIN
NEW.col2 := ROUND(NEW.col2,-2);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER g1 BEFORE INSERT OR UPDATE ON t1
FOR EACH ROW
EXECUTE PROCEDURE f1();
INSERT INTO t1 VALUES( 11, 1234567890 );
SELECT * FROM t1;
|
### 2.3 Pseudocolumns
This section explains pseudocolumns.
#### 2.3.1 CURRVAL
**Description**
CURRVAL returns the value nearest to that obtained by NEXTVAL from the sequence in the current session.
**Functional differences**
- **Oracle database**
- The sequence name is specified as sequenceName.CURRVAL.
- **PostgreSQL**
- The sequence name is specified as CURRVAL('sequenceName').
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the keyword CURRVAL and identify where it is used.
2. Change the sequence name specification by placing it after CURRVAL and enclosing it in parentheses and single quotation marks.
**Migration example**
The example below shows how to migrate CURRVAL.
| Oracle database |
PostgreSQL |
SELECT seq1.CURRVAL FROM DUAL;
|
SELECT CURRVAL('seq1') FROM DUAL;
|
#### 2.3.2 NEXTVAL
**Description**
NEXTVAL returns the next number in the sequence.
**Functional differences**
- **Oracle database**
- The sequence name is specified as sequenceName.NEXTVAL.
- **PostgreSQL**
- The sequence name is specified as NEXTVAL('sequenceName').
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the keyword NEXTVAL and identify where it is used.
2. Change the sequence name specification by placing it after NEXTVAL and enclosing it in parentheses and single quotation marks.
**Migration example**
The example below shows how to migrate NEXTVAL.
| Oracle database |
PostgreSQL |
SELECT seq1.NEXTVAL FROM DUAL;
|
SELECT NEXTVAL('seq1') FROM DUAL;
|
#### 2.3.3 ROWID
**Description**
ROWID obtains information for uniquely identifying data.
**Functional differences**
- **Oracle database**
- ROWID is created automatically when a table is created.
- **PostgreSQL**
- ROWID cannot be used. Use OID instead. However, WITH OIDS must be specified when a table is created.
**Migration procedure**
Use the following procedure to perform migration:
1. Specify WITH OIDS at the end of the CREATE TABLE statement.
2. Change the ROWID extraction item in the SELECT statement to OID.
**Migration example**
| Oracle database |
PostgreSQL |
CREATE TABLE t1( col1 INTEGER );
INSERT INTO t1 VALUES( 11 );
SELECT ROWID, col1 FROM t1;
|
CREATE TABLE t1( col1 INTEGER ) WITH OIDS;
INSERT INTO t1 VALUES( 11 );
SELECT OID, col1 FROM t1;
|
#### 2.3.4 ROWNUM
**Description**
ROWNUM obtains the number of the current row.
##### 2.3.4.1 Obtaining the Row Number
**Functional differences**
- **Oracle database**
- ROWNUM obtains the number of the current row.
- **PostgreSQL**
- ROWNUM cannot be used. Use ROW_NUMBER() OVER() instead.
**Migration procedure**
Using the ROW_NUMBER() function instead of ROWNUM, perform migration so that the current number is obtained. Use the following procedure to perform migration:
1. Search for the keyword ROWNUM and identify where it is used.
2. Change ROWNUM to ROW_NUMBER() OVER().
**Migration example**
The example below shows migration when a line number is obtained.
| Oracle database |
PostgreSQL |
SELECT ROWNUM, i_number, i_name
FROM inventory_table;
|
SELECT ROW_NUMBER() OVER(), i_number, i_name
FROM inventory_table;
|
**Note**
----
This migration example cannot be used with the UPDATE statement.
----
##### 2.3.4.2 Sorting Records and Obtaining the First N Records
**Functional differences**
- **Oracle database**
- If a subquery that contains an ORDER BY clause is specified in the FROM clause and a ROWNUM condition is defined in the WHERE clause, the records are sorted and then the first N records are obtained.
- **PostgreSQL**
- ROWNUM cannot be used. Using the LIMIT clause instead, sort the records and obtain the first N records.
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the keyword ROWNUM and identify where it is used.
2. If an ORDER BY clause is specified in a subquery of the FROM clause and the results are filtered according to the ROWNUM condition in the WHERE clause, regard this portion as an SQL statement that sorts the records and then obtains the first N records.
3. Move the table name and ORDER BY clause from the FROM clause subquery to a higher SELECT statement and delete the subquery.
4. In the LIMIT clause, set the same number as the ROWNUM condition of the WHERE clause, and delete the ROWNUM condition from the WHERE clause.
**Migration example**
The example below shows migration when records are sorted and then the first N records are obtained.
| Oracle database |
PostgreSQL |
SELECT i_number, i_name
FROM ( SELECT * FROM inventory_table
ORDER BY i_number DESC )
WHERE ROWNUM < 5;
|
SELECT i_number, i_name
FROM inventory_table
ORDER BY i_number DESC LIMIT 4;
|
### 2.4 Treatment of NULL and Zero-Length Strings
This section explains how NULL and zero-length strings are treated.
Oracle databases treat zero-length strings as NULL. In contrast, PostgreSQL treats zero-length strings and NULL as two different values.
The table below lists the advantages and disadvantages of using zero-length strings and NULL when performing migration.
**Advantages and disadvantages of retaining or migrating Oracle database zero-length strings**
|Oracle database zero-length strings |Advantages |Disadvantages |
|:---|:---|:---|
|Treated as zero-length strings
without being migrated to NULL | String concatenation (||) can be used as is. | The target data has fewer hits than with IS NULL.
Conditional expressions must be changed. |
| Migrated to NULL | IS NULL can be used as is. | The result of string concatenation (||) is NULL.
String concatenation must be changed. |
The following sections explain how to make changes if zero-length strings and NULL values are used together.
#### 2.4.1 Search Conditions (IS NULL Predicate)
**Functional differences**
- **Oracle database**
- Even zero-length strings hit the IS NULL condition.
- **PostgreSQL**
- Zero-length strings do not hit the IS NULL condition.
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the keyword IS NULL and identify where a NULL search is used.
2. Change the portions found by the IS NULL search to IS NULL OR strName = ''.
**Migration example**
The example below shows migration when a search for zero-length strings and NULL values is performed.
| Oracle database |
PostgreSQL |
SELECT * FROM staff_table
WHERE job IS NULL;
|
SELECT * FROM staff_table
WHERE job IS NULL OR job = '';
|
The example below shows migration when a search for values other than zero-length strings and NULL values is performed.
| Oracle database |
PostgreSQL |
SELECT * FROM staff_table
WHERE job IS NOT NULL;
|
SELECT * FROM staff_table
WHERE job IS NOT NULL AND job != '';
|
#### 2.4.2 Search Conditions (Comparison Predicate)
**Functional differences**
- **Oracle database**
- Zero-length strings are treated as NULL, so they do not match search conditions.
- **PostgreSQL**
- Zero-length strings are not treated as NULL, so they can match search conditions.
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the name of the column where the zero-length string is stored, and identify where a string comparison is used.
2. Add AND columnName != '' to the search condition.
**Migration example**
The example below shows migration when a zero-length string comparison is specified as the search condition.
| Oracle database |
PostgreSQL |
SELECT * FROM staff_table
WHERE job < 'A00';
|
SELECT * FROM staff_table
WHERE job < 'A00' AND job != '';
|
#### 2.4.3 String Concatenation (||)
**Functional differences**
- **Oracle database**
- Concatenation with NULL returns strings other than NULL.
- **PostgreSQL**
- Concatenation with NULL returns NULL.
**Migration procedure**
Use the following procedure to perform migration:
1. Search for the keyword || and identify where string concatenation is used.
2. If the values to be concatenated are likely to become NULL, use the NVL function to return a zero-length string instead of NULL.
**Migration example**
The example below shows migration when NULL is returned by string concatenation (||) in Oracle databases.
| Oracle database |
PostgreSQL |
SELECT 'NAME:' || name
FROM staff_table;
|
SELECT 'NAME:' || NVL( name, '' )
FROM staff_table;
|
|