Question
EY
IN
Last activity: 27 Apr 2022 11:42 EDT
PostgreSQL Date Column - Unknown JDBC Datatype
We are using PRPC 8.6 and PostgreSQL 11.13.
While saving a record in an external (CustomerDATA) postgresql database, date column is throwing following Exception -
Database column sy***_cre***_dt in database customerdata, table ************* has an unknown type: JDBC Type: Date.
PRPC 8.1 was not supporting date column for PostgreSQL as per PDN, has it been resolved in 8.6?
If not, what is the workaround.
We have already tried converting the Property to text, Date and DateTime.
We can not change the DB column type as this is an Customer Enterprise wide used DB.
***Edited by Moderator Marije to change Type from Discussion to Question***
Accepted Solution
Updated: 27 Apr 2022 11:42 EDT
EY
IN
@MarijeSchillern Sure, I understand.
We can have a stored procedure written in DB for the insertion via a Connect-SQL. That might work. Will update here..
Updated: 26 Apr 2022 11:23 EDT
Pegasystems Inc.
GB
Date datatype of Postgresql is still not officially supported by Pega. There are several support articles documenting this:
If you have a date property within Pega that will map to a varchar2(8) database column in a postgres database then 'Optimize for reporting' automates this as expected.
The issue is users who have an external database with a table that has a DATE column type.
When you run the database mapping tool (Create external postgres table with column of type DATE. Run Data Model -> Classes and Properties -> New External Database Table Class Mapping) the wizard maps this DATE column type to a datetime property within Pega. A DATE column in postgres does not have a timestamp, just a date, so it cannot map to a datetime property in pega. It also cannot currently map to a date property in pega. The only thing that will currently work is a text property in pega to a DATE column in postgres. If you try to query with the mapping as generated it fails
We do not yet support mapping a DATE column in postgres to a date property within pega.
There are two open Enhancements:
Date datatype of Postgresql is still not officially supported by Pega. There are several support articles documenting this:
If you have a date property within Pega that will map to a varchar2(8) database column in a postgres database then 'Optimize for reporting' automates this as expected.
The issue is users who have an external database with a table that has a DATE column type.
When you run the database mapping tool (Create external postgres table with column of type DATE. Run Data Model -> Classes and Properties -> New External Database Table Class Mapping) the wizard maps this DATE column type to a datetime property within Pega. A DATE column in postgres does not have a timestamp, just a date, so it cannot map to a datetime property in pega. It also cannot currently map to a date property in pega. The only thing that will currently work is a text property in pega to a DATE column in postgres. If you try to query with the mapping as generated it fails
We do not yet support mapping a DATE column in postgres to a date property within pega.
There are two open Enhancements:
- FDBK-31089.
- FDBK-25593
There are many issues related to using Date type as explained in article 'Date properties are mapped as VARCHAR2 columns, not DATE columns'.
The product management is already aware of the requests but there is no clear road map yet regarding by when or which Pega version the support is going to be available. We would suggest you to use Varchar or Timestamp column types instead as a work around.
Did you already try to convert to varchar(8)?
Please see the available documentation here
Oracle, Microsoft SQL Server and IBM Db2 compatibility
Updated: 26 Apr 2022 11:28 EDT
EY
IN
@MarijeSchillern Thanks for the details. We can't convert it to a Varchar as I have posted already. The DB we are pointing to is an Enterprise DB schema and downstream applications consume the same.
Thanks for all the details.
Pegasystems Inc.
GB
@Subhankar G I have not been able to find any other workaround.
I will let others comment with their suggestions.
Accepted Solution
Updated: 27 Apr 2022 11:42 EDT
EY
IN
@MarijeSchillern Sure, I understand.
We can have a stored procedure written in DB for the insertion via a Connect-SQL. That might work. Will update here..