Postgresql weird behavior when sorting null column with nulls first/limit

Question

I have the following data set on Postgresql:

create table data (
  id    int,
  stage varchar(255),
  name  varchar(255)
);

 id    stage      name  
 ----------------------------
 1   pyramid     act 1  
 2   pyramid     act 2  
 3   NULL        act 3  
 4   NULL        act 4  
 5   NULL        act 5  
 6   NULL        act 6  
 7   NULL        act 7  
 8   NULL        act 8  
 9   NULL        act 9  
 10  NULL        act 10 
 11  NULL        act 11 
 12  NULL        act 12 
 13  shangri la  act 13

I query all the data table and sort by stage with nulls last/ limit:

select *
from data
order by stage asc nulls first
limit 5;

Result:

 id    stage      name  
 ----------------------------
 6     NULL       act 6 
 3     NULL       act 3 
 4     NULL       act 4 
 5     NULL       act 5 
 7     NULL       act 7 
 ----------------------------

The issue when I change the limit value I get a different sorting result:

select *
from data
order by stage asc nulls first
limit 3;

Result:

 id    stage      name  
 ----------------------------
 4     NULL       act 4 
 3     NULL       act 3 
 5     NULL       act 5 
 ----------------------------

PS: I'm using Postgresql PostgreSQL 10.3.

Kamil Gosciminski · Accepted Answer · 2018-06-25 09:59:04Z

4

This is standard behaviour. You are sorting only on stage column which means that arbitrary rows with the same value in stage column are being picked up (ties are not being resolved all the time in the same manner, in fact Postgres doesn't care about them, which is why it pulls random rows)

As a rule of thumb, you won't get sorted output unless you explicitly sort it yourself.

Consider below example for your data with added id column. This will bring the same rows every time you execute the SELECT statement assuming that this column holds unique values (in your sample data it does). For that purpose you can define a primary key on column id or add a unique constraint.

select *
from data
order by stage asc nulls first, id
limit 3;

Output always the same within your sample data:

 id    stage      name  
 ----------------------------
 3     NULL       act 3
 4     NULL       act 4 
 5     NULL       act 5

edited Jun 25, 2018 at 9:59

answered Jun 25, 2018 at 7:35

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql weird behavior when sorting null column with nulls first/limit

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related