RE: Use pgloader with FDW

Thanks a lot Adrian.
We can't update the mysql columns values, so I will adopt the datetime -> text solution.
Thanks again

-----Message d'origine-----
De : Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Envoyé : vendredi 23 mars 2018 14:25
À : Patricia DECHANDOL <pdechandol(at)intercountry(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
Objet : Re: Use pgloader with FDW

On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote:
> Hello Adrian,
>
> So, if I well understand, the only solution is to wrap the mysql datetime columns to "text" columns in my foreign tables.
> And then use a cast function to convert from text to date in Postgre when I want to use these columns ?

There is also the option of changing the values in the MySQL database to either an actual datetime or NULL. Though there is the potential issue of what that would do to code that is pulling from the MySQL database.

>
> No other way.
> The pgloader can't be used by the FDW to manage this point ?

It has been a while since I used pgloader, but from what I remember it is a tool for doing the initial migration of data from MySQL/Sqlite/SQL Server to Postgres. What you seem to be looking for is pgloader to sit between the MySQL database and the Postgres one and do the transformation in real time. AFAIK that is not possible.

>
>
>
> -----Message d'origine-----
> De : Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> Envoyé : vendredi 23
> mars 2018 13:52 À : Patricia DECHANDOL <pdechandol(at)intercountry(dot)com>;
> pgsql-general(at)lists(dot)postgresql(dot)org
> Objet : Re: Use pgloader with FDW
>
> On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote:
>> Hello,
>>
>> I'm new to Postgresql and try to use the FDW with mysql database.
>>
>> Everything is OK to create my FDW and foreign tables, but I meet a
>> problem when I try to do a select on a FOREIGN table containing
>> datetime column which contains the value "0000-00-00 00:00:00" in mysql.
>
> That is MySQL's version of NULL for datetimes. It is not a valid datetime though.
>
>>
>> Select on the foreign table fails.
>>
>> The mysql datetime column has been automatically wrapped to
>> "timestamp without timezone" column in the foreign table by the
>> instruction "import foreign schema" that I used.
>
>>
>> How can I deal with this ?
>
> https://github.com/EnterpriseDB/mysql_fdw/issues/38
>
>>
>> I read about the pgloader with can manage this king of problem, but
>> can't figure out how to use it with FDW.
>>
>> Thanks for your help.
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com