From: Ian Burrell <ib@onsitetech.com>
To: pgsql-sql@postgresql.org
Subject: Sorting by NULL values
Date: Tue, 04 Mar 2003 11:45:02 -0800
Message-ID: <3E65023E.10404@onsitetech.com> (raw)
I am doing a query where I need to sort by a column that may be NULL
because it is coming from an OUTER JOIN. I noticed a difference between
PostgreSQL and other databases about where NULLs show up. It seems that
with Postgres, NULLs are sorted after other values. Other databases
sort them before.
Is there any standard on how sorting NULLs work? Is there a way to
change Postgres's behavior? Is there a way to replace the NULLs with
empty strings?
This is my query:
SELECT product_id
FROM product p
LEFT OUTER JOIN attribute_data ad
ON p.product_id= ad.product_id
AND ad.attribute_id = ?
WHERE p.category_id = ?
ORDER BY data;
The tables look like:
CREATE TABLE product (
product_id serial NOT NULL PRIMARY KEY,
category_id integer NOT NULL
);
CREATE TABLE attribute_data (
attribute_id integer NOT NULL,
product_id integer NOT NULL,
data varchar(1024),
PRIMARY KEY (attribute_id, product_id)
);
- Ian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ib@onsitetech.com
Subject: Re: Sorting by NULL values
In-Reply-To: <3E65023E.10404@onsitetech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox