Chapter 2 Migrating Syntax Elements

This chapter explains how to migrate SQL syntax elements.

2.1 Basic Elements

This section explains the basic elements of SQL syntax.

2.1.1 TIMESTAMP Literal

Description

A literal with the TIMESTAMP prefix is treated as TIMESTAMP type data.

Functional differences

  • Oracle database
    • The TIMESTAMP prefix can signify a time zone literal.
  • PostgreSQL
    • The TIMESTAMP WITH TIME ZONE prefix signifies a time zone literal. If the prefix is TIMESTAMP only, the time zone value is discarded. No warning, error, or other notification is output.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword TIMESTAMP and identify where it is used as a literal prefix.
  2. If a time zone has been specified for the literal, change the prefix to TIMESTAMP WITH TIME ZONE.

Migration example

The example below shows how to migrate a TIMESTAMP literal with time zone.

2.1.2 Alternate Quotation Literal

Description

Using alternate quotation enables a delimiter to be used for a text string.

Functional differences

  • Oracle database
    • The alternate quotation marks q’x and x’ are used. The letter x represents the alternate character.
  • PostgreSQL
    • The alternate quotation marks $q$ and $q$ are used.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword q’ or Q’ and identify where alternate quotation marks are used.
  2. Delete alternate characters and single quotation marks where alternate quotation has been used, and enclose strings with $q$. The character between the two $ symbols can be omitted or any string can be specified.

Migration example

The example below shows how to migrate alternate quotation.

See

Refer to “The SQL Language” > “Lexical Structure” > “Dollar-quoted String Constants” in the PostgreSQL Documentation for information on alternate quotation marks.

2.2 Data Types

This section explains data types.

2.2.1 Migrating Data Types

The table below lists the PostgreSQL data types that correspond to Oracle database data types.

Data type correspondence

  • Character

|Oracle database Data type |Remarks |Migratability |PostgreSQL Data type |Remarks | | :— | :— | :—: | :— | :— | | CHAR CHARACTER | Specifies the number of bytes or number of characters. | YR | char character | Only the number of characters can be specified. | | CLOB CHAR LARGE OBJECT CHARACTER LARGE OBJECT | | MR | text Large object | Up to 1 GB(text) Up to 4 TB(Large object) | | CHAR VARYING CHARACTER VARYING VARCHAR | Specifies the number of bytes or number of characters. | YR | char varying character varying varchar | Only the number of characters can be specified. LONG MR text Up to 1 GB M Large object | NCHAR NATIONAL CHAR NATIONAL CHARACTER | | YR | nchar national char national character | This data type is internally used as a character type. | | NCHAR VARYING NATIONAL CHAR VARYING NATIONAL CHARACTER VARYING | | YR | nchar varying national char varying national character varying | This data type is internally used as a character varying type | | NCLOB NCHAR LARGE OBJECT NATIONAL CHARACTER LARGE OBJECT | | MR | text Large object | Up to 1 GB(text) Up to 4 TB(Large object) NVARCHAR2 YR nvarchar2 Collating sequence is not supported. This data type is added using orafce. MR nchar varying national char varying national character varying This data type is internally used as a character varying type. VARCHAR2 Specifies the number of bytes or number of characters. YR varchar2 Only the number of bytes can be specified. Collating sequence is not supported. This data type is added using orafce. MR varchar Only the number of characters can be specified.

  • Numeric

|Oracle database Data type |Remarks |Migratability |PostgreSQL Data type |Remarks | | :— | :— | :—: | :— | :— | | BINARY_DOUBLE | | M | double precision | | | BINARY_FLOAT | | M | real | | | DOUBLE PRECISION | | Y | double precision | | | FLOAT | | Y | float | | | INTINTEGER | | Y | intinteger | | | NUMBER DEC DECIMAL NUMERIC | Specifies numbers rounded according to the scale definition. | MR | smallint integer bigint numeric | Integers from -32,768 to +32,767 (smallint) Integers from -2,147,483,648 to +2,147,483,647 (integer) Integers from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807(bigint) | | REAL | | Y | real | | | SMALLINT | | Y | smallint | |

  • Date and time

|Oracle database Data type |Remarks |Migratability |PostgreSQL Data type |Remarks | | :— | :— | :—: | :— | :— | | INTERVAL DAY TO SECOND | | Y | interval day to second | | | INTERVAL YEAR TO MONTH | | Y | interval year to month | | | TIMESTAMP | | Y | timestamp | | | TIMESTAMP WITH LOCAL TIME ZONE | | M | timestamp with time zone | | | TIMESTAMP WITH TIME ZONE | | Y | timestamp with time zone | DATE Y date (orafce) The time can be stored in addition to the date. The search_path parameter must be specified. YR date (PostgreSQL) Only the date is stored. 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

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.

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.

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.

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

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.

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.

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.

The example below shows migration when a search for values other than zero-length strings and NULL values is performed.

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.

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.