Nulls first/last function in PostgreSQL

Source: Internet
Author: User
Tags postgres database
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.