Re: newbie - postgresql or mysql

Michael Fuhr wrote:
> [Please copy the mailing list on replies so others can
> contribute to and learn from the discussion.  Also, please
> don't top-post, as it destroys the flow of the discussion;
> I've moved your questions to a more logical place.]
>
> On Wed, Aug 31, 2005 at 03:49:57PM -0600, aly.dharshi@telus.net wrote:
>> On Wed, 31 Aug 2005, Michael Fuhr wrote:
>>> On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote:
>>>>>> insert  into category values
>>>>>> (4, 'Hardware - Monitor', 2, '2004-10-12 10:50:01'),
>>>>>> (5, 'Hardware - Printer', 2, '2004-10-12 10:50:02'),
>>>>>> (6, 'Hardware - Terminal', 2, '2004-10-12 10:50:02'),
>>>
>>> PostgreSQL doesn't support this form of INSERT; you'll have to use a
>>> separate INSERT per record or use COPY.  Another method, although
>>> probably not useful in this case, is to insert records from a
>>> subquery; see the INSERT documentation for more information.
>>
>> Wouldn't it be simpler to do a dblink, and just get the data from
>> MySQL and drop it into PostgreSQL ? Or is this too complicated ?
>
> dblink (at least the one distributed as contrib/dblink) is
> for making connections to other PostgreSQL databases.  You
> could, however, use DBI-Link or something similar to make
> connections to MySQL or another data source; in that case you
> could use the subquery form of INSERT:
>
> INSERT INTO tablename (columnlist) SELECT columnlist FROM ... ;
>
> However, if you're just doing a one-time import of data from
> MySQL, then it might be simplest to dump the data with
> separate INSERT statements (mysqldump --extended-insert=FALSE).

If you have time to play around a bit, and have Ruby, og could be
utilized...
create a test env, do a little hacking on the below ( from the og
examples dir) i.e. you'd want to remove the destroy => true, or set to
false, in the config, etc...

$ cat mysql_to_psql.rb
# = Mysql to PostgreSQL migration example.
#
# A simple example to demonstrate the flexibility of
# Og. Two connections to different databases are
# created and data is copied from a MySQL database
# to a PostgreSQL database.
#
# Og makes it easier to switch to a REAL database :)

require 'og'

# Configure databases.

psql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'psql',
  :user => 'postgres',
  :password => 'navelrulez'
}

mysql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'mysql',
  :user => 'root',
  :password => 'navelrulez'
}

# Initialize Og.

psql = Og.connect(psql_config)
mysql = Og.connect(mysql_config)

# An example managed object.
# Looks like an ordinary Ruby object.

class Article
  property :name, :body, String

  def initialize(name = nil, body = nil)
    @name, @body = name, body
  end
end

# First populate the mysql database.

mysql.manage(Article)

a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')

# Read all articles from Mysql.

articles = Article.all

# Switch to PostgreSQL.

psql.manage(Article)

# Store all articles.

for article in articles
  article.insert
end

# Fetch an article from PostgreSQL
# as an example. Lookup by name.

article = Article.find_by_name('name1')

reid