RE: Use pgloader with FDW

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 ?

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

-----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