When syncing a Salesforce field using Heroku Connect you are encountering
value too long for type character varying(40) errors but database data type definition matches the Salesforce definition.
Some Salesforce fields are forgiving when provided data longer than the defined field length. Salesforce will even truncate the data when displaying it in a dashboard. However, the Salesforce API returns the data in full which means that Heroku Connect will receive that and attempt to insert it into the database. When your mapping is in
read-only mode, Heroku Connect will truncate the data for you but it is possible that in
read-write the changed data could be written back to Salesforce which may unexpectedly alter the data which is why you see this error.
Salesforce standard picklist fields are also known to encounter this problem. They are always defined as a maximum of 40 characters long but will allow you to create and use picklist values that have more than 40 characters. To resolve this issue redefine picklist values to be no more than 40 characters. Please see the Salesforce documentation on altering picklist values for more information.