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

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:

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:

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):

• Informix to PostgreSQL Migration Tools and Reference
• Online SQL Conversion Tool

• String Sorting Order - Informix to PostgreSQL Migration

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.