Postgresql Order By 가 안되는 문제가 발생 했다!

개발 서버 PostgreSQL 에서 Order By 결과가 꼬여나오는 문제 발생!!
왜 일까~?

ChatGPT 도와주ㅓ!

Check Database Collation

Ensure your database and tables are using the correct collation that supports Korean language sorting. Run the following query to check the collation:

SELECT datname, datcollate, datctype 
FROM pg_database 
WHERE datname = 'your_database_name';

If the collation is not appropriate for Korean, consider changing it or using a different collation in your queries.

첫 발 부터 아니되옴

Specify Collation in Query

You can specify a collation directly in your SQL query to enforce correct sorting. For example:

SELECT * FROM conai_product_standardisation_mst 
WHERE user_id = 'swiffy' 
ORDER BY product_name COLLATE "C.UTF-8", unit_product_name COLLATE "C.UTF-8" ASC;

Replace "C.UTF-8" with the appropriate collation, such as "ko_KR.UTF-8" for proper Korean sorting.

SELECT * FROM conai_product_standardisation_mst 
WHERE user_id = 'swiffy' 
ORDER BY product_name COLLATE "ko_KR.UTF-8" ASC;

문제 발견!! 해결을 위해서 postgres 로 psql 접속하려 했는데,,,

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Option 1: Use sudo to Switch to the postgres User

Since peer authentication allows the postgres system user to access the postgres database user without a password, you can switch to the postgres system user using sudo and then run psql:

sudo -i -u postgres
psql

This should log you into the PostgreSQL prompt as the postgres superuser. From here, you can perform any administrative tasks.

Once connected, check the available collations:

SELECT * FROM pg_collation;

If you're still unable to access the pg_collation table, here are a few steps to troubleshoot:

확인 결과 ko_KR collation 없음

Install Korean Locale on the System

If you require specific sorting behavior for Korean, you need to ensure that your operating system has the Korean locale installed. Follow these steps:

Install the Korean Locale on Ubuntu:

sudo locale-gen ko_KR.UTF-8
sudo update-locale

sudo update-locale 에서 전세계 언어를 전부 설치해버림

왜냐하면 전세계에 서비스 할 거니까!!!?? 허허

Restart PostgreSQL:

sudo systemctl restart postgresql

Check Available Collations Again:

SELECT * FROM pg_collation WHERE collname LIKE '%ko%';

If "ko_KR.UTF-8" appears after this, you can use it directly:

SELECT * FROM conai_product_standardisation_mst
WHERE user_id = 'swiffy'
ORDER BY product_name COLLATE "ko_KR.UTF-8";

쿼리 수준에서 collation 적용 안 됨, 허허

Update Collate of databas

PostgreSQL 한글정렬이 제대로 안되는 문제

Collation이란?

Collation은 데이터베이스에서 문자열을 비교하고 정렬할 때 사용하는 규칙을 정의하는 설정입니다. PostgreSQL에서는 데이터베이스를 생성할 때 collation 설정을 지정할 수 있습니다. 기본적으로 en_US.UTF-8과 같은 영어 기반의 collation이 설정되어 있는 경우, 한글 정렬이 제대로 되지 않는 문제가 발생할 수 있습니다.

문제 원인 분석

PostgreSQL에서 데이터베이스의 collation이 한글 정렬에 적합하지 않은 값으로 설정된 경우, 한글이 올바르게 정렬되지 않습니다. 예를 들어, en_US.UTF-8로 설정된 데이터베이스는 한글을 영어 알파벳과 동일한 기준으로 정렬하기 때문에, 우리가 기대하는 "가나다" 순으로 정렬되지 않습니다.

이미 생성된 데이터베이스에서 collation 변경하기

이미 데이터베이스가 생성된 후에는 collation을 변경하기가 어렵습니다. 이 경우, 데이터베이스를 백업하고 새로운 collation 설정으로 데이터베이스를 다시 생성해야 합니다.

UPDATE PG_DATABASE SET DATCOLLATE = 'ko_KR.utf8',
WHERE DATNAME='[데이터베이스명]';