Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Convert PostgreSQL Dump to MySQL

    Hi Chaps,

    been searching for a while, but cannot find a suitable answer..yet.

    I'm trying to rebuild a site (currently Ruby/PostgreSQL) into PHP/MySQL.

    I have a PostgreSQL dump .sql file, which looks like:

    Code:
    CREATE TABLE addresses (
    id integer NOT NULL,
    firstname character varying(255),
    lastname character varying(255),
    address1 character varying(255),
    address2 character varying(255),
    city character varying(255),
    state_id integer,
    zipcode character varying(255),
    country_id integer,
    phone character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    state_name character varying(255),
    alternative_phone character varying(255)
    );
    
    ALTER TABLE public.addresses OWNER TO XXXXXX;
    
    CREATE SEQUENCE addresses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
    
    ALTER TABLE public.addresses_id_seq OWNER TO XXXXXX;
    
    ALTER SEQUENCE addresses_id_seq OWNED BY addresses.id;
    
    SELECT pg_catalog.setval('addresses_id_seq', 348, TRUE);
    But there are no INSERT INTO's, rather (I think the dump uses tab to seperate the values?):
    Code:
    COPY addresses (id, firstname, lastname, address1, address2, city, state_id, zipcode, country_id, phone, created_at, updated_at, state_name, alternative_phone) FROM stdin;
    1 Mr CustomerA 1 New St Somewhere \N PS1 2BC 213 07XXX XXX XXX 2010-06-24 11:44:25.130213 2010-06-24 12:35:14.501107 SomeState \N
    1 Mr CustomerB 2 New St Somewhere \N PS2 3AB 213 07XXX XXX XXX 2010-06-25 11:00:25.130213 2010-06-25 12:00:14.501107 SomeState \N
    I have tried a couple of converters, but it doesn't include any data (I'm guessing as there is no INSERT INTO syntax). Can someone shed some light on this, whether it is possible, and what's the best method of doing so (automatic/manual)?

    If there are examples of using MySQL LOAD DATA INFILE, which explains this method (using SQL tab seperators, rather than CSV)....please help!

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. convert your CREATE TABLE statements manually, in a text editor

    you might be able to find a conversion program to do this, but i wouldn't bother, as you need to inspect every datatype and change them appropriately

    for example, state_id and country_id are suboptimal (INTEGER is clearly too large, but using international character codes would be even better), zipcode as VARCHAR(255) is silly, timestamps need to be rethought, etc.

    2. write some application code to insert field separators in the appropriate places in the data file, then use LOAD DATA INFILE to load them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    6
    Hi, thanks for the reply, most helpful....think I can do that.

    One question though, using PHP, how would you replace a tab with (say) a comma? preg_replace?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what tab separators?

    the file has tab separators? then go directly to LOAD DATA INFILE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    6
    OK, so I CREATE the tables manually, setting the correct column attributes, then use LOAD DATA INFILE for each table? right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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