Thread: Moving from MySQL to PostgreSQL with Ruby on Rails.

Hi,

I'm just new to the PostgreSQL world. I've been using MySQL but I want to develop a Ruby on Rails application that can be installed on either MySQL or PostgreSQL. I don't know how much the DDL dialects vary between them. At the moment I am interested in the options on a table like UTF-8. In MySQL I write

CREATE TABLE product (
  id INT NOT NULL AUTOINCREMENT,
  name VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) DEFAULT CHARSET=UTF-8;

Will this definition work in the PostgreSQL world? Is there a web page for people with MySQL exerience moving to PostgreSQL?

Part of the issue is the way Ruby on Rails migration class enables me to add options to Rails' own abstraced DDL just like I have done in the above example. Other ways of adding options might be tricky.

Thanks,
Peter

On Thu, 2005-11-17 at 08:48 -0800, Peter Michaux wrote:
> Hi,
>
> I'm just new to the PostgreSQL world. I've been using MySQL but I want
> to develop a Ruby on Rails application that can be installed on either
> MySQL or PostgreSQL. I don't know how much the DDL dialects vary
> between them. At the moment I am interested in the options on a table
> like UTF-8. In MySQL I write
>
> CREATE TABLE product (
>   id INT NOT NULL AUTOINCREMENT,
>   name VARCHAR(255) NOT NULL DEFAULT '',
>   PRIMARY KEY (id)
> ) DEFAULT CHARSET=UTF-8;
>
> Will this definition work in the PostgreSQL world? Is there a web page
> for people with MySQL exerience moving to PostgreSQL?
>

CREATE TABLE product (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL DEFAULT '',
);


> Part of the issue is the way Ruby on Rails migration class enables me
> to add options to Rails' own abstraced DDL just like I have done in
> the above example. Other ways of adding options might be tricky.

With ActiveRecord::Migration:

# db/migrate/1_initial.rb
class Initial < ActiveRecord::Migration

  def self.up
    create_table :products do |t|
      t.column :name, :string, :default => ''
    end
  end

  # drop all tables 'rake migrate VERSION=0'
  def self.down
    drop_table :products
  end

end

# Run from main Rails directory
rake migrate

Using either plain SQL like above or AR::Migrate will generate the same table structure.


Cheers,

-Robby

--
/******************************************************
* Robby Russell, Founder.Developer.Geek
* PLANET ARGON, Rails Development, Consulting & Hosting
* Portland, Oregon  | p: 503.351.4730 | f: 815.642.4068
* www.planetargon.com | www.robbyonrails.com
* Programming Rails   | www.programmingrails.com
*******************************************************/

On Thu, Nov 17, 2005 at 08:48:45AM -0800, Peter Michaux wrote:
> Hi,
>
> I'm just new to the PostgreSQL world. I've been using MySQL but I want to
> develop a Ruby on Rails application that can be installed on either MySQL or
> PostgreSQL. I don't know how much the DDL dialects vary between them. At the
> moment I am interested in the options on a table like UTF-8. In MySQL I
> write
>
> CREATE TABLE product (
> id INT NOT NULL AUTOINCREMENT,
> name VARCHAR(255) NOT NULL DEFAULT '',
> PRIMARY KEY (id)
> ) DEFAULT CHARSET=UTF-8;

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL
/* "name" isn't your greatest idea because it's a keyword.
 * http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
 * A more descriptive word or pair of words would be good here.
 * There is no DEFAULT clause because the database should throw an
 * error if somebody tries to INSERT a NULL here, not march onward.
 */
);

/* You might also want this: */
CREATE UNIQUE INDEX idx_uniq_product_name
    ON product(LOWER(TRIM("name")));

> Will this definition work in the PostgreSQL world? Is there a web
> page for people with MySQL exerience moving to PostgreSQL?

Here are a couple.  The first is a general "how to convert from other
things," while the second is MySQL specific. :)

http://techdocs.postgresql.org/#convertfrom
http://www.in-nomine.org/~asmodai/mysql-to-pgsql.html

> Part of the issue is the way Ruby on Rails migration class enables
> me to add options to Rails' own abstraced DDL just like I have done
> in the above example.

I can't say I think it's a good idea to have "abstracted" or
"portable" DDL.  It's always expensive and difficult to maintain
because you're either writing the DDL, etc. several times, or you're
pushing functionality up into middleware where it may not belong.

Pick one database back-end and stick with it.  It's ever so much
easier to deal with.

OK, that's my $.02 :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

On Thu, Nov 17, 2005 at 09:23:51 -0800,
  Robby Russell <robby.lists@planetargon.com> wrote:
>
> CREATE TABLE product (
>   id SERIAL PRIMARY KEY,
>   name VARCHAR(255) NOT NULL DEFAULT '',
> );

And depending on why you chose VARCHAR(255), you may really want to use TEXT
instead.

On Thu, 2005-11-17 at 15:10 -0600, Bruno Wolff III wrote:
> On Thu, Nov 17, 2005 at 09:23:51 -0800,
>   Robby Russell <robby.lists@planetargon.com> wrote:
> >
> > CREATE TABLE product (
> >   id SERIAL PRIMARY KEY,
> >   name VARCHAR(255) NOT NULL DEFAULT '',
> > );
>
> And depending on why you chose VARCHAR(255), you may really want to use TEXT
> instead.

Mainly because, Rails will generate forms based on the data types
differently. A text field -> VARCHAR... textarea -> TEXT.

Sort of a meta-approach for Rails-based scaffolding generator. Not
required, but it'll speed up the process and limit the number of chars
that you can stick into a text field opposed to a text area.

Robby

--
/******************************************************
* Robby Russell, Founder.Developer.Geek
* PLANET ARGON, Rails Development, Consulting & Hosting
* Portland, Oregon  | p: 503.351.4730 | f: 815.642.4068
* www.planetargon.com | www.robbyonrails.com
* Programming Rails   | www.programmingrails.com
*******************************************************/

On Nov 17, 2005, at 4:44 PM, Robby Russell wrote:

> Sort of a meta-approach for Rails-based scaffolding generator. Not
> required, but it'll speed up the process and limit the number of chars
> that you can stick into a text field opposed to a text area.

Yet again you see RoR compensating for lack of data integrity
checking in the DB of choice... :-)