Computer Stuff They Didn't Teach You #2 - Code Pages, Character Encoding...

My understanding of sorting in Postgre

While sorting in Postgre, Collation is taken into account.

SELECT name FROM unnest(ARRAY[
    'a', 'B', 'c', 'D'
]) name ORDER BY name COLLATE "en_US";

It returns 

a
B
c
D

So, with collate "en_US", we can observe that first comes a, then B, then c and then D.

When we try this

SELECT name FROM unnest(ARRAY[
    'apple', 'BANANA', 'carrot', 'ORANGE'
]) name ORDER BY name COLLATE "C";

It returns

B
D
a
c

So, with collate "C", We can observe that all words which start with upper case comes first then the lower case ones.

Some points to be noted:

1) If we don't pass the collation while creating the DB, the collation of the operating system is taken into account which can be for example, "en_US.UTF-8".

2) As recommended in reference link 1, it is good to go with Collation "C" as it delivers consistent results in a platform-independent manner.

References

1) https://simply.name/pg-lc-collate.html

2) https://docs.oracle.com/javadb/10.10.1.2/devguide/cdevcollationexamples.html (Oracle-specific but concepts are applicable to Postgre as well)

The Math Needed for Computer Science

But how does bitcoin actually work?

How Does The Internet Work? An Epic Quest...

Articles read (9-20 July)

The Bulk of Software Engineering in 2018 is Just Plumbing

A Better Way to Write SQL queries for Developers

Do I need to pay for Java now?

String split behavior in Python/Scala/Java & AWK/Perl/Ruby

https://chriszetter.com/blog/2017/10/29/splitting-strings/

The crux part of the post is about what happens when we split an empty string with a delimiter.

In Python, "".split(",") returns [""]. The reason behind why this works this way is explained as:

"aa".split(",") returns ["aa"]
"a".split(",") returns ["a"]
so, "".split(",") returns [""]

This behavior is same for Java, Scala and other languages which are mentioned in the post.

But in Ruby, "".split(",") returns []. The reason why this works this way is because:

"aa,aa".split(",") returns ["aa","aa"]
"aa".split(",") returns ["aa"]
"".split(",") returns []

Ruby operates based on field concept which is inherited from AWK.

For more information please refer the link mentioned on the top of the post.