NULL Values in ORDER BY - Informix to PostgreSQL Migration

In Informix, when you sort columns in ORDER BY clause NULL values go first for ascending order, and last for descending order.

In PostgreSQL, the default behavior is different, NULL values go last for ascending order, and first for descending order, but PostgreSQL allows you to specify NULLS FIRST and NULLS LAST clauses in ORDER BY to specify sorting for NULL values

Informix PostgreSQL
Ascending order NULLs first NULLs last
Descending order NULLs last NULLs first

In Informix, NULL values always go first in ascending order:

Informix:

  -- Sample table and data
  CREATE TABLE cities (name VARCHAR(70));
 
  INSERT INTO cities VALUES ('Austin');
  INSERT INTO cities VALUES (NULL);
  INSERT INTO cities VALUES ('Seattle');  
 
  -- NULLs are always first in ASC order
  SELECT name FROM cities ORDER BY name;

Result:

name
NULL
Austin
Seattle

But you run the same query on the same data in PostgreSQL, you will get the different result:

PostgeSQL:

  -- NULLs are last in ASC order by default
  SELECT name FROM cities ORDER BY name;

Result:

name
Austin
Seattle
NULL

But PostgreSQL allows you to specify NULLS FIRST in the ORDER BY, in this case NULL values will go first:

PostgreSQL:

  -- NULLs are first now
  SELECT name FROM cities ORDER BY name NULLS FIRST;

Result (the same as Informix):

name
NULL
Austin
Seattle

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - August 2013.

You could leave a comment if you were logged in.