Re: newbie - postgresql or mysql - Mailing list pgsql-general

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.