Nulls first/last function in PostgreSQL

Nulls first/last

The nulls first/last function is mainly used in order by sorting clauses, which affects the position of null values in the sorting result. Simply put, nulls first indicates that the null value is always in front of all values during sorting, that is, when processing order by a DESC, the PostgreSQL executor considers that the null value is greater than all values, when order by a or order by a ASC, the executor considers that the null value is smaller than all values, and puts the null value first. Nulls last indicates that the null value is always placed behind all values during sorting, that is, processing order
When a DESC is used, the PostgreSQL actuator considers that the null value is less than all values, while when order by a or order by a ASC, the actuator considers that the null value is greater than all values, leading the null value. When the nulls first/last function is not specified, the executor considers that the null value must be greater than all values by default, and the sorting result of the order by clause is processed.

Simple nulls first/last function display

The following tests are performed in the Postgres database. The database version is 9.2.2 and the test system is Linux.

Simple functions of a common table:

Create Table test1 (A int, B INT );

Insertinto test1 values (1, 2 );

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

 

The partition table function is simple. Note the following when creating a partition table in a PostgreSQL database:

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) Do insteadinsert into test_hash1 values (new. A, new. 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 (1, 2 );

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

 

The above are the nulls first/last functions of the order by clause in the SELECT statement. The same applies to other places that require the order by clause, such as creating an index. Here is just a simple demonstration of the function, there is time to write some articles to analyze the PostgreSQL database.