Thread: Migration from SQLServer
Hi All: I have just started my migration activity from MS SQLServer to postgreSQL. While the initial setup and tests are working, I had to put all my table names within quotations. Is there something I have missed in configuration or does postgreSQL work like this ? I have to go thru code now and make changes. (I might have to do that after all this if the data types are not matching - Over all not a great day since I found this out and LAL lost today Any help will be appreciated. Thanks in advance. Re Tapan
On Fri, 16 May 2003, Tapan Trivedi wrote: > Hi All: > > I have just started my migration activity from MS SQLServer to postgreSQL. > > While the initial setup and tests are working, I had to put all my table names within quotations. You don't say _why_ you had to put all your table names in double quotes. I presume it's because they are mixed or upper case. > Is there something I have missed in configuration or does postgreSQL work like this ? PostgreSQL folds identifiers to lower case, where other systems fold to upper, unless they are enclosed in double quotes in which case case is preserved. > I have to go thru code now and make changes. (I might have to do that after all this if the data types are not matching - Over all not a great day since I found this out and LAL lost today If you had to quote your table names in your create scripts how come you're having to also change the code to use quote marks? Surely you changed the create scripts because the code was already using them. > > Any help will be appreciated. > > Thanks in advance. > > Re Tapan -- Nigel J. Andrews
Thanks for the quick response. a. I have to put the table names in double quotes since postgreSQL throws out Relation ".." not found error. The only way I can access the tables are by including them in quotes and maintaining the case. b. Create script. Let me explain - I generated the script from MSSQL and it gave a whole lot of issues (for SQL statements and types) . When I resolved all of them, I encountered Relation ".." not found error. The only way I could resolve it was by giving the table names in quotes. I guess I should have converted all table names in lowercase and then all would be OK. What I am trying to find out now is - Is there anyway I can avoid dropping the database and recreating it ?? Re Tapan Nigel J. Andrews wrote: >On Fri, 16 May 2003, Tapan Trivedi wrote: > > > >>Hi All: >> >>I have just started my migration activity from MS SQLServer to postgreSQL. >> >>While the initial setup and tests are working, I had to put all my table names within quotations. >> >> > >You don't say _why_ you had to put all your table names in double quotes. I >presume it's because they are mixed or upper case. > > > >>Is there something I have missed in configuration or does postgreSQL work like this ? >> >> > >PostgreSQL folds identifiers to lower case, where other systems fold to upper, >unless they are enclosed in double quotes in which case case is preserved. > > > >>I have to go thru code now and make changes. (I might have to do that after all this if the data types are not matching - Over all not a great day since I found this out and LAL lost today >> >> > >If you had to quote your table names in your create scripts how come you're >having to also change the code to use quote marks? Surely you changed the >create scripts because the code was already using them. > > > >> >>Any help will be appreciated. >> >>Thanks in advance. >> >>Re Tapan >> >> > > > >
On Fri, 16 May 2003, Tapan Trivedi wrote: > Thanks for the quick response. > > a. I have to put the table names in double quotes since postgreSQL > throws out Relation ".." not found error. The only way I can access the > tables are by including them in quotes and maintaining the case. > > b. Create script. Let me explain - I generated the script from MSSQL and > it gave a whole lot of issues (for SQL statements and types) . When I > resolved all of them, I encountered Relation ".." not found error. The > only way I could resolve it was by giving the table names in quotes. I > guess I should have converted all table names in lowercase and then all > would be OK. > > What I am trying to find out now is - Is there anyway I can avoid > dropping the database and recreating it ?? Sure, take a look at relname in pg_class and typname in pg_type. The other columns in those table will help you decide who owns what and what schema they're in so you can change only those ones you want to. I don't think there's any more tables that hold the names but it's worth checking. It'll be safer to reload your functions, views, tirggers? and possibly rules having done this and amended them as appropiate. > > Nigel J. Andrews wrote: > > >On Fri, 16 May 2003, Tapan Trivedi wrote: > > > >>Hi All: > >> > >>I have just started my migration activity from MS SQLServer to postgreSQL. > >> > >>While the initial setup and tests are working, I had to put all my table names within quotations. > > > >You don't say _why_ you had to put all your table names in double quotes. I > >presume it's because they are mixed or upper case. > > > > > > > >>Is there something I have missed in configuration or does postgreSQL work like this ? > > > >PostgreSQL folds identifiers to lower case, where other systems fold to upper, > >unless they are enclosed in double quotes in which case case is preserved. > > > >>I have to go thru code now and make changes. (I might have to do that after all this if the data types are not matching - Over all not a great day since I found this out and LAL lost today > > > >If you had to quote your table names in your create scripts how come you're > >having to also change the code to use quote marks? Surely you changed the > >create scripts because the code was already using them. > > -- Nigel J. Andrews
On Fri, 16 May 2003, Nigel J. Andrews wrote: > On Fri, 16 May 2003, Tapan Trivedi wrote: > > > b. Create script. Let me explain - I generated the script from MSSQL and > > it gave a whole lot of issues (for SQL statements and types) . When I > > resolved all of them, I encountered Relation ".." not found error. The > > only way I could resolve it was by giving the table names in quotes. I > > guess I should have converted all table names in lowercase and then all > > would be OK. > > > > What I am trying to find out now is - Is there anyway I can avoid > > dropping the database and recreating it ?? > > Sure, take a look at relname in pg_class and typname in pg_type. The other > columns in those table will help you decide who owns what and what schema > they're in so you can change only those ones you want to. Oh, and don't forget to start a transaction before making the changes so that you get the chance to rollback if you make a mistake, these are system tables. -- Nigel Andrews
Thanks Nigel but too late :-(( Did try something and got really messed up. I guess database recreation is the only alternative left. Actually wanted to do this in 2 hrs time - already am late by a day. Thanks anyway. Tapan Nigel J. Andrews wrote: >On Fri, 16 May 2003, Nigel J. Andrews wrote: > > > >>On Fri, 16 May 2003, Tapan Trivedi wrote: >> >> >> >>>b. Create script. Let me explain - I generated the script from MSSQL and >>>it gave a whole lot of issues (for SQL statements and types) . When I >>>resolved all of them, I encountered Relation ".." not found error. The >>>only way I could resolve it was by giving the table names in quotes. I >>>guess I should have converted all table names in lowercase and then all >>>would be OK. >>> >>>What I am trying to find out now is - Is there anyway I can avoid >>>dropping the database and recreating it ?? >>> >>> >>Sure, take a look at relname in pg_class and typname in pg_type. The other >>columns in those table will help you decide who owns what and what schema >>they're in so you can change only those ones you want to. >> >> > >Oh, and don't forget to start a transaction before making the changes so that >you get the chance to rollback if you make a mistake, these are system tables. > > >-- >Nigel Andrews > > > >
--- Tapan Trivedi <ttrivedi@webifyservices.com> wrote: > Thanks Nigel but too late :-(( > > Did try something and got really messed up. I guess > database recreation > is the only alternative left. Actually wanted to do > this in 2 hrs time - > already am late by a day. This need not take a long time (unless you have a really huge amount of data). Just dump the schema (table definitions etc.) and the data separately (man pg_dump for details). Check that the dump files are ok, then drop the database. Edit the schema dump to force everything to lower case (easy with a small script). Restore the database: psql < schema.dump psql < data.dump and you are fixed. __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com