#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0

    MySQL -> PostgreSQL conversion troubles


    Hi there,

    I've been working on a website driven by phpmysql. Now the client wants it to be in PostGreSQL. I've no experience at all with PostGreSQL but I started converting the mysql database anyways. Here is what I made of my MySQL create table code:

    CREATE TABLE `content` (
    `autoID` int(11) NOT NULL auto_increment,
    `section` tinytext NOT NULL,
    `sectionID` tinytext NOT NULL,
    `subsection` tinytext NOT NULL,
    `subsectionID` tinytext NOT NULL,
    `content` longtext NOT NULL,
    `public` tinytext NOT NULL,
    `date` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`autoID`),
    UNIQUE KEY `autoID` (`autoID`)
    ) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=96 ;

    became in PostgreSQL

    CREATE TABLE "content" (
    "autoID" int8 NOT NULL,
    "section" text NOT NULL,
    "sectionID" text NOT NULL,
    "subsection" text NOT NULL,
    "subsectionID" text NOT NULL,
    "content" text NOT NULL,
    "public" text NOT NULL,
    "date" timestamp NOT NULL
    );
    CREATE UNIQUE INDEX "content_autoID_key" ON "content" ("autoID");

    I'm not sure if I did this correctly but it seems to work. Any hints and tips are more than welcome though!

    With some cutting and pasting and finding and replacing I converted the insert code into this:

    Met wat knip en plak werk en find and replace heb ik de content uit MySQL dump omgezet naar PostGrez. Hierbij kwam ik een probleem tegen:

    INSERT INTO "content" ("autoID", "section", "sectionID", "subsection", "subsectionID", "content", "public", "date") VALUES(1, 'Home', '1', 'Raves & amp ; parties', '0', 'Fake text fake text', 'show', '2003-11-14 16:26:25+01');

    Now I have a problem. And I think it's the insertion of:
    'Raves & amp ; parties'

    Because this piece of date contains a ; character phppgadmin won't complete the query. This wasn't a problem in phpmyadmin.

    Any solutions to this?? Can I change something in my datatypes settings in order to fix this?

    Thanx a million,
    Cheers,
    Hendricus
    Last edited by Hendricus; November 18th, 2003 at 01:21 PM.
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    What's the exact error you're getting on insert. One initial thought looking at the table setup for autoID are you sure you need it to be int8? Also if you need an autoincrementing number look into the SERIAL type, which is PG's version of autoincrement which just matches a SEQUENCE to a column.

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0

    error message


    Thanx for your reply! This is the error message:

    Warning: pg_exec() query failed: ERROR: Unterminated quoted string in /usr/local/httpd/htdocs/DBAdmin/db_readdump.php on line 33
    Error - /usr/local/httpd/htdocs/DBAdmin/db_readdump.php -- Line: 33
    PostgreSQL said: ERROR: Unterminated quoted string
    Your query:
    INSERT INTO "content" ("autoID", "section", "sectionID", "subsection", "subsectionID", "content", "public", "date") VALUES(1, 'Home', '1', 'blaat & nbsp ;

    See where it ends after the ;
    Eventhough the query was:

    INSERT INTO "content" ("autoID", "section", "sectionID", "subsection", "subsectionID", "content", "public", "date") VALUES(1, 'Home', '1', 'blaat & nbsp ; blaat', '0', '', 'show', '2003-11-14 16:26:25+01');

    Strange stuff. I just tried it again with phpMyAdmin and no problems there....

    Hendricus
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Strange, I just copied your create statement and your insert statement and it worked fine for me.

    Some questions:
    Are you inserting these manually via phppgadmin or are you importing via an import file?
    What version of postgres are you using?
    What version of phppgadmin do you have?
    If this is from a dump file, have you tried using psql to insert it from the command line (psql -U*username* -d *database* -f *dumpfilename* )?

    -b
    PostgreSQL, it's what's for dinner...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    Thanx again for your help!

    I'm inserting these through 'Run SQL query/queries on database' via phpPgAdmin

    Not sure about the version, when I clicked 'home' in phpPgadmin it says;

    PostgreSQL Admin Suite
    PostgreSQL 7.1.3 running on local:

    Haven't tried using psql to insert. I have no experience using command line. Can't really experiment with this, might mess up a database which is not mine

    Cheers,
    Hendricus
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0
    I just experemented some more and if I manually insert data row by row it has no problems with data containing a ;

    Weird stuff
  12. #7
  13. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Sounds like you're using an older version of phppgadmin is the background bluish or yellowish/brownish?

    Strange, I'm using 7.1.3, myself, and I have tried phpPgAdmin 3.1 and 2.4a and they both work when just copying and pasting the statements you've posted.

    If you're scared of losing data, you may want to backup the db. Older versions of phppgadmin have this feature (the newer version doesn't as they haven't been able to keep compatibility with pg's dumping utility), otherwise you may want to look into pg_dump from the command line.

    pg_dump -u -d *database* -f *outputfile* .

    Sorry I can't be of more help, but it just works for me. I even tried inserting an artificial line break where your semicolon is and it still worked.

    -b
    PostgreSQL, it's what's for dinner...
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Amsterdam
    Posts
    26
    Rep Power
    0

    thanx


    The background is bluish... looks almost the same as my phpmyadmin only way less functions...

    I made a backup... but I can't access the complete database. There is some other users on there as well.

    Looks like I will have to do it manually... (or have my classmate working with me on this do it )

    Thanx for your help anyway!

    Cheers,
    Hendricus

    btw. your SERIAL tip works like a charm!

IMN logo majestic logo threadwatch logo seochat tools logo