Thread: newbie - postgresql or mysql
Hello, I would like to try postgresql and was wondering what benefits it has over MySql. I am using navicat to manage my MySQL databases. Thanks for the help. Frank
On Wed, Aug 31, 2005 at 11:50:55AM -0400, Frank wrote: > I would like to try postgresql and was wondering what benefits it > has over MySql. Search the list archives to see past discussion, or use a search engine like Google with words like "postgresql mysql comparison." A couple of links you might want to read are: http://sql-info.de/mysql/gotchas.html http://sql-info.de/postgresql/postgres-gotchas.html -- Michael Fuhr
Frank wrote: > Hello, > > I would like to try postgresql and was wondering what benefits it has > over MySql. > I am using navicat to manage my MySQL databases. Hi Frank. You don't say what you want to use your RDBMS for, so it's a bit difficult to point out what about PostgreSQL would be best for you. Can you expand on what you are trying to do? What platforms you want to run on? What experience with other RDMBS you have? -- Richard Huxton Archonet Ltd
Hello Frank,
I believe that PostgreSQL is a proper RDBMS adhering to the ACID
principles, as well as has similar functions to Oracle, in terms of having
procedural language support with pg/plsql and the ability to also use
other languages such as perl, java (pl/perl and pl/java) to do the same.
I think that you can use the GUI tool PgAdmin III to do what you
probably do with Navicat for MySQL. MySQL is not really free there are
some caveats that you have to look out for, PostgreSQL is 100% free (as
like in the brew).
I am sure that there are others who could give you a better
insight than I have, BUT hopefully this helps.
Cheers,
Aly
On Wed, 31 Aug 2005, Frank wrote:
> Hello,
>
> I would like to try postgresql and was wondering what benefits it has over
> MySql.
> I am using navicat to manage my MySQL databases.
>
> Thanks for the help.
>
> Frank
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Aly S.P Dharshi
aly.dharshi@telus.net
"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"
Thanks for the feedback, sorry I was not more specific. We are a non-profit hospital and have been using MySQL for about 4 years. I wanted to convert some apps over to use postgresql and cannot find a good tool to import and auto create the tables. MySQL syntax is not compatible with postgresql. I get: ERROR: syntax error at or near "`" at character 14 from the MySQL output below. CREATE TABLE `category` ( `category_id` int(11) NOT NULL auto_increment, `category` char(50) default NULL, `LastUser` int(11) NOT NULL default '0', `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 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'), (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), (11, 'Software - PC', 2, '2004-10-12 10:50:03'), (13, 'Software - Network', 2, '2004-10-12 10:50:04'), (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), (15, 'Software - Server', 2, '2004-10-12 10:50:04'), (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); Regards, Frank
On Wed, 2005-08-31 at 13:50, Frank wrote: > Thanks for the feedback, sorry I was not more specific. > We are a non-profit hospital and have been using MySQL for about 4 years. > > I wanted to convert some apps over to use postgresql and cannot find > a good tool to import and auto create the tables. Look in the contrib/mysql directory in the source file (or install the contrib packages for your system, assuming they come with that contrib package.) > MySQL syntax is not compatible with postgresql. Generally speaking, MySQL syntax is just not compatible. With anything. > I get: > ERROR: syntax error at or near "`" at character 14 > from the MySQL output below. > > CREATE TABLE `category` ( > `category_id` int(11) NOT NULL auto_increment, > `category` char(50) default NULL, > `LastUser` int(11) NOT NULL default '0', > `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on > update CURRENT_TIMESTAMP, > PRIMARY KEY (`category_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Yeah, the SQL spec says to use " for that, not `. There's a MySQL switch that makes it use the right character, but it breaks many applications so no one uses it. A simple sed or perl script, or even a search and replace should do the trick. If you don't need upper / lower case in your table names, just don't quote them (ever) and they'll case fold internally to lower case in postgresql. Note that instead of autoincrement, use the macro serial. CREATE TABLE category ( category_id int(11) NOT NULL SERIAL, category char(50) default NULL, LastUser int(11) NOT NULL default '0', LastUpdated timestamp NOT NULL default now(), PRIMARY KEY (category_id) ); Note that since you declared category_id as a primary key, there's no real need for the not null, since it's implied with pk. If you want an auto updating last update field you'll need a simple trigger to do that. > 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'), > (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); Hope that helps get ya started.
Thanks for clearing up some confusion. >>Look in the contrib/mysql directory in the source file (or install the contrib packages for your system, assuming they come with that contrib package<< I do not have that, where can I download it? This fails to insert records > > 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'), > > (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > > (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > > (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > > (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > > (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > > (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > > (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > > (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); Regards, Frank
MySQL has a nasty habit of ignoring standards; in every other database I've used, if you want to quote an identifier (such as a field name), you use ", not `. The fields are also incompatable. int() is non-standard, for starters. There are MySQL to PostgreSQL conversion tools out there that should help. On Wed, Aug 31, 2005 at 02:50:16PM -0400, Frank wrote: > Thanks for the feedback, sorry I was not more specific. > We are a non-profit hospital and have been using MySQL for about 4 years. > > I wanted to convert some apps over to use postgresql and cannot find > a good tool to import and auto create the tables. > MySQL syntax is not compatible with postgresql. > I get: > ERROR: syntax error at or near "`" at character 14 > from the MySQL output below. > > CREATE TABLE `category` ( > `category_id` int(11) NOT NULL auto_increment, > `category` char(50) default NULL, > `LastUser` int(11) NOT NULL default '0', > `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on > update CURRENT_TIMESTAMP, > PRIMARY KEY (`category_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > > 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'), > (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); > > Regards, > > Frank > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote: > Thanks for clearing up some confusion. > > >>Look in the contrib/mysql directory in the source file (or install the > contrib packages for your system, assuming they come with that contrib > package<< > > I do not have that, where can I download it? It's part of the PostgreSQL source code, which you can download from the website. > This fails to insert records Yes, the values (), (), () syntax isn't supported yet (I think it's slated for 8.2). In the meantime you'll need to convert either to multiple insert statements (which you'll want to wrap in a BEGIN; COMMIT;) or better yet a copy statement. But really what you want to do is use the migration tools that are out there... > >> 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'), > >> (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > >> (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > >> (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > >> (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > >> (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > >> (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > >> (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > >> (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); > > Regards, > > Frank > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Frank <farocco@verizon.net> writes: >> Look in the contrib/mysql directory in the source file (or install the >> contrib packages for your system, assuming they come with that contrib >> package<< > I do not have that, where can I download it? The best place to get it would be http://gborg.postgresql.org/project/mysql2psql/projdisplay.php I think we dropped the contrib copy for 8.1 anyway... regards, tom lane
On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote: > This fails to insert records > > >> 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. -- Michael Fuhr
On 8/31/05, Frank <farocco@verizon.net> wrote: > I wanted to convert some apps over to use postgresql and cannot find > a good tool to import and auto create the tables. > MySQL syntax is not compatible with postgresql. > I get: > ERROR: syntax error at or near "`" at character 14 > from the MySQL output below. > Try playing with the mysqldump command. Some of the options alleviate the quoting issue. Try the --quote-names=FALSE option for example.
On Wed, 2005-08-31 at 14:17, Frank wrote: > Thanks for clearing up some confusion. > > >>Look in the contrib/mysql directory in the source file (or install the > contrib packages for your system, assuming they come with that contrib > package<< It comes with the postgresql tar ball. just download it from the www.postgresql.org site and untar it somewhere. then cd into the postgresql-x.y.z/contrib/mysql directory, where x.y.z is the version of postgresql you downloaded. > > I do not have that, where can I download it? > > This fails to insert records > > > > 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'), > > > (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > > > (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > > > (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > > > (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > > > (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > > > (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > > > (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > > > (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); Yeah, you'll have to do it like: insert into table values (.... insert into table values (.... insert into table values (.... insert into table values (.... insert into table values (.... If you've got a lot to do (more than a few dozen) wrap them in one big transaction with begin end: begin; insert into table values (.... insert into table values (.... insert into table values (.... insert into table values (.... insert into table values (.... commit; Note that any errors in the import with begin/end wrapped around it means the whole import fails. which is a good thing (tm) since you don't have to figuring out which rows made it in and which didn't. Fix the error and try the whole being;insertX1000;commit again.
If the inserts are all a bunch of data statements like that, a SED script could turn them into something for bulk load via COPY easily enough. Might be an even better solution, depending on what the OP is trying to accomplish. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Michael Fuhr > Sent: Wednesday, August 31, 2005 12:39 PM > To: Frank > Cc: Scott Marlowe; pgsql-general@postgresql.org > Subject: Re: [GENERAL] newbie - postgresql or mysql > > On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote: > > This fails to insert records > > > > >> 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. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
[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). -- Michael Fuhr
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
Hi Frank; I will use your questions to help in the next version of my migration guide, but you might want to take a look at it anyway. You can find it at: http://www.metatrontech.com/wpapers/ It covers many areas of incompatibility between MySQL and PostgreSQL. My own suggestions is that you wait until 8.1 is released as some of the issues involved in application porting will be resolved (LEAST and GREATEST, for example). Any feedback on the guide would be appreciated. Best Wishes, Chris Travers Metatron Technology Consulting
Attachment
>>mysqldump --extended-insert=FALSE<< Thanks for the help, this is allowing me to import the data now. Regards, Frank At 09:37 AM 9/1/2005, 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). > >-- >Michael Fuhr > >---------------------------(end of broadcast)--------------------------- >TIP 2: Don't 'kill -9' the postmaster