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

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

      luistorres It's great to hear that your internal testing with the ASCII fat client went smoothly after creating the database using UTF8 encoding and subsequently adding the 1252 collation. 

       

      If you encounter any further challenges or need additional assistance, please do not hesitate to contact me.

Resources