DISQUS

DISQUS Hello! Code Spatter is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Jump to original thread »
Author

Tips for MySQL to PostgreSQL Switch

Started by Greg Allard · 10 months ago

If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get into any reasons why to move to PostgreSQL or not. There are already
many discussions on
the topic.
Create Syntax
The first five listed need to be done in order ... Continue reading »

8 comments

  • "Remove all instances of NOT NULL"
    WTF!? This can seriously break many schemas. Don't do this.

    Postres supports multiple-row inserts only in latest versions (8.3+ IIRC).

    another useful replacement may be CURRENT_TIMESTAMP with NOW() and timestamp with timestamp without time zone, etc.
  • @kL
    Thanks for the comment. I updated the post to cross out the NOT NULL part, I think I meant that only for the auto_increment part, but messed it up.

    I'll check out the newer versions to see what's been changed. I was on 7.4.7.
  • - datetime in MySQL is equal to timestamp in PostgreSQL. There is no equivalent of MySQL's timestamp data type in PostgreSQL

    - An enum column in MySQL is equal to a varchar with a check constraint in PostgreSQL, for example:
    column_name enum('foo', 'bar') is equal to column_name varchar(3) check (column_name IN ('foo', 'bar'))

    - The blob datatype in MySQL is equal to a bytea datatype in PostgreSQL

    Also, when truncating a table, I do not believe PostgreSQL resets the sequence for serial columns, so it will need to be done manually.

    Obviously there are more differences when moving to PostgreSQL, but these are most of the big ones for CREATE TABLE statements.

    Just a little FYI. I wrote these all out, but forgot my email. Your blog is very unfriendly to comments without an email, as in I lost my comments.
  • I removed the requirement for e-mail. I had no idea WordPress handled it so poorly since I was always logged in.
  • can you give me the sql equivalent of this to postgresql, i'll be using phppgadmin thanks...

    CREATE TABLE manila_area_tbl
    (
    area_no tinyint(4) NOT NULL auto_increment,
    area_name varchar(65) NOT NULL,
    disp_flag char(1) NOT NULL default '1',
    disp_no tinyint(4) NOT NULL,
    del_flag char(1) NOT NULL default '0',
    PRIMARY KEY (`area_no`),
    KEY disp_no` (`disp_no`),KEY `del_flag` (`del_flag`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;
  • I noticed another case I may have missed, so I edited the post to catch when the key may be a smallint.

    Try running these 4 queries.


    CREATE TABLE manila_area_tbl
    (
    area_no SERIAL,
    area_name varchar(65) NOT NULL,
    disp_flag char(1) NOT NULL default 1,
    disp_no smallint NOT NULL,
    del_flag char(1) NOT NULL default 0,
    PRIMARY KEY (area_no)
    ) ;

    CREATE INDEX disp_no ON manila_area_tbl (disp_no);

    CREATE INDEX del_flag ON manila_area_tbl (del_flag);

    ALTER SEQUENCE manila_area_tbl_area_no_seq RESTART WITH 25;
  • Hello,
    do you know how to do something like this ?

    ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) + 1 FROM univers);

    Thanks
    Emilien
  • If that query isn't working, try moving the +1 out of the select statement like this:

    ALTER SEQUENCE univers_id_seq RESTART WITH (SELECT max(id) FROM univers)+1;

Add New Comment

Returning? Login