Обсуждение: can't read SQL dump from MySQL

Hi,

I'm updating from MySQL to PostgrSQL. Therefor I use
the mysqldump tool for exporting CREATE and INSERT statements
from all tables and rows.

When I try to import the SQL file into PostgreSQL with
psql --> \i filename

I get the following error:

line 17:parser: parse error at or near "("

My SQL statement:

<schnipp>

1   CREATE TABLE Adresse (
2   pid_adresse int(11) DEFAULT '0' NOT NULL auto_increment,
3   id_person int(11) DEFAULT '0' NOT NULL,
4   PLZ varchar(50),
5   id_ort int(11) DEFAULT '0',
6   strasse varchar(50),
7   id_postcode int(11),
8   hausnummer int(11),
9   zusatz varchar(25),
10  postfach varchar(20),
11  leitcode varchar(18),
12  strassen_code char(3),
13  FZ char(3),
14  adresszusatz varchar(50),
15  PRIMARY KEY (pid_adresse),
16  KEY id_person (id_person)
17  );

<schnapp>

any suggestions ?
I can't find a docu for the syntax of the textfiles, which
I can import with the option \i !

On Mon, Sep 11, 2000 at 12:38:54PM +0200, Andreas Jerke wrote:
>
> 1   CREATE TABLE Adresse (
> 2   pid_adresse int(11) DEFAULT '0' NOT NULL auto_increment,
> 3   id_person int(11) DEFAULT '0' NOT NULL,
> 4   PLZ varchar(50),
> 5   id_ort int(11) DEFAULT '0',
> 6   strasse varchar(50),
> 7   id_postcode int(11),
> 8   hausnummer int(11),
> 9   zusatz varchar(25),
> 10  postfach varchar(20),
> 11  leitcode varchar(18),
> 12  strassen_code char(3),
> 13  FZ char(3),
> 14  adresszusatz varchar(50),
> 15  PRIMARY KEY (pid_adresse),
> 16  KEY id_person (id_person)
> 17  );
>
> any suggestions ?

I think the problem is int(11) - maybe this should be numeric rather than int?
Or will integer on its own do?

Not sure about the "KEY" bit at the end - maybe

id_person INTEGER NOT NULL UNIQUE DEFAULT 0,

is sufficient?

Cheers,

Patrick

thanks for help,
I fixed this with:

CREATE TABLE Adresse (
  pid_adresse INT8 DEFAULT NEXTVAL('seqAdresse') PRIMARY KEY,
  id_person int4 DEFAULT '0' NOT NULL,
  PLZ varchar(50),
  id_ort int4 DEFAULT '0',
  strasse varchar(50),
  id_postcode int4,
  hausnummer int4,
  zusatz varchar(25),
  postfach varchar(20),
  leitcode varchar(18),
  strassen_code char(3),
  FZ char(3),
  adresszusatz varchar(50)
);

Cheers,
Andreas



::-----Urspr�ngliche Nachricht-----
::Von: Patrick Welche [mailto:prlw1@newn.cam.ac.uk]
::Gesendet: Montag, 11. September 2000 15:23
::An: Andreas Jerke
::Cc: Pgsql-General
::Betreff: Re: [GENERAL] can't read SQL dump from MySQL
::
::
::On Mon, Sep 11, 2000 at 12:38:54PM +0200, Andreas Jerke wrote:
::>
::> 1   CREATE TABLE Adresse (
::> 2   pid_adresse int(11) DEFAULT '0' NOT NULL auto_increment,
::> 3   id_person int(11) DEFAULT '0' NOT NULL,
::> 4   PLZ varchar(50),
::> 5   id_ort int(11) DEFAULT '0',
::> 6   strasse varchar(50),
::> 7   id_postcode int(11),
::> 8   hausnummer int(11),
::> 9   zusatz varchar(25),
::> 10  postfach varchar(20),
::> 11  leitcode varchar(18),
::> 12  strassen_code char(3),
::> 13  FZ char(3),
::> 14  adresszusatz varchar(50),
::> 15  PRIMARY KEY (pid_adresse),
::> 16  KEY id_person (id_person)
::> 17  );
::>
::> any suggestions ?
::
::I think the problem is int(11) - maybe this should be numeric
::rather than int?
::Or will integer on its own do?
::
::Not sure about the "KEY" bit at the end - maybe
::
::id_person INTEGER NOT NULL UNIQUE DEFAULT 0,
::
::is sufficient?
::
::Cheers,
::
::Patrick
::

On Mon, 11 Sep 2000, Andreas Jerke wrote:

> Hi,
>
> I'm updating from MySQL to PostgrSQL. Therefor I use
> the mysqldump tool for exporting CREATE and INSERT statements
> from all tables and rows.
>
> When I try to import the SQL file into PostgreSQL with
> psql --> \i filename
>
> I get the following error:
>
> line 17:parser: parse error at or near "("
>
> My SQL statement:
>
> <schnipp>
>
> 1   CREATE TABLE Adresse (
> 2   pid_adresse int(11) DEFAULT '0' NOT NULL auto_increment,
> 3   id_person int(11) DEFAULT '0' NOT NULL,
> 4   PLZ varchar(50),
> 5   id_ort int(11) DEFAULT '0',
> 6   strasse varchar(50),
> 7   id_postcode int(11),
> 8   hausnummer int(11),
> 9   zusatz varchar(25),
> 10  postfach varchar(20),
> 11  leitcode varchar(18),
> 12  strassen_code char(3),
> 13  FZ char(3),
> 14  adresszusatz varchar(50),
> 15  PRIMARY KEY (pid_adresse),
> 16  KEY id_person (id_person)
> 17  );

It's probably complaining about the
KEY id_person (id_person)
line since I don't think that's a
standard syntax or at least it doesn't
look like syntax postgres understands.
What's the intent behind the line?

On Mon, Sep 11, 2000 at 03:46:26PM +0200, Andreas Jerke wrote:
> thanks for help,
> I fixed this with:
>
> CREATE TABLE Adresse (
>   pid_adresse INT8 DEFAULT NEXTVAL('seqAdresse') PRIMARY KEY,
                INT4 would do the job here.

>   id_person int4 DEFAULT '0' NOT NULL,
>   PLZ varchar(50),
>   id_ort int4 DEFAULT '0',
>   strasse varchar(50),
>   id_postcode int4,
>   hausnummer int4,
>   zusatz varchar(25),
>   postfach varchar(20),
>   leitcode varchar(18),
>   strassen_code char(3),
>   FZ char(3),
>   adresszusatz varchar(50)
> );

Integer Types

 ___mysql____________ postgress_____________
 int1 = tinyint(4)    -            =    8Bit
 int2 = smallint(6)   int2         =   16Bit
 int3 = mediumint(9)  -            =   24Bit
 int4 = int(11)       int4         =   32Bit
 int8 = bigint(20)    int8         =   64Bit

i think that the mysql syntax like tinyint(4) stands for less than 4 digist.

Chears,
Gunnar von Boehn
--
LinuxHaus Stuttgart                    | Tel.:  +49 (7 11) 2 85 19 05
D-70734 Fellbach                       | Fax:   +49 (7 11) 5 78 06 92
Linux, Netzwerke, Consulting & Support | http://lihas.de