Обсуждение: Docker image of 11~beta2-2 orders strings case-insensitively

Поиск
Список
Период
Сортировка

Docker image of 11~beta2-2 orders strings case-insensitively

От
Yahor Yuzefovich
Дата:
Hello,

I used these commands to run Beta PG 11:
docker pull postgres:11
docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11
docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres

I've created a table as follows:
CREATE TABLE t (id serial PRIMARY KEY, name VARCHAR (255) NOT NULL);
INSERT INTO t (name) VALUES ('M'), ('i'), ('A');

and ran this query:
SELECT name FROM t ORDER BY name;

I expected to see:
A
M
i

but found:
A
i
M

Best,
Yahor Yuzefovich

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
Tom Lane
Дата:
Yahor Yuzefovich <yahor@cockroachlabs.com> writes:
> I expected to see:
> A
> M
> i

> but found:
> A
> i
> M

That would be a matter of what collation you're running it under ...
if sort(1) sorts the same way, it's not a PG issue.

            regards, tom lane

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
"David G. Johnston"
Дата:
On Monday, August 6, 2018, Yahor Yuzefovich <yahor@cockroachlabs.com> wrote:
Hello,

I used these commands to run Beta PG 11:
docker pull postgres:11
docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11
docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres

I've created a table as follows:
CREATE TABLE t (id serial PRIMARY KEY, name VARCHAR (255) NOT NULL);
INSERT INTO t (name) VALUES ('M'), ('i'), ('A');

and ran this query:
SELECT name FROM t ORDER BY name;

I expected to see:
A
M
i

but found:
A
i
M

This is an environmental aspect (locale) of the docker image; not a bug.  I suggest posting on -general, with details about your locale settings, if you would like some guidance in that area.

David J.

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
Yahor Yuzefovich
Дата:
Here is the output of running 

docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres -l


                                 List of databases

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   

-----------+----------+----------+------------+------------+-----------------------

 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 

 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +

           |          |          |            |            | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +

           |          |          |            |            | postgres=CTc/postgres I might be missing something, but it looks to me that I have regular collation. Sorry, I don't have any experience with this.


Best,
Yahor Yuzefovich

On Mon, Aug 6, 2018 at 12:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, August 6, 2018, Yahor Yuzefovich <yahor@cockroachlabs.com> wrote:
Hello,

I used these commands to run Beta PG 11:
docker pull postgres:11
docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11
docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres

I've created a table as follows:
CREATE TABLE t (id serial PRIMARY KEY, name VARCHAR (255) NOT NULL);
INSERT INTO t (name) VALUES ('M'), ('i'), ('A');

and ran this query:
SELECT name FROM t ORDER BY name;

I expected to see:
A
M
i

but found:
A
i
M

This is an environmental aspect (locale) of the docker image; not a bug.  I suggest posting on -general, with details about your locale settings, if you would like some guidance in that area.

David J.

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
"David G. Johnston"
Дата:
On Mon, Aug 6, 2018 at 12:30 PM, Yahor Yuzefovich <yahor@cockroachlabs.com> wrote:
I might be missing something, but it looks to me that I have regular collation. Sorry, I don't have any experience with this.
The locale en_US.utf8 has the behavior you are observing.  You seem to be familiar with the generic "C" locale which has the sorting behavior you are expecting.

https://www.postgresql.org/docs/10/static/charset.html
David J.

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
Alvaro Herrera
Дата:
On 2018-Aug-06, Yahor Yuzefovich wrote:

> and ran this query:
> SELECT name FROM t ORDER BY name;
> 
> I expected to see:
> A
> M
> i

alvherre=# SELECT name FROM t ORDER BY name COLLATE "C";
 name 
──────
 A
 M
 i
(3 filas)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Docker image of 11~beta2-2 orders strings case-insensitively

От
Yahor Yuzefovich
Дата:
Thanks for the explanation. Sorry for the confusion.

Best,
Yahor Yuzefovich

On Mon, Aug 6, 2018 at 4:37 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
On 2018-Aug-06, Yahor Yuzefovich wrote:

> and ran this query:
> SELECT name FROM t ORDER BY name;
>
> I expected to see:
> A
> M
> i

alvherre=# SELECT name FROM t ORDER BY name COLLATE "C";
 name
──────
 A
 M
 i
(3 filas)

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services