I have several tables in a SQL Anywhere 10 database that I'm converting to PostgreSQL 8.4.2. In ASA we did not use any GUID columns but we are in Postgres.
I have one table that is citystateinfo and the primary key is a UUID and it is automatically generated using the contrib module oosp-uuid. That works fine.
We are also using the citext contrib module on most of our text columns so that we can do case insensitive searches. We now have this working properly.
Here is a simple select that is used to get the UUID primary key for the citystateinfo table to put into a terminaladdress table.
select "citystateinfoid" as KeyName from iss.citystateinfo where "cityname"='JACKSONVILLE' and "statecode"='FL' and "zipcode"='32226'.
In PGAdmin this returns the correct uuid in the proper format.
In the program that we're using to convert this data (a commerical product called DMC), when it is run on the tech support machine at the authors company, it returns the proper UUID to all columns where there is a matching row in the citystateinfo table.
On my machine the UUID that is returned is 16 bytes and I cannot make out any relevant numbers from the UUID key in the citystateinfo table. I've tried this in a Windows XP machine and a Windows 7 64 bit.
Now here is the weird thing. I did a complete backup of my database and had the author restore the data on his. It works every time there. He then did a pg_dumpall and I reloaded my db with his script and while his runs all of the queries fine, mine is still not returning the proper length UUID column.
I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2.
Does anyone have any idea on what could possibly be going on? It's running find on computers located in other locations but doesn't work properly on either one of my machines. It seems to me that it must be enviornmental. The author has both Windows 2008 server and Windows 7 64 bit.
Best Regards
Michael Gould