Migrating a software (application or database or any layer) that is running on one vendor’s platform to another vendor’s platform is never easy or risk-free. Same applies to relational databases as well. Though fundamentally all the relational database are supposed to adhere to same relational theory, but commercial products have some vendor specific implementations of commands and functions. For example relational databases generally adhere to ANSI-SQL syntax, but there are 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-

+ 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
);
___________________________________

If you notice the operator uses the function that we have defined above.

Here is how it would work-

___________________________________

postgres=# select ‘sameer’ + ‘kumar’;
?column?
————-
sameerkumar
(1 row)
___________________________________

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 thereafter 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.