Will Postgres' DISTINCT function always return null as the first element?

Question

I'm selecting distinct values from tables thru Java's JDBC connector and it seems that NULL value (if there's any) is always the first row in the ResultSet.

I need to remove this NULL from the List where I load this ResultSet. The logic looks only at the first element and if it's null then ignores it.

I'm not using any ORDER BY in the query, can I still trust that logic? I can't find any reference in Postgres' documentation about this.

Answer 1

You can add a check for NOT NULL. Simply like

select distinct columnName 
from Tablename    
where columnName IS NOT NULL

Also if you are not providing the ORDER BY clause then then order in which you are going to get the result is not guaranteed, hence you can not rely on it. So it is better and recommended to provide the ORDER BY clause if you want your result output in a particular output(i.e., ascending or descending)

If you are looking for a reference Postgresql document then it says:

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Answer 2

If it is not stated in the manual, I wouldn't trust it. However, just for fun and try to figure out what logic is being used, running the following query does bring the NULL (for no apparent reason) to the top, while all other values are in an apparent random order:

with t(n) as (values (1),(2),(1),(3),(null),(8),(0)) 
select distinct * from t 

However, cross joining the table with a modified version of itself brings two NULLs to the top, but random NULLs dispersed througout the resultset. So it doesn't seem to have a clear-cut logic clumping all NULL values at the top.

with t(n) as (values (1),(2),(1),(3),(null),(8),(0)) 
select distinct * from t 
cross join (select n+3 from t) t2