SQL Help

[frikkie6000]

I am importing the following query into php my admin

--
-- PostgreSQL database dump
--

--connect - alanbbr

SET search_path = public, pg_catalog;

--DROP SEQUENCE public.events_eid_seq;
--DROP SEQUENCE public.fuel_fid_seq;
--DROP SEQUENCE public.maintenance_mid_seq;
--DROP SEQUENCE public.vehicles_mid_seq;
--DROP TABLE public.events;
--DROP TABLE public.fuel;
--DROP TABLE public.maintenance;
--DROP TABLE public.vehicles;


CREATE TABLE vehicles (
    vid serial PRIMARY KEY,
    name character varying(20) NOT NULL,
    make character varying(40),
    model character varying(40),
    "year" integer,
    vin character varying(20)
);
REVOKE ALL ON TABLE vehicles FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE vehicles TO GROUP autotrak;
REVOKE ALL ON TABLE vehicles_vid_seq FROM PUBLIC;
GRANT ALL ON TABLE vehicles_vid_seq TO GROUP autotrak;


CREATE TABLE events (
    eid serial PRIMARY KEY,
    event character varying(64)
);
REVOKE ALL ON TABLE events FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE events TO GROUP autotrak;
REVOKE ALL ON TABLE events_eid_seq FROM PUBLIC;
GRANT ALL ON TABLE events_eid_seq TO GROUP autotrak;


CREATE TABLE fuel (
    fid serial PRIMARY KEY,
    vid integer NOT NULL references vehicles,
    date date NOT NULL,
    previous numeric(10,1),
    current numeric(10,1),
    price numeric(10,3),
    volume numeric(10,3)
);
REVOKE ALL ON TABLE fuel FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE fuel TO GROUP autotrak;
REVOKE ALL ON TABLE fuel_fid_seq FROM PUBLIC;
GRANT ALL ON TABLE fuel_fid_seq TO GROUP autotrak;


CREATE TABLE maintenance (
    mid serial PRIMARY KEY,
    vid integer NOT NULL references vehicles,
    eid integer NOT NULL references events,
    date date,
    odometer integer,
    notes character varying(800)
);
REVOKE ALL ON TABLE maintenance FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE maintenance TO GROUP autotrak;
REVOKE ALL ON TABLE maintenance_mid_seq FROM PUBLIC;
GRANT ALL ON TABLE maintenance_mid_seq TO GROUP autotrak;


INSERT INTO events (eid, event) VALUES (1, 'Change Oil');
INSERT INTO events (eid, event) VALUES (2, 'Rotate Tires');
INSERT INTO events (eid, event) VALUES (3, 'Change air filter');
INSERT INTO events (eid, event) VALUES (4, 'Change timing belt');

INSERT INTO vehicles (vid, name, make, model, "year", vin) VALUES (1, 'Legacy', 'Subaru', 'Legacy', 1991, '');

SELECT pg_catalog.setval ('events_eid_seq', 4, true);
SELECT pg_catalog.setval ('vehicles_vid_seq', 1, true);

Following error

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--connect - alanbbr

SET search_path = public, pg_catalog' at line 5

Can someone help pls thanks

 

[froot]

You should remove the PostgreSQL comments.

 

[Sinbad]

Your error is using a postgres script against a mysql DB

 

[froot]

Your error is using a postgres script against a mysql DB

I don't know all the statements but it looks like most (or all) will work.

 

[Sinbad]

I don't know all the statements but it looks like most (or all) will work.

SET search_path = public, pg_catalog;

There is the problem.

 

[frikkie6000]

You should remove the PostgreSQL comments.

Aaa I have never used postgresql so was a bit unsure about that

 

[frikkie6000]

I used the following converter

http://www.lightbox.ca/pg2mysql.php

Code as follow now

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone="+00:00";

CREATE TABLE vehicles (
vid int(11) auto_increment PRIMARY KEY,
name varchar(20) NOT NULL,
make varchar(40),
model varchar(40),
`year` int(11),
vin varchar(20)
, PRIMARY KEY(`vid`)
) TYPE=MyISAM;

CREATE TABLE events (
eid int(11) auto_increment PRIMARY KEY,
event varchar(64)
, PRIMARY KEY(`eid`)
) TYPE=MyISAM;

CREATE TABLE fuel (
fid int(11) auto_increment PRIMARY KEY,
vid int(11) NOT NULL references vehicles,
date date NOT NULL,
previous numeric(10,1),
current numeric(10,1),
price numeric(10,3),
volume numeric(10,3)
, PRIMARY KEY(`fid`)
) TYPE=MyISAM;

CREATE TABLE maintenance (
mid int(11) auto_increment PRIMARY KEY,
vid int(11) NOT NULL references vehicles,
eid int(11) NOT NULL references events,
date date,
odometer int(11),
notes text
, PRIMARY KEY(`mid`)
) TYPE=MyISAM;

INSERT INTO events (eid, event) VALUES (1, 'Change Oil');
INSERT INTO events (eid, event) VALUES (2, 'Rotate Tires');
INSERT INTO events (eid, event) VALUES (3, 'Change air filter');
INSERT INTO events (eid, event) VALUES (4, 'Change timing belt');
INSERT INTO vehicles (vid, name, make, model, `year`, vin) VALUES (1, 'Legacy', 'Subaru', 'Legacy', 1991, '');


On import error

Error
SQL query:

CREATE TABLE vehicles(

vid INT( 11 ) AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 20 ) NOT NULL ,
make VARCHAR( 40 ) ,
model VARCHAR( 40 ) ,
`year` INT( 11 ) ,
vin VARCHAR( 20 ) ,
PRIMARY KEY ( `vid` )
) TYPE = MYISAM ;

MySQL said:

#1068 - Multiple primary key defined

Is there perhaps a easier way to do this ?

 

[Sinbad]

take out the PRIMARY KEY (`table name`) stuff from each create statement.

 

[frikkie6000]

take out the PRIMARY KEY (`table name`) stuff from each create statement.

Got it right thanks