Forum Discussion

luistorres's avatar
luistorres
Brass Contributor
Mar 11, 2024

Is Encoding SQL_ASCII and Collation en_US.1252 supported in Azure Flexible Server PostgreSQL?

Is encoding SQL_ASCII with  Collation en_US.1252 supported in Azure FlexibleServer PostgreSQL?

 

CREATE DATABASE databaseforme
WITH
ENCODING = 'SQL_ASCII'
LC_COLLATE = 'en_US.1252'
LC_CTYPE = 'en_US.1252'
IS_TEMPLATE = False;
I am trying to create the database but I am getting the error:
ERROR: invalid locale name: "en_US.1252" SQL state: 42809

5 Replies

Sort By
  • BabatundeDallas's avatar
    BabatundeDallas
    Brass Contributor
    Mar 13, 2024

    luistorres  SQL_ASCII encoding and the en_US.1252 collation is not supported in Azure Flexible Server PostgreSQL. 

    The Azure Flexible Server for PostgreSQL does not recognize the en_US.1252 collation. Instead, you should use the UTF-8 collation, which provides better sorting and comparison rules for various languages.

     

    I recommend using the following configuration to create your database 

     

    CREATE DATABASE databaseName
WITH
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
IS_TEMPLATE = False;

     

    Note: Using UTF-8 encoding and en_US.UTF-8 collation, you’ll ensure compatibility with Azure Flexible Server for PostgreSQL. If you encounter any further issues, feel free to @ me in replies for further assistance.

     

    Read more on https://learn.microsoft.com/en-us/answers/questions/484134/invalid-byte-sequence-for-encoding-utf8-azure-data?page=1&WT.mc_id=%3Fwt.mc_id%3Dstudentamb_357518

    --

    If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.

     

    Thanks

    • luistorres's avatar
      luistorres
      Brass Contributor
      Mar 13, 2024
      I am migrating a database from Azure PostgreSQL Single server to posgreSQL Flexible Server and the application is using en_US.1252
      this is the definition of the database in single Server
      CREATE DATABASE "database_name_here"
      WITH
      ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' TABLESPACE = pg_default CONNECTION LIMIT = -1 IS_TEMPLATE = False;

      I was able to create the database using this SQL sentence, but not sure yet if I will get application errors
      CREATE DATABASE "database_name_here"
      WITH
      TEMPLATE = template0 ENCODING = 'SQL_ASCII' C_COLLATE = 'C' LC_CTYPE = 'C' LOCALE_PROVIDER = 'libc' CONNECTION LIMIT = -1 IS_TEMPLATE = False;

      • BabatundeDallas's avatar
        BabatundeDallas
        Brass Contributor
        Mar 13, 2024

        luistorres If you encounter issues, consider adjusting the collation settings. 

        Some applications rely on specific collation rules, so adjust the collation settings based on your application's requirements to choose the appropriate locale.

         

        However, I recommend using the using UTF8

        CREATE DATABASE "database_name_here"
WITH
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
IS_TEMPLATE = False;

         

        You can review the https://learn.microsoft.com/en-us/azure/postgresql/migrate/how-to-migrate-single-to-flexible-portal?WT.mc_id=%3Fwt.mc_id%3Dstudentamb_357518 latest update, release on 03/07/2024 .

         

        Thanks

         

Resources