Null ordering in queries can be changed by settings?

From: Vincenzo Melandri <vmelandri(at)imolinfo(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: Null ordering in queries can be changed by settings?
Date: 2013-02-15 10:14:35
Message-ID: CAHSd9GfD6N80F75SgMuhLB0gd-Dqo77wVvR_QsqRJT4QOt1R0w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I stepped into the same problem as this guy from 2005.
Is this problem changed in the last 8 years? :)
Can it be changed from settings now?

>
> unfortunately, standard 'nulls first/last' isn't supported yet.
> to change behaviour you should use following statement:
> select v_date, v_userid from votes order by v_date is not null desc, v_date desc
> or, something like this:
> select v_date, v_userid from votes order by coalesce(v_date, XXX) desc
> where XXX is substituted by the date value that is smaller than any
> other used in your table
>
> On 20/11/05, me(at)alternize(dot)com <me(at)alternize(dot)com> wrote:
> >
> > hi list
> >
> > coming from the MS-SQL Server world, we're migrating our applications slowly
> > to pgsql 8.1. now we thumbled on a problem with different sort-order
> > behaviours:
> >
> > we got a table with dated records:
> > table: votes (v_userid, v_date)
> >
> > the v_date field can either be null or a date in the past. in mssql, sorting
> > the table to list records in descending date-order
> >
> > select v_date, v_userid from votes order by v_date desc
> >
> > produces a list where first the records with dates are listed, and then the
> > ones with nulls. in pgsql, the null-records are listed first before the
> > date-records... how can this behaviour be changed?
> >
> > mssql:
> > --------
> > 2005-11-05 3
> > 2005-11-01 4
> > 2005-09-10 1
> > null 2
> > null 5
> >
> >
> > pgsql:
> > --------
> >
> > null 2
> > null 52005-11-05 3
> > 2005-11-01 4
> > 2005-09-10 1
> >
> > thanks in advance,
> > thomas
> --
> Best regards,
> Nikolay

--
Vincenzo.
Imola Informatica

Ai sensi del D.Lgs. 196/2003 si precisa che le informazioni contenute
in questo messaggio sono riservate ed a uso esclusivo del
destinatario.
Pursuant to Legislative Decree No. 196/2003, you are hereby informed
that this message contains confidential information intended only for
the use of the addressee.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thomas Kellerer 2013-02-15 11:02:57 Re: Null ordering in queries can be changed by settings?
Previous Message Sergey Konoplev 2013-02-15 02:18:10 Re: Insert output query to a column from a joined table in PostgreSQL 9.1