As the title suggests, we're going to be migrating from MS SQL Server 2008 to PostgreSQL 9.5 within the next couple of years and I was wondering if anyone had any experience they could share, particularly a decent client?
I've been asked to look for a new client solution that the information analysts (very accustomed to SQL Server but should easily switch to Postgres) and contract performance officers (generally little experience) can use which has the following:
I've struggled to find a client that does all of the above, especially the second point as sometimes we need to import tables with over 500 fields, I can't do this server side via some type of copy as the server won't have access to the user's files (I'd assume some kind of staging area which both the user and server have access to would remedy this but nonetheless I'd like to have this feature as there may be some Information Governance issues).
The best that I've found so far is Toad Data Point however it doesn't recognise the Postgres extensions to SQL in the editor and it requires a specific database in the connection string so I'd need to create multiple connections for each database (I've suggested we move to a single database with properly locked down schemas to overcome this issue). Then there's the expense; we're moving over to Postgres partly to reduce infrastructure costs so we'd like to spend as little as possible.
I'm also involved in the migration of our stored procedures, functions and existing tables so any advice in that would be appreciated.
I've been asked to look for a new client solution that the information analysts (very accustomed to SQL Server but should easily switch to Postgres) and contract performance officers (generally little experience) can use which has the following:
- A decent SQL Editor that can format statements and has code completion.
- Ability to import csv files as a new table without needing to create a table first.
- Interpret empty strings as null values when desired on import.
- Make a distinction between empty strings and null values in the results view.
I've struggled to find a client that does all of the above, especially the second point as sometimes we need to import tables with over 500 fields, I can't do this server side via some type of copy as the server won't have access to the user's files (I'd assume some kind of staging area which both the user and server have access to would remedy this but nonetheless I'd like to have this feature as there may be some Information Governance issues).
The best that I've found so far is Toad Data Point however it doesn't recognise the Postgres extensions to SQL in the editor and it requires a specific database in the connection string so I'd need to create multiple connections for each database (I've suggested we move to a single database with properly locked down schemas to overcome this issue). Then there's the expense; we're moving over to Postgres partly to reduce infrastructure costs so we'd like to spend as little as possible.
I'm also involved in the migration of our stored procedures, functions and existing tables so any advice in that would be appreciated.