This page documents differences between the current SQL Server database and the future PostgreSQL database. Some datatypes and functions that exist in one SQL implementation do not exist in the other. This page will grow and change as the project moves on.

Type Differences

Some Types that we use in SQL Server don't exist in PostgreSQL or they have different names. Here’s a list:

• decimal(int, int) is called numeric(int, int). Note that decimal(int, int) can still be used and is automatically cast to numeric(int, int).

• char(int) is called character(int).

• nvarchar(int) is called varchar(int).

• uniqueidentifier is called uuid (which stands for “universal unique identifier”).

• varbinary(int) does not exist. ByteA is used instead as it serves the same purpose.

• datetime does not exist. Instead either timestamp with timezone or timestamp without timezone should be used. In its migration Django has consistently chosen to use timestamp with timezone so this trend was continued.

Differences in Function Syntax

According to wikipedia, PostgreSQL’s PL/pgSQL, which is the language of choice for functions and procedures, closely resembles the PL/SQL language that Oracle uses. If You have experience with Oracle databases You should feel right at home. But openIMIS uses SQL Server, so I have compiled here all the differences I noticed (and remembered) into a list:

• Function variables do not start with an “@” character.

• All variables are declared at the top of the functions in a DECLARE block. Additionally, the BEGIN statement comes after this DECLARE block. For an example of how such a block might look, take a look at the functions or stored procedures script you can find at Database Structure Migration Process

• Function parameters with default values must come after all function parameters without default values. This has lead to some functions having the order of their parameters changed. This is important as it means You’ll have to change application code to match the new order of function parameters, if or when You switch databases.

• When working with variables that have the same name as a column of a table that is being used in the same statement PL/pgSQL expects absolute clarity as to which of the two items is specified. Adding “#variable_conflict (use_variable|use_column|error)” to the start of a function can mitigate ambiguities. The setting defaults to error but changing the setting still means developers should look at the code carefully.

• Tables can not be declared. Instead in PL/pgSQL a temporary table needs to be created. Preferably, this temporary table should be dropped before ending the function as it persists until the end of the current session. Trying to create a (temp) table that already exists causes errors and accessing a table with old data from the previous execution of a function may lead to wrong results.

• IF and IF/ELSE need a THEN following the condition and they need to end with a semicolon : IF (condition) THEN (statements) END IF; Also Else IF is now ELSIF (condition) THEN

• LOOPs also need to be terminated with a semicolon: END LOOP; This includes WHILE loops that are started with WHILE (conditions) LOOP

• SELECT (@variables) = (columns) FROM … is not valid in PL/pgSQL. Instead, developers should use SELECT (columns) INTO (variables) FROM...

• variables are not assigned using SET. Instead, developers can use C-like syntax: variable = value; These assignments can contain mathematical operations.

• EXEC @variable = function is not used. Instead, C-like syntax can be used: variable = function(parameters);

• As PostgreSQL supports many languages for functions, the chosen language is usually specified: Adding LANGUAGE plpgsql; to the end of the function is enough.

• As with the databases Views, some functionality of SQL Server was mimicked through the creation of new functions. Specifically, SQL Server's DATEADD(day|month|year) function has been reintroduced in the new DATEADD_DAY(), DATEADD_MONTH(), and DATEADD_YEAR() functions. The page Database Structure Migration Process has a script file 02_aux_functions that has all these scripts.