Postgresql – ERROR: collation “database_default” for encoding “UTF8” does not exist

collationpostgresql

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?

Best Answer

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.

Related Solutions

PostgreSQL – Handling Encoding Issues with pg_dump/restore Between Windows and Linux

I suppose the dump file you have is encoded in WIN1252 and the target database on Linux has LATIN1 as the server encoding. That's not going to work, as the error message says.

I suggest you reinitialize the target database using UTF8.

Alternatively, create the dump in UTF8 using the pg_dump -E option.

SQL Server – Why is There a Collation Conflict Between System Views

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro:

create database foo collate Latin1_General_CI_AI

use foo

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;

Best Answer

Related Solutions

PostgreSQL – Handling Encoding Issues with pg_dump/restore Between Windows and Linux

SQL Server – Why is There a Collation Conflict Between System Views

Related Question