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 = '...
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

       

      • luistorres's avatar
        luistorres
        Brass Contributor
        Mar 21, 2024

        BabatundeDallas 

        I created the database using the encodings UTF8, then I created the Collation 1252 in the database. Internal customer tested read/write with the ASCII fat client and works without issues

        CREATE DATABASE "test-dev-002"
           WITH
        TEMPLATE = template0
            ENCODING = 'UTF8'
            LC_COLLATE = 'C'
            LC_CTYPE = 'C'
            LOCALE_PROVIDER = 'libc'
            IS_TEMPLATE = False;


        CREATE COLLATION "English_United States.1252" (provider = icu, locale = "English_United States.1252");

        highlighted: library C and Template0 needed for creating the database

Resources