Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts

    MysQL to Postgres

    I have spent months developing a MySQL DB only to find that the sys admin of the project only uses postgres. So now I have to migrate to Postgres. Any ideas for saving time?
    You can not say you know how to do something, until you can teach it to someone else.

  • #2
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    So what's the problem? They're basically the same.

    id INT NOT NULL PRIMARY KEY auto_increment becomes id SERIAL NOT NULL PRIMARY KEY

    you have to use triggers on insert to emulate INSERT ... ON DUPLICATE KEY UPDATE ...

  • Users who have thanked ralph l mayo for this post:

    timgolding (11-13-2007)

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    postgres won't like any backticks you might have (`s), and if your export from mysql contains anything referencing table-types (myisam, innodb) lose them as postgres doesn't have such a concept

    The best thing is to install postgres, try and import the export you've done from mysql and see what doesn't work- there shouldn't be anything (much?) that won't work, once you've got an error-free import.

    With postgres, DDL operations ('create table' etc.), are revert-able when inside a transaction, so when doing the imports using 'begin;' and 'commit;
    will make the trial+error process a bit easier.
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • Users who have thanked GJay for this post:

    timgolding (11-13-2007)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •