1

Moving application from SQL Server to PostgreSQL Database. I am getting this error from one of the queries used in a Java file:

ERROR: collation "database_default" for encoding "UTF8" does not exist Position: 467. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: collation "database_default" for encoding "UTF8" does not exist
Position: 467

Can you please let me know how to resolve this issue?

2

database_default is a SQL Server-specific collation that explicitly tells SQL Server to use the database's default collation, overriding the default collation precedence behavior. This is used in a COLLATE clause most typically to avoid collation conflict errors that arise when using temp tables in scenarios where tempdb's default collation differs from the current database's default collation and string columns are being joined or concatenated.

The Java code would need to be edited to remove the COLLATE DATABASE_DEFAULT from the query. Though if this app is coded for Microsoft SQL Server, will it work in PostgreSQL even if this one part is fixed? There are other SQL syntax and datatype differences between the RDBMSs.

For more information on migrating from SQL Server to PostgreSQL, please see:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server

However, if you are dealing with a 3rd party app that has embedded T-SQL, then you might be forced to contact the software vendor to ask about the potential for PostgreSQL support.

| improve this answer | |
  • In PostgreSQL you could use COLLATE "default" to get the default collation (the double quotes are required), but it is better to omit that. – Laurenz Albe Oct 2 at 10:15
  • Thanks @LaurenzAlbe your solution is working. – Ashish Kumar Roy Oct 3 at 14:43
  • @AshishKumarRoy You never indicated whether or not this was in-house or 3rd party code. Please update the question with that info. And, have you tried simply removing the COLLATE DATABASE_DEFAULT clause? – Solomon Rutzky Oct 3 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.