Support Article
Database object create fails when importing on different DB
SA-50743
Summary
Applications were exported from an Oracle database running Pega 7.2.1 and they are being imported into a PostgreSQL DB running Pega 7.3.1.
The import fails due to invalid schema object names.
The names of the indexes is not valid for PostgreSQL - they have special characters which are now allowed. The database actually complains about the '/' in the following example:
--execute on the database specified by Data-Admin-DB-Name PegaRULES:
CREATE INDEX BIN$XabcX1CjEX/gUwzK3gqxKA==$0 ON pegadata.tablename
(
"pxobjclass" ASC,
"classname" ASC,
"propertyname" ASC,
);
Error Messages
ERROR: syntax error at or near "/"
LINE 1: CREATE INDEX BIN$XabcX1CjEX/gUwzK3gqxKA==$0 ON pegadata.tablename
^
SQL state: 42601
Character: 28
Steps to Reproduce
Import the application with an option to let the system generate the DDL.
Root Cause
Database vendor SQL languages are not directly cross-compatible.
As such, auto-generated index identifiers generated in one database may not be valid for a DDL being imported into another database. In this casen the "/" is causing the first syntax error
Note: Syntax parsing terminates at first error, and will not check the rest of the identifier for further syntax issues.
Resolution
Perform the following local-change steps:
1. Directly modify the DDL to choose index identifiers which do not conflict with your databases syntax for identifiers.
2. Apply the DDL manually.
3. Perform the import, using the option corresponding to applying the DDL manually.
Published May 3, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.