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, [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
