PGXN

Contents

• • Chapter 2 Migrating Syntax Elements
    • 2.1 Basic Elements
    • 2.2 Data Types
    • 2.3 Pseudocolumns
    • 2.4 Treatment of NULL and Zero-Length Strings

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.

INSERT INTO attendance_table 
 VALUES( '1001', 
 'i', 
 TIMESTAMP'2016-05-20 12:30:00 +09:00' );

INSERT INTO attendance_table 
 VALUES( '1001',
         'i',
 TIMESTAMP WITH TIME ZONE'2016-05-20 12:30:00 +09:00' );

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.

SELECT q'[Adam Electric company's address]' 
 FROM DUAL;

SELECT $q$Adam Electric company's address$q$ 
 FROM DUAL;

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.

• 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 | | | INT
INTEGER | | Y | int
integer | | | 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 |

• 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

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.

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.

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.

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

 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.

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.

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.

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.

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.

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.

SELECT 'NAME:' || name 
 FROM staff_table;

 SELECT 'NAME:' || NVL( name, '' ) 
  FROM staff_table;