The Nulls first/last function in PostgreSQL

Nulls First/last Function Introduction
The Nulls first/last feature is primarily used in order by sort clauses, affecting the position of null values in the sort results. In simple terms, Nulls first means that the null value is always sorted before all values, that is, when processing order by a DESC, the PostgreSQL executor considers the null value to be greater than all values, and order by a or order by a ASC when the executor considers the null value to be less than all values, and the null value is preceded. Nulls last indicates that the null value is always sorted after all values, that is, the PostgreSQL executor considers the null value to be less than all values when processing order by a DESC, and order by a or order by a ASC when the executor considers the null value to be greater than all values, the null value is preceded. When you do not specify the Nulls first/last feature, the executor defaults to the value of NULL to be greater than all values, as a result of the ORDER BY clause processing.

Nulls first/last function Simple display
The following tests were tested under the Postgres database, with a database version of 9.2.2, and the test system for Linux.

Ordinary table simple function display:

Create table Test1 (a int, b int);

Insertinto test1 values ();

Insertinto test1 values (3,4);

Insertinto test1 values (5);

Select * from Test1 ORDER BY b desc nulls first;

A b

5

3 4

1 2

Select * from Test1 ORDER by b desc nulls last;

A b

3 4

1 2

5

Select *from test1 ORDER by b desc nulls; Error

Partition table Simple function Show, note how the PostgreSQL database builds a partitioned table:

CreateTable Test_hash (a int, b int);

CreateTable test_hash1 (check (a>=0 and A<5)) inherits (Test_hash);

CreateTable test_hash2 (check (a>=5)) inherits (Test_hash);

Createrule test_hash_1 as on insert to Test_hash where (a>=0 and a<5) does insteadinsert into TEST_HASH1 values (new.a,n EW.B);

Createrule test_hash_2 as on insert to Test_hash where (a>=5) do instead insertinto TEST_HASH2 values (new.a,new.b);

Insertinto Test_hash values ();

Insertinto Test_hash values (3,4);

Insertinto Test_hash values (5);

Select *from test_hash ORDER by b desc nulls first;

A b

5

3 4

2 2

Select *from test_hash ORDER by b desc nulls last;

A b

3 4

1 2

5

All of the above are nulls first/last features of the ORDER BY clause in the SELECT statement, and other places where an ORDER BY clause are required to create an index. Here is a simple demonstration of the function, there is time to write some analysis of the PostgreSQL database articles out

The Nulls first/last function in PostgreSQL