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!