Migrating from any kind of software running on a product from one vendor to a product from another vendor is never easy or risk-free. You always have to go through proper testing cycles and at times there might be changes required in application working on top of product (OS, Database, App server, Web Server etc) being migrated.
Same applies to relational databases as well. Though fundamentally all the relational database are supposed to adhere to same relational theory, still they have some specific implementations of commands and functions. This leads to application changes and with those changes, comes a need of additional efforts for re-testing. The pain is quite much relieved by the fact that relational databases tries to adhere to ANSI-SQL syntax. But they all have few exceptions while they try to comply with ANSI standards.
In my day job as DB Solution Architect, I come across one or two new migration cases almost every month. And at times these are Migration from SQL Server to PostgreSQL. In my experience and opinion SQL Server is one implementation of Relational Theory which has most deviations from ANSI syntax. For example- all RDBMS use double pipe [ || ] as concatenation operator whereas SQL Server (for some good or bad reason) chose it to be +.
Another feature which I have seen being a pain while migrating is 'case-insensitive searches'. All databases support that feature if you would use UPPER( ) function on string before comparing them. But that is the default and only behavior in SQL Server database, if you have created with a collation ending with CI_AS or CI_AI (meaning case insensitive and ascent sensitive/insensitive). I have faced that challenge of migrating these customers.
When you look at the database while assessing it for migration it all looks fine. The SQL Server database has only tables (no programmable objects e.g. procedures or functions) with no 'funny' data-types [again the whole world calls timestamp as data-time and SQL Server uses it as a binary datatype for Optimistic Locking :) ]. You migrate it over (well quite easily), and finally when you connect the application you start getting error left-right-and-center!
Well PostgreSQL is not just a stable alternative for proprietary databases but is also offers great flexibility to make itself a popular alternative. Let me share two examples where-in PostgreSQL can really make it easy for you to migrate over.
In PostgreSQL you can create user defined casts and user defined operators. So to over come the issues I have mentioned above, let me create user defined operators and test-
1. + as Concatenation operator-
Let's first create a function which can concatenate two strings
___________________________________
CREATE FUNCTION
concat_with_plus(varchar, varchar)
RETURNS varchar
AS $$ SELECT $1 || $2 $$
LANGUAGE SQL;
___________________________________
Now let's create a custom operator which has two arguments ( left=string and right=string )
___________________________________
CREATE OPERATOR + (
LEFTARG = varchar,RIGHTARG = varchar ,
PROCEDURE = concat_with_plus
);
___________________________________
Here is how it would work-
___________________________________
postgres=# select 'sameer' + 'kumar';
?column?
-------------
sameerkumar
(1 row)
___________________________________
2. Enable Case-insensitive search for all the tables and columns
All we need to do is create an operator '=' overriding the default behavior.
Before that let's first define a function which matches two string irrespective of their case.
______________________________________
CREATE FUNCTION
varchar_equal_caseinsenstive(varchar, varchar)
RETURNS boolean
AS $$ SELECT upper($1)=upper($2) $$
LANGUAGE SQL;
______________________________________
Let's now create an operator '=' using this functions
______________________________________
CREATE OPERATOR = (
LEFTARG = varchar,
RIGHTARG = varchar,
PROCEDURE = varchar_equal_caseinsenstive,
COMMUTATOR = =,
NEGATOR = !=,
HASHES, MERGES
);
______________________________________
Here is how it would work-
______________________________________
postgres=# SELECT * FROM test_varchar
postgres-# WHERE col1='SAMEER';
col1
--------
sameer
Sameer
SameeR
SAmeeR
Sameer
<<more rows in result>>
______________________________________
And if you define an index over UPPER(COL1),
______________________________________
CREATE INDEX upper_col_test_varchar ON
test_varchar(UPPER(col1));
______________________________________
it would be used for index scans/bitmap scans-
____________________________________________________________________________
postgres=# EXPLAIN SELECT * FROM test_varchar WHERE col1='SAMEER';
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on test_varchar (cost=11.77..394.81 rows=432 width=10)
Recheck Cond: (upper((col1)::text) = 'SAMEER'::text)
-> Bitmap Index Scan on upper_col_test_varchar (cost=0.00..11.66 rows=432 width=0)
Index Cond: (upper((col1)::text) = 'SAMEER'::text)
Planning time: 2.685 ms
(5 rows)
____________________________________________________________________________
These are one of the most frequently deployed tricks when I migrate my customer from SQL Server to PostgreSQL. These wrappers are written inside your database. You can create them on template0 database and hence all the databases created there after will have these casts. This is not only easy but also does away with the requirement of changing application code.
Stay tuned and we will be sharing more such tips and tricks which helps you break away from the lock-in of proprietary database vendors and move to a much more cost effective solution.
In case you are looking for professional services for migration you may contact my employers Ashnik (www.ashnik.com | success@ashnik.com). We offer wide range of PostgreSQL services - Migration, Heterogeneous Replication, Hardening, Tuning, High Availability, DR Setup and anything that has to do with PostgreSQL.
