On 11/9/2017 8:19 AM, Merlin Moncure wrote:
On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback
<adambrusselback@gmail.com> wrote:
Since you are migrating data into a staging table in PostgreSQL, you may set
the field data type as TEXT for each field where you have noticed or
anticipate issues.
Then after population perform the datatype transformation query on the given
fields to determine the actual field value that could not be gracefully
transformed.
This is the approach I have come to as the most successful for data migrations.
I will use tools like Kettle / Talend to get data into a staging table
with every column as text, then use SQL to migrate that to a properly
typed table. Works much better than trying to work within the
constraints of these tools.
YES
I call the approach 'ELT', (Extract, Load, Trasform). You are much
better off writing transformations in SQL than inside of an ETL tool.
This is a perfect example of why.
All sound advice. Thanks.
Igal Sapir