Problem with sorting in PostgreSQL

Question

Problem with sorting in PostgreSQL

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (0 votes)

1

I have a problem when ordering columns in PostgreSQL even though it is properly configured in UTF-8.

The version of PostgreSQL is 9.3 and it is installing on a Maverick 10.9.5 MacOSX (this problem has already occurred in earlier versions of the system).

WhenIorderthe'name'columninphpPgAdmin,Django,ortheterminal,PostgreSQLisnotdealingwithuppercase,lowercase,andaccentedcharacterscorrectly.

Viewtheorderedtableasitgets:

As you can see, lowercase characters are getting longer than uppercase characters, and characters with accents are getting last.

I want you to have accents, be it uppercase or lowercase, that ordering is done correctly. Does anyone know how to solve this?

postgresql osx

asked by anonymous 29.09.2014 / 21:23

3 answers

How to solve image upload problems in Wordpress? Open disk reader with Java, and without specifying the drive

score 3 · Answer 1

PostgreSQL uses the colons provided by the system. Check what's available:

$ locale -a

In Fedora Linux the "pt_BR" collation works correctly. Try other collations in your query. Note that only the collations for UTF-8 encoding will work on the UTF-8 base:

select * 
from t 
order by nome collate "pt_PT" -- ou "en_US"

If no collation works you can still, for testing purposes, create a base in the LATIN1 (ISO-88591) encoding and try the collation again.

Its biggest problem is that OSX should not be used on a production server. If there is an opportunity try or suggest to the authorities on call a Linux distribution.

score 2 · Answer 2

The ideal is to specify encoding and collation at the time of creating the database so that future tables always look the way they want:

CREATE DATABASE name ENCODING 'UTF8 LC_COLLATE 'pt_BR'

One possible alternative is to specify collation in the column itself:

CREATE TABLE alfabetica (
    id SERIAL PRIMARY KEY,
    nome TEXT COLLATE "pt_BR"
);

See an example working on SQL Fiddle .

score 0 · Answer 3

I'm not experienced in PostgreSQL to say that there's nothing wrong with your bank, but I'm experienced enough with Mac to say there's a problem with OS X.

By default, the locale options on the Mac are just symbolic links to other options.

In my version (Sierra), for example, LC_COLLATE of "pt_BR.UTF-8" points to LC_COLLATE of "la_LN.US-ASCII" .

This is a mega problem (not to say nonsense) in many ways, but in practice what that means is that any library that depends on the collate system will work the wrong way.

I agree with @Clodoaldo that it is best to take OS X out of production.