Postgresql CSV upload with Boolean field generates error

[sedacrivity]

When doing a deploy which includes CSV with data, we are getting the following error back:

CDS output

error: in cds.deploy(): column "active" is of type boolean but expression is of type text
Query {
  UPSERT: {
    into: 'xxx.xxxTypes',
    columns: [
      'ID',
      'Active',
      'Title',
      'Description',
      'businessObject_ID',
      'implementationClassID',
      'externalProcessing'
    ],
    rows: [
      [Array], [Array],
      [Array], [Array],
      [Array], [Array],
      [Array], [Array],
      [Array]
    ]
  }
}
    at /home/xxxx/node_modules/pg/lib/client.js:526:17

DB output:

db_1       | 2023-09-08 13:05:23.317 UTC [753] ERROR:  column "active" is of type boolean but expression is of type text at character 157
db_1       | 2023-09-08 13:05:23.317 UTC [753] HINT:  You will need to rewrite or cast the expression.
db_1       | 2023-09-08 14:15:18.538 UTC [188] STATEMENT:  INSERT INTO xxxxTypes (ID,Active,Title,Description,businessObject_ID,implementationClassID,externalProcessing) SELECT value->>0,value->>1,value->>2,value->>3,value->>4,value->>5,CASE value->>6 WHEN 'true' THEN true WHEN 'false' THEN false END FROM json_array_elements($1::JSON) WHERE true ON CONFLICT(ID) DO UPDATE SET Active = excluded.Active,Title = excluded.Title,Description = excluded.Description,businessObject_ID = excluded.businessObject_ID,implementationClassID = excluded.implementationClassID,externalProcessing = excluded.externalProcessing

The CSV data file deploys fine on HANA and has not changed:

ID;Active;Title;Description;businessObject_ID;implementationClassID;externalProcessing
PROFILE_INIT;True;Profile Initialization;Initialize new profiles;PROFILES;;True

I tried changing into lower case values for the boolean but that doesn't make a difference.

[patricebender]

Could you please provide your cds --versions? It would be great if you'd provide a sample project and detailed steps to reproduce as well.

[sedacrivity]

Hi, Sorry for the belated response .. RL got in the way.

I altered the beershop to include an active flag and updated to the latest versions of packages.

https://github.com/sedacrivity/pg-beershop/tree/SDS-Active

To my surprise this deploys 'fine' - the boolean column is added and the adjusted CSV data is uploaded without issues.

@cap-js/postgres: 1.2.1
@sap/cds: 7.2.1
@sap/cds-compiler: 4.0.2
@sap/cds-dk: 7.2.0
@sap/cds-dk (global): 7.2.0
@sap/cds-fiori: 1.1.0
@sap/cds-foss: 4.0.2
@sap/cds-mtxs: 1.11.0
@sap/eslint-plugin-cds: 2.6.3
Node.js: v18.16.1

So will recheck my project.

Thanks,

Steven

[sedacrivity]

After updating my project to the latest versions, I still had the same issue.

So comparing the CSV file again I could not see any differences within the actual data - compared with the brewery project.

Then I did notice a difference - the header ! My boolean field name 'active' was spelled with an uppercase.

ID;Active;Title

When I change it to lower case, the deploy works fine and data is uploaded !

So somehow the boolean header field is affected by 'lower/upper case' differences while for other fields -like 'Title' - it does not seem to make a difference ?

Second 'issue' I noticed is that the actual value 'True' is now accepted but not converted into a boolean value. It needs to be lower case apparently.

Kind Regards,

Steven

[patricebender]

Hi @sedacrivity

w.r.t. your first issue, this is a known limitation at the moment, see #92. As of now, the csv headers must match the column names in case. We are discussing this and will track the progress in the other issue.

@BobdenOs could you say something to the second inquiry:

Second 'issue' I noticed is that the actual value 'True' is now accepted but not converted into a boolean value. It needs to be lower case apparently.