We are planning to move from MSSQL to PostgrSQL . With respect to Ignition related database other than schema, data migration ,PostgreSQL JDBC driver and connection properties configuration in ignition, what other aspects need to be considered ?
The default identifier quoting (square brackets) in MS SQL doesn't work in any other brand of database. Start by setting your SQL Server to allow "ANSI" quotes, the standard, and convert all of your queries to use them. (Double quote marks are for identifiers, single quote marks for data, per the SQL standard.)
Also convert queries to use quoting where your tables aren't entirely lower case. MS SQL Server is case-insensitive case-preserving. PostgreSQL is entirely case-sensitive and converts unquoted identifiers to lower case.
While doing that, look for SQL functions that are different in PostgreSQL (sorry, I don't have a list) and note where they are so they can be fixed after migration.
And for ignition built-in database (alarm history and other tables) what efforts are involved in migration?
Generally nothing. All of Ignition's automatically generated tables use lower case with no spaces or special characters, so they are compatible across all DB brands without identifier quoting.
1 Like
Thank you . That helps a lot .
Note that timestamps that are actual datetime or datetime2 column types will likely need special care, as the corresponding PostgreSQL types (timestamp and timestamptz) have some semantic differences. Use timestamptz for most cases to maximize robustness with time zones.
(I generally change Ignition's PostgreSQL translator to use timestamptz by default.)