Issue
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.
Resolution
If the field that is providing an error is a custom field then the likely reason is that the field used to allow a longer data type and was altered to make it shorter. When a Salesforce custom field definition is modified to make it shorter any data in that field is left as is. This might mean that some data is longer than the schema allows. Salesforce may truncate the data when displaying it to users in Salesforce. However, the Salesforce API returns the data in full which means that Heroku Connect will receive too long data 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 to prevent the possibility of loosing data while in read-write mode Heroku Connect will refuse to truncate this data and as a result you will see the above 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.