...
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). These two types should be identical.
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. Currently we’re mostly using date. In some instances we use timestamp. Note that date does not store the time of day but in some cases timestamp may be causing issues with Django’s automated migration. This is currently being investigated.
There exists no equivalent of tinyint. We’re forced to use smallint instead. Hopefully this will not affect db size too much.
varbinary(int) does not exist. Currently we use ByteA. It is a variable-length binary string, unfortunately we can not assign a default length.
uniqueidentifier is called uuid (this is short for “universal unique identifier”)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.