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.

Answer 1

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