pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Sorting by NULL values
6+ messages / 6 participants
[nested] [flat]
* Sorting by NULL values
@ 2003-03-04 19:45 Ian Burrell <ib@onsitetech.com>
2003-03-04 19:01 ` Re: Sorting by NULL values Josh Berkus <josh@agliodbs.com>
2003-03-04 20:01 ` Re: Sorting by NULL values Tomasz Myrta <jasiek@klaster.net>
2003-03-04 20:07 ` Re: Sorting by NULL values Dan Langille <dan@langille.org>
2003-03-04 20:13 ` Re: Sorting by NULL values Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 4 replies; 6+ messages in thread
From: Ian Burrell @ 2003-03-04 19:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sorting by NULL values
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
@ 2003-03-04 19:01 ` Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-03-04 19:01 UTC (permalink / raw)
To: Ian Burrell <ib@onsitetech.com>; pgsql-sql
Ian,
> 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.
Not all other databases. It really depends.
> Is there a way to replace the NULLs with
> empty strings?
Sure: UPDATE table SET column = '' WHERE column IS NULL
--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sorting by NULL values
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
@ 2003-03-04 20:01 ` Tomasz Myrta <jasiek@klaster.net>
3 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-04 20:01 UTC (permalink / raw)
To: Ian Burrell <ib@onsitetech.com>; +Cc: pgsql-sql
Ian Burrell wrote:
> 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?
order by field is null asc/desc, field asc/desc
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sorting by NULL values
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
@ 2003-03-04 20:07 ` Dan Langille <dan@langille.org>
3 siblings, 0 replies; 6+ messages in thread
From: Dan Langille @ 2003-03-04 20:07 UTC (permalink / raw)
To: Ian Burrell <ib@onsitetech.com>; +Cc: pgsql-sql
On Tue, 4 Mar 2003, Ian Burrell wrote:
> 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?
I think what you want may be either COALESCE or ISNULL/IFNULL or perhaps a
CASE statement.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sorting by NULL values
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
@ 2003-03-04 20:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-04 22:01 ` Re: Sorting by NULL values Rod Taylor <rbt@rbt.ca>
3 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-03-04 20:13 UTC (permalink / raw)
To: Ian Burrell <ib@onsitetech.com>; +Cc: pgsql-sql
On Tue, 4 Mar 2003, Ian Burrell wrote:
> 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
IIRC, they're either considered greater than or less than non-NULL values,
but the decision is up to the implementation.
> change Postgres's behavior? Is there a way to replace the NULLs with
> empty strings?
Coalesce should work.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sorting by NULL values
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
2003-03-04 20:13 ` Re: Sorting by NULL values Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-03-04 22:01 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Rod Taylor @ 2003-03-04 22:01 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Ian Burrell <ib@onsitetech.com>; pgsql-sql
On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote:
> On Tue, 4 Mar 2003, Ian Burrell wrote:
>
> > 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
If you care, order by their boolean equivelent first:
order by field is null desc, field
DESC puts nulls first, since true > false
> IIRC, they're either considered greater than or less than non-NULL values,
> but the decision is up to the implementation.
>
> > change Postgres's behavior? Is there a way to replace the NULLs with
> > empty strings?
>
> Coalesce should work.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-04 22:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-04 19:45 Sorting by NULL values Ian Burrell <ib@onsitetech.com>
2003-03-04 19:01 ` Josh Berkus <josh@agliodbs.com>
2003-03-04 20:01 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-04 20:07 ` Dan Langille <dan@langille.org>
2003-03-04 20:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-04 22:01 ` Rod Taylor <rbt@rbt.ca>
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