Re: Miigration Wizard ignores not nulls & defau - Mailing list pgadmin-support

By all means - distribute it far and wide.  I have no copyrights or claims on it.  
 
Looking forward to the day when techdocs gets it's stuff together.  I've got lots of little things like this to post.
 
Roman

    -----Original Message----- 
    From: Dave Page [mailto:dpage@vale-housing.co.uk] 
    Sent: Tue 5/20/2003 7:59 AM 
    To: Roman Fail; Corneliu GALEV 
    Cc: pgadmin-support@postgresql.org 
    Subject: RE: [pgadmin-support] Miigration Wizard ignores not nulls & defau
    
    

    Thanks Roman. Do you mind if I incorporate this into the pgAdmin docs?
    
    Regards, Dave.
    
    > -----Original Message-----
    > From: Roman Fail [mailto:rfail@posportal.com]
    > Sent: 20 May 2003 15:41
    > To: Corneliu GALEV
    > Cc: pgadmin-support@postgresql.org; Roman Fail
    > Subject: Re: [pgadmin-support] Miigration Wizard ignores not
    > nulls & defau
    >
    >
    > I have a little HOWTO on migrating binary data from MSSQL
    > when I moved a huge table over.  I offered it up on
    > techdocs.postgresql.org about 6 months ago, but no one
    > responded.   For anything not involving binary data, I found
    > the pgAdmin2 Migration Wizard to be my favorite tool! 
    >
    > 
    >
    > 1. On MSSQL, create a User-Defined Function that can convert
    > each binary byte into an escaped-octal byte (which is the
    > only way PostgreSQL can read in binary data).  Use the
    > following statement as a guideline:
    >
    > 
    >
    > CREATE FUNCTION [dbo].[vbin2oct] (@vbin VARBINARY(255)) 
    >
    > RETURNS VARCHAR(1275)
    >
    > AS 
    >
    > BEGIN
    >
    > /* Converts a VARBINARY to a string of escaped octal values
    >
    > suitable for import into PostgreSQL.  Written for MS SQL Server 2000.
    >
    > This should also work on SQL 7.0, but the return length must
    > be shortened
    >
    > to 255 characters (which means the input varbinary should be
    > no more than
    >
    > 50 characters, since every input character results in 5 for
    > the output).
    >
    > 07 JAN 2003 - Roman Fail (rfail@posportal.com) 
    >
    > */
    >
    > DECLARE @p1 INTEGER, @p8 INTEGER, @p64 INTEGER,
    >
    >                   @int INTEGER, @i INTEGER, @len INTEGER,
    >
    >                   @octal VARCHAR(1275)
    >
    > SET ANSI_PADDING OFF
    >
    > IF @len IS NULL
    >
    >     SELECT @len = LEN(@vbin)
    >
    > SET @i = 1
    >
    > SET @octal = ''
    >
    > WHILE @i <= @len
    >
    >   BEGIN
    >
    >     SELECT @int = substring(@vbin, @i, 1)
    >
    >     SET @p64 = FLOOR(@int/64)
    >
    >     SET @p8 = (@int - (@p64 * 64))/8
    >
    >     SET @p1 = @int % 8
    >
    >     SET @octal = @octal + '\\'+
    >
    >            CAST(@p64 AS CHAR(1)) +    
    >
    >            CAST(@p8 AS CHAR(1)) +    
    >
    >            CAST(@p1 AS CHAR(1))    
    >
    >     SET @i = @i + 1
    >
    >   END
    >
    > SET ANSI_PADDING ON
    >
    > RETURN(RTRIM(LTRIM(@octal)))
    >
    > END
    >
    > 
    >
    > IMPORTANT NOTE: If the last byte of a binary field is equal
    > to the hexadecimal value '20' (same as decimal '32' or ASCII
    > 'space'), the MSSQL LEN() function will truncate it (so no
    > octal character is created).  The SET ANSI_PADDING OFF
    > statement is essential to prevent this problem.
    >
    > 
    >
    > 2. Start bcp for the table.  My server took about 1.5 hours
    > to generate 24 million rows (must enter the actual sa
    > password & server name in correct spot).
    >
    >        a. Execute bcp_batchdetail.bat on MSSQL server, which
    > contains only one command:
    >
    >         bcp "SELECT primaryKeyID,
    > dbo.vbin2oct(myVarBinaryField), otherField FROM myTable"
    > queryout c:\tabledata.txt -k -U "sa" -P "<sapassword>" -c -S
    > <myservername>
    >
    > 
    >
    > 
    >
    > 3. Use FTP to transfer tabledata.txt from MSSQL server to PG
    > server.  One of the servers must have an FTP server daemon
    > installed.  Make sure to use FTP ASCII mode (this will strip
    > Carriage Return characters from the file, which psql COPY
    > won't recognize).  You can use other methods to transfer the
    > file, but the CR's must be removed somehow.  Unfortunately,
    > after much research I could not get 'bcp' to output JUST line
    > feeds at the end of each line.
    >
    > 
    >
    > 4. Using pgAdmin2, psql, or the tool of your choice create
    > the table definition on the PostgreSQL server.  If you have
    > an MSSQL IDENTITY column in your table, you will first need
    > to manually CREATE SEQUENCE and set the appropriate start
    > value for the sequence, then include a DEFAULT
    > nextval(<mysequence>) for the column.  For example:
    >
    >    CREATE SEQUENCE mytable_primarykeyid_key
    >     START 24492190
    >     INCREMENT 1
    >     MAXVALUE 9223372036854775807
    >     MINVALUE 1
    >     CACHE 1;
    >
    > 
    >
    >   CREATE TABLE mytable (
    >     primarykeyid integer DEFAULT
    > nextval('"mytable_primarykeyid_key"'::text) NOT NULL,
    >     myvarbinaryfield bytea,
    >     otherfield character varying(16)
    >
    >    );
    >
    >
    > (Note the proper use of single/double quotes, and all names
    > have been folded to lower case)
    >
    > 
    >
    > 5. On the PostgreSQL server, run psql and issue the command:
    >
    > COPY batchdetail FROM '/home/postgres/tabledata.txt'; 
    >
    > 
    >
    > For me, this took 45 minutes for 24 million rows - about a 10GB file.
    >
    > 
    >
    > 6. Re-create indexes, foreign keys, and permissions manually.
    >
    > 
    >
    > 7. Run VACUUM FULL VERBOSE and then ANALYZE to get the house in order.
    >
    > 
    >
    > 8. Test database queries using pgAdminII or psql command
    > line.  Use EXPLAIN ANALYZE for execution plans to be displayed.
    >
    > 
    >
    > Hope this helps you out.
    >
    > 
    >
    > Roman Fail
    >
    > POS Portal, Inc.
    >
    > 
    >
    >       -----Original Message-----
    >       From: Corneliu GALEV [mailto:Corneliu.Galev@cefin.com]
    >       Sent: Mon 5/19/2003 11:16 PM
    >       To: Roger Gordon
    >       Cc: pgadmin-support@postgresql.org
    >       Subject: Re: [pgadmin-support] Miigration Wizard
    > ignores not nulls & defau
    >      
    >      
    >       I also noticed that varbinary fields are not migrated
    > properly. I maped varbinary to bytea and fileds values are
    > not migrated at all (I'm migrating from MSSQL7)
    >
    >               -----Original Message-----
    >               From: Roger Gordon [mailto:Roger@emojo.com]
    >               Sent: Monday, 19 May 2003 12:46
    >               To: pgadmin-support@postgresql.org
    >               Subject: [pgadmin-support] Miigration Wizard
    > ignores not nulls & default values
    >              
    >              
    >
    >               Hi,
    >
    >               
    >
    >               Noticed 2 problems migrating from SQLServer
    > 2000 using pgAdmin 1.4.12:
    >
    >               1.      NOT NULLs other than the primary key
    > code migrate over as NULLs
    >               2.      Default values are not migrated
    >
    >               I'm writing a script to fix these errors, but
    > any advice would be most appreciated....
    >
    >               
    >
    >               Thanks,
    >
    >               Roger
    >
    >               
    >
    >
    > ---------------------------(end of
    > broadcast)---------------------------
    > TIP 2: you can get off all lists at once with the unregister command
    >     (send "unregister YourEmailAddressHere" to
    > majordomo@postgresql.org)
    >