Thread: 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