Postgres order by in wrong order

Question

Postgres order by get wrong result:

postgres=# SELECT (url) FROM posts_post ORDER BY url;
                                               url
--------------------------------------------------------------------------------------------------
 -----------------------------------------------------------------------------------------
 http://nautil.us/issue/70/variables/aging-is-a-communication-breakdown
 https://github.com/felixse/FluentTerminal
 http://www.bbc.com/future/story/20160408-the-ancient-peruvian-mystery-solved-from-space
 http://www.graffathon.fi/2016/presentations/additive_slides.pdf
(4 rows)

As you can see, there is a problem with "http://nautil.us/issue/70/variables/aging-is-a-communication-breakdown". It doesn't sort correctly.

I saved the parsed result in Postgres using Python and psycopg2, and come to point, where I can't test sorting, cause Postgres return order by with errors.

UPD: REPRODUCE:

CREATE TABLE test_post ("id" serial NOT NULL PRIMARY KEY, "title" text NOT NULL, "url" text NOT NULL, "created" timestamp with time zone NOT NULL);

INSERT INTO test_post (title, url, created) VALUES ('Aging Is', 'http://nautil.us/issue/70/variables/aging-is-a-communication-breakdown', NOW()) ON CONFLICT DO NOTHING;

INSERT INTO test_post (title, url, created) VALUES ('Untrusted – a user', 'https://github.com/felixse/FluentTerminal', NOW()) ON CONFLICT DO NOTHING;

INSERT INTO test_post (title, url, created) VALUES ('Artyping (1939)', 'http://www.bbc.com/future/story/20160408-the-ancient-peruvian-mystery-solved-from-space', NOW()) ON CONFLICT DO NOTHING;

INSERT INTO test_post (title, url, created) VALUES (' Applying the Universal', 'http://www.graffathon.fi/2016/presentations/additive_slides.pdf', NOW()) ON CONFLICT DO NOTHING;

SELECT (url) FROM test_post ORDER BY url;

PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u 1) 6.3.0 20170516, 64-bit

":" is less than "s" so it sorts correctly (http: should be before all https:). — thebjorn
– thebjorn, Commented Mar 24, 2019 at 18:49
@thebjorn Initially I thought that as well, but there are two lines beginning with "http" at the bottom. — 404
– 404, Commented Mar 24, 2019 at 18:58
Edit your question, and paste create table, insert, and select statements that reproduce your problem. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 24, 2019 at 19:17
Tag the Postgres version. Show the results with your reproducing limited set (so 4 instead of 33 rows). Anyway, works for me, Postgres 11.1. — pirho
– pirho, Commented Mar 24, 2019 at 20:39
There is no problem with the ordering: Some collations ignores characters that are not letters or digits. — Usagi Miyamoto
– Usagi Miyamoto, Commented Mar 24, 2019 at 21:06

Mike Sherrill 'Cat Recall' · Accepted Answer · 2019-03-24 21:26:45Z

Assuming that you're using UTF8 encoding, specifying the collation instead of accepting the default should fix your immediate problem. Whether this is the right thing to do is application-dependent.

There are several different ways to specify the collation. You can specify it when the database cluster is initialized, when the database is created, when you run a query, etc. See Collation support in the docs for much more detail.

CREATE TABLE test_post (
    "id" serial NOT NULL PRIMARY KEY, 
    "title" text NOT NULL, 
    "url" text collate ucs_basic NOT NULL, 
    "created" timestamp with time zone NOT NULL
);

INSERT INTO test_post (title, url, created) VALUES 
('Aging Is', 'http://nautil.us/issue/70/variables/aging-is-a-communication-breakdown', NOW()) ON CONFLICT DO NOTHING;
INSERT INTO test_post (title, url, created) VALUES 
('Untrusted – a user', 'https://github.com/felixse/FluentTerminal', NOW()) ON CONFLICT DO NOTHING;
INSERT INTO test_post (title, url, created) VALUES 
('Artyping (1939)', 'http://www.bbc.com/future/story/20160408-the-ancient-peruvian-mystery-solved-from-space', NOW()) ON CONFLICT DO NOTHING;
INSERT INTO test_post (title, url, created) VALUES 
(' Applying the Universal', 'http://www.graffathon.fi/2016/presentations/additive_slides.pdf', NOW()) ON CONFLICT DO NOTHING;

SELECT (url) FROM test_post ORDER BY url;

http://nautil.us/issue/70/variables/aging-is-a-communication-breakdown
http://www.bbc.com/future/story/20160408-the-ancient-peruvian-mystery-solved-from-space
http://www.graffathon.fi/2016/presentations/additive_slides.pdf
https://github.com/felixse/FluentTerminal

Collectives™ on Stack Overflow

Postgres order by in wrong order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related