We plan to build a Postgresql database connected to our mysql database to plug BI tools. We will build materialized views in Postgresql. The datetime conversion logic will be seen in these views, I think.
Thanks for your advise. -----Message d'origine----- De : Alban Hertroys <haram...@gmail.com> Envoyé : vendredi 23 mars 2018 14:58 À : Patricia DECHANDOL <pdechan...@intercountry.com> Cc : Adrian Klaver <adrian.kla...@aklaver.com>; pgsql-general@lists.postgresql.org Objet : Re: Use pgloader with FDW I suppose MySQL has the concept of views. Perhaps you can create a view over the table that translates those 0-datetimes to NULL and have the FDW connect to the view instead? On 23 March 2018 at 14:27, Patricia DECHANDOL <pdechan...@intercountry.com> wrote: > 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.kla...@aklaver.com> Envoyé : vendredi 23 > mars 2018 14:25 À : Patricia DECHANDOL <pdechan...@intercountry.com>; > pgsql-general@lists.postgresql.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.kla...@aklaver.com> Envoyé : vendredi 23 >> mars 2018 13:52 À : Patricia DECHANDOL <pdechan...@intercountry.com>; >> pgsql-general@lists.postgresql.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.kla...@aklaver.com >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
