sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) collation "utf8" for encoding "UTF8" does not exist

Question

I'm currently running into the following error with SQLAlchemy and postgresql, when running alembic upgrade head

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) collation "utf8" for encoding "UTF8" does not exist

I'm initialising a Postgres database as the following, in a docker-compose.yml file:

  db:
    image: postgres:14-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    ports:
      - 5432:5432
    env_file:
      - .env
    environment:
      - POSTGRES_INITDB_ARGS='--lc-collate="en_US.UTF-8" --lc-ctype="en_US.UTF-8"'

However, I believe there is a mismatch when attempting to migrate a field in my model, such as:

# Common Name
name = Column(
    String(
        length=180,
        collation="utf8",
        convert_unicode=False,
        unicode_error=None,
    ),
    index=True,
    name="name",
    comment="Common or IAU Name",
)

Could anyone explain to me what has gone wrong here, and some potential fixes for this?

Answer 1

assuming you need collation, you may want to check this post out, it does a better job than I can of explaining collation.

https://dba.stackexchange.com/questions/240930/postgresql-difference-between-collations-c-and-c-utf-8

I think the answer you are looking for though is:

name = Column(
    String(
        length=180,
        collation="C.UTF-8",
        convert_unicode=False,
        unicode_error=None,
    ),
    index=True,
    name="name",
    comment="Common or IAU Name",
)