Обсуждение: Moving data from SQL Server to Postgres via VFP ODBC Driver
I am attempting to move data from a SQL Server 2008 database to Postgres v8.4. I am using Visual Fox Pro as an intermediate step in this process. I open a connection to the SQL tables using the ODBC driver and at the same time open a connect to the PostGres table using PostgresSQL30. The reason I am doing it this way is I cannot seem to overcome issues concerning bit fields in SQL Server and Booleans in Postgres. Using VFP I can do a quick and dirty conversion. However, the problem is that when I look at the data in Postgres it converted successfully with the exception of varying character fields which have been padded to their maximum length. My insert statement is in the form : Insert into PostgresTableName Values(m.field1, trim(m.field2....) The m. systax is because I am using memory variables in VFP. The data contained in the m.fields has all be trimmed if it is character type. Is there any way to keep varying character fields from being padded out to their maximum length. Thanks!
On Mon, Aug 9, 2010 at 10:13 AM, Calvin <calvinsmith266@hotmail.com> wrote: > I am attempting to move data from a SQL Server 2008 database to Postgres v8.4. I > am using Visual Fox Pro as an intermediate step in this process. I open a > connection to the SQL tables using the ODBC driver and at the same time open a > connect to the PostGres table using PostgresSQL30. The reason I am doing it this > way is I cannot seem to overcome issues concerning bit fields in SQL Server and > Booleans in Postgres. Using VFP I can do a quick and dirty conversion. However, > the problem is that when I look at the data in Postgres it converted > successfully with the exception of varying character fields which have been > padded to their maximum length. My insert statement is in the form : It would seem that the string padding is taking place on the SQL-Server side. Is there anyway to verify if it is treating the varchar type as char in its odbc driver? If you need to you can put the trimming operation in before insert trigger functions on these columns. But I expect this solution is not optimal. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Mon, Aug 9, 2010 at 11:31 AM, calvin smith <calvinsmith266@hotmail.com> wrote: > Thank you for your reply. Actually I am dumping the data from SQLServer into > Visual FoxPro memory variables on a record by record basis and then > checking the datatype for each memory variable.. If the data type is VFP > character I am trimming the value so that I KNOW that what is going into my > Insert statement is trimmed data. I haven't used VFP. Is it an ETL tool? Another ETL tool to consider (its open source) that is JDBC based can be found here: http://sourceforge.net/projects/pentaho/files/Data%20Integration/4.0.0-stable/ Also, I don't know if you seen these migration resources: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server Hopefully there is something useful there. Also don't forget to reply-all so that the mailing list is included so that others can reply. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Calvin <calvinsmith266@hotmail.com> writes:
> Is there any way to keep varying character fields from being padded out to their
> maximum length.
I suspect that your "varying" fields are in fact declared as just
CHAR(n). Use VARCHAR(n), or if there isn't a particularly good reason
to have a specific length limit, use TEXT.
regards, tom lane