CODEWITHSUNDEEP
postgresqldocker
shuba.ivan
shuba.ivan

Reputation: 4061

Postgres faced with: error collation does not exist. when try to change collation

I use docker image for postgres

postgres:
    image: postgres:9.6
    ports:
        - '5432:5432'
    container_name: 'postgresql'
    working_dir: /app
    restart: always
    environment:
        POSTGRES_DB: ${POSTGRES_DB}
        POSTGRES_USER: ${POSTGRES_USER}
        POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
        LC_COLLATE: 'sv_SE.UTF-8'
        LC_CTYPE: 'sv_SE.UTF-8'
    volumes:
        - ./data/postgresql:/var/lib/postgresql/data
        - ./postgres/locale.conf:/etc/locale.conf
    networks:
        - php

I want to set swedish collate to my column category_name in category table, For that I just try to execute this query

ALTER TABLE category ALTER COLUMN category_name SET DATA TYPE character varying(255) COLLATE "sv_SE.UTF-8"

> ERROR:  collation "sv_SE.UTF-8" for encoding "UTF8" does not exist

after that I executed manually in my container

root@95494dd4339b:/app# locale-gen sv_SE.utf8 && dpkg-reconfigure locales
Generating locales (this might take a while)...
Generation complete.
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_COLLATE = "sv_SE.UTF-8 UTF-8",
    LC_CTYPE = "sv_SE.UTF-8 UTF-8",
    LANG = "en_US.utf8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
Configuring locales
-------------------

Locales are a framework to switch between multiple languages and allow users to
use their language, country, characters, collation order, etc.

Please choose which locales to generate. UTF-8 locales should be chosen by
default, particularly for new installations. Other character sets may be useful
for backwards compatibility with older systems and software.

  1. All locales

Locales to be generated: 1

and choice 1 and after loading all locales I saw

416 sv_SE.UTF-8
Default locale for the system environment: 416

but after that still faced with the same error 

> ERROR:  collation "sv_SE.UTF-8" for encoding "UTF8" does not exist

when I check pg_collation I did not found my sv_SE.UTF-8

myuser=# SELECT * FROM pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype

------------+---------------+-----------+--------------+-------------+----------
--
 default    |            11 |        10 |           -1 |             | 
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf
8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf
8
 ucs_basic  |            11 |        10 |            6 | C           | C
(7 rows)

myuser=#

how to set swedish collate for my column ?

UPDATE

after manually executed in postgres container locale-gen sv_SE.utf8 && dpkg-reconfigure have that result:

root@95494dd4339b:/app# locale -a | grep sv_SE
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_COLLATE to default locale: No such file or directory
sv_SE
sv_SE.iso88591
sv_SE.iso885915
sv_SE.utf8

I try to create new database but faced with the same error... what wrong ??

myuser=# CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'sv_SE.UTF-8' LC_CTYPE = 'sv_SE.UTF-8';
ERROR:  invalid locale name: "sv_SE.UTF-8"
myuser=# 

SELECT * FROM category ORDER BY category_name COLLATE "sv_SE";

but still faced with error > ERROR: collation "sv_SE" for encoding "UTF8" does not exist

maybe need reload service or something like that for apply changes?

Upvotes: 2

Views: 5146

Answers (2)

pifor
pifor

Reputation: 7882

If possbile try to run initdb so that PostgreSQL takes into account available collations (but you need to re-create the database)

or

try to create the collation in the existing PostgreSQL instance with:

 create collation swedish (locale='sv_SE.utf8');

Upvotes: 1

jmaitrehenry
jmaitrehenry

Reputation: 2400

Can you try to create your own docker image like that:

FROM postgres:9.6
RUN localedef -i sv_SE -c -f UTF-8 -A /usr/share/locale/locale.alias sv_SE.UTF-8
ENV LANG sv_SE.utf8

Do you use a volume to store your database outside your image? If so, you can build a new image, and run it by using the same volume as before and your data should be here.

Upvotes: 0

Related Questions