Skip to content
Advertisement

Sort by NULL field only if field is NULL, then sort by name in Postgresql

I have the following sort in Postgresql:

ORDER BY verified_at NULLS FIRST, name ASC

What I want is having all records with verified_at NULLS first, but only if the field verified_at is not null, then order by name. I don’t want the verified_at to have the priority on name if verified_at IS NOT NULL.

With this code, verified_at has the priority on name, after the NULLs are displayed. In fact, is-it possible to have records order by name except for the ones that have verified_at NULL ?

Example of what I want:

verified_at:     name:
NULL             Alex
NULL             Bernard
NULL             Toto
2018             Albert
2012             Boris
2015             Michel
2001             Lena

Advertisement

Answer

You could add a first sorting criteria that checks if the data is null, then order by name:

order by (verified_at is not null)::int, name

Actually using the boolean directly should also work:

order by verified_at is not null, name
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement