Thread: Empty to NULL conversion - Ruby - Postgres ?
Hi, I am trying to copy some data from MySQL to Postgres using Ruby. This is NOT a MySQL to PostgreSQL conversion project. I need to read the data from a MySQL database and load it into PostgreSQL database. How do I handle "nil" in ruby and convert them into NULL so that I can insert NULL into INT columns instead of empty values? In short I am getting the following popular error: invalid input syntax for integer: "" (PGError) This is because when I read data from MySQL, all the NULL values are converted into "nil" in Ruby and inserting them into Postgres is an issue because "nil" is being transmitted as "" (empty) and not as SQL NULL. -Venks
On Feb 1, 2008, at 5:33 PM, Venks wrote: > Hi, > > I am trying to copy some data from MySQL to Postgres using Ruby. This > is NOT a MySQL to PostgreSQL conversion project. I need to read the > data from a MySQL database and load it into PostgreSQL database. > > How do I handle "nil" in ruby and convert them into NULL so that I can > insert NULL into INT columns instead of empty values? In short I am > getting the following popular error: > > invalid input syntax for integer: "" (PGError) > > This is because when I read data from MySQL, all the NULL values are > converted into "nil" in Ruby and inserting them into Postgres is an > issue because "nil" is being transmitted as "" (empty) and not as SQL > NULL. That all sounds perfectly reasonable up until the "nil" being translated to the empty string. It sounds like the issue is the ruby module you're using to talk to postgresql, or your use of it. The docs for Ruby DBI suggest that nil is converted into SQL null correctly, so you're going to have to be more specific about what you're doing and what problem you're seeing, I think. Cheers, Steve
On Fri, 2008-02-01 at 20:33 -0500, Venks wrote:
> Hi,
>
> I am trying to copy some data from MySQL to Postgres using Ruby. This
> is NOT a MySQL to PostgreSQL conversion project. I need to read the
> data from a MySQL database and load it into PostgreSQL database.
>
> How do I handle "nil" in ruby and convert them into NULL so that I can
> insert NULL into INT columns instead of empty values? In short I am
> getting the following popular error:
>
Can you install the latest module from the ruby-pg project (or just "gem
install pg").
If you have this problem still, send an email to the ruby-pg-general
mailing list (or post in the forum, or email me directly).
Regards,
Jeff Davis
Steve/Jeff, Thanks for your replies. I am using the latest "pg" module but I don't know if there is any way to handle this without SQL. I am manually taking care of it using SQL functions to convert empty strings to NULL. It would be nice if there is a setting that could take care of this issue. PS: This issue is similar to COPY where by default "" is considered empty and not NULL and so you can't load this data into a NUMERIC data type defined as NULL. COPY has a WITH NULL AS syntax which addresses this issue. On Feb 5, 2008 5:47 PM, Jeff Davis <pgsql@j-davis.com> wrote: > On Fri, 2008-02-01 at 20:33 -0500, Venks wrote: > > Hi, > > > > I am trying to copy some data from MySQL to Postgres using Ruby. This > > is NOT a MySQL to PostgreSQL conversion project. I need to read the > > data from a MySQL database and load it into PostgreSQL database. > > > > How do I handle "nil" in ruby and convert them into NULL so that I can > > insert NULL into INT columns instead of empty values? In short I am > > getting the following popular error: > > > > Can you install the latest module from the ruby-pg project (or just "gem > install pg"). > > If you have this problem still, send an email to the ruby-pg-general > mailing list (or post in the forum, or email me directly). > > Regards, > Jeff Davis > >
On Sat, 2008-02-09 at 09:23 -0500, Venks wrote:
> Steve/Jeff,
>
> Thanks for your replies. I am using the latest "pg" module but I don't
> know if there is any way to handle this without SQL. I am manually
> taking care of it using SQL functions to convert empty strings to
> NULL. It would be nice if there is a setting that could take care of
> this issue.
Hi,
Can we take this issue to the ruby-pg lists (or forums), so that other
ruby-pg users can see it?
Show a simple irb session that demonstrates your problem, and also the
version of the "pg" module that you are using. This issue did exist, and
was fixed, but perhaps you have one version behind or something.
Regards,
Jeff Davis