Thread: Use pgloader with FDW
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.
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 ?
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.
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.klaver@aklaver.com
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.klaver@aklaver.com> Envoyé : vendredi 23 mars 2018 13:52 À : Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com
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.klaver@aklaver.com> > Envoyé : vendredi 23 mars 2018 13:52 > À : Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
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.klaver@aklaver.com> Envoyé : vendredi 23 mars 2018 14:25 À : Patricia DECHANDOL <pdechandol@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 thereis 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 datafrom MySQL/Sqlite/SQL Server to Postgres. What you seem to be looking for is pgloader to sit between the MySQL databaseand the Postgres one and do the transformation in real time. AFAIK that is not possible. > > > > -----Message d'origine----- > De : Adrian Klaver <adrian.klaver@aklaver.com> Envoyé : vendredi 23 > mars 2018 13:52 À : Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
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 <pdechandol@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.klaver@aklaver.com> > Envoyé : vendredi 23 mars 2018 14:25 > À : Patricia DECHANDOL <pdechandol@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 thereis 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 datafrom MySQL/Sqlite/SQL Server to Postgres. What you seem to be looking for is pgloader to sit between the MySQL databaseand the Postgres one and do the transformation in real time. AFAIK that is not possible. > >> >> >> >> -----Message d'origine----- >> De : Adrian Klaver <adrian.klaver@aklaver.com> Envoyé : vendredi 23 >> mars 2018 13:52 À : Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
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 <haramrae@gmail.com> Envoyé : vendredi 23 mars 2018 14:58 À : Patricia DECHANDOL <pdechandol@intercountry.com> Cc : Adrian Klaver <adrian.klaver@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-datetimesto NULL and have the FDW connect to the view instead? On 23 March 2018 at 14:27, Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com> Envoyé : vendredi 23 > mars 2018 14:25 À : Patricia DECHANDOL <pdechandol@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 thereis 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 datafrom MySQL/Sqlite/SQL Server to Postgres. What you seem to be looking for is pgloader to sit between the MySQL databaseand the Postgres one and do the transformation in real time. AFAIK that is not possible. > >> >> >> >> -----Message d'origine----- >> De : Adrian Klaver <adrian.klaver@aklaver.com> Envoyé : vendredi 23 >> mars 2018 13:52 À : Patricia DECHANDOL <pdechandol@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.klaver@aklaver.com >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.