From: | David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tob <me(at)ibotty(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: tuple compare involving NULL |
Date: | 2014-08-13 15:53:49 |
Message-ID: | CAKFQuwb6W7BU-rdet9iPc+GE=s0SGPE+2wa0YR9rMMBtqY6cTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Aug 13, 2014 at 8:24 AM, Tob <me(at)ibotty(dot)net> wrote:
> hi,
>
>
> This isn't really useful context, to me at least.
>>
>
> sorry to hear that.
>
>
> It tells me nothing of why you need an artificial PK
>>
>
> i don't understand what you mean with artificial. id _is_ the primary key.
>
>
It is defined as a serial so it is "made up" - i.e., not a fundamental
part of the data. Ideally you'd have another unique index on this table as
well.
>
> > or why you think a and b need to allow null.
>
> they do. i don't control the database layout and i have to support
> pagination involving NULLs in order by columns.
>
>
Fair enough - though this limits your options.
>
>
> The where clause is odd with its mix of q and t in the same row value
>>
>
> it is to allow ascending order on the first and descending order on the
> second.
>
>
>
> and q.id is technically broken though I know this is just an
>> example.
>>
>
> why is it? whenever (q.a, q.b) = (t.a, t.b) you need another (unique) key
> to seek to the next row. maybe i am missing things.
>
>
The query you provided did not have a column "q.id" output from the
subquery is all. You did " ( SELECT a, b FROM ... WHERE id ) q "
> As muchas arbitrary default values suck they are at least better
>>
>> than null in this regard. You can use coalesce for any data type.
>>
>
> is there a special value (of every type) that is minimal for ever
> comparison (except with itself)? if so i could easily compare
> (COALESCE(t.a, MINIMAL VALUE), COALESCE(q.b, MINIMAL VALUE), t.id)
>
>
>
Not as such but in most use cases there is a value you can choose that
would have the same effect. If you have some control over the schema you
can add a check constraint to ensure that our chosen special value is
always less than the allowed values for the relevant column.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Lee | 2014-08-13 18:10:10 | To be like Them, Think like Them |
Previous Message | Tobias Florek | 2014-08-13 15:25:43 | Re: tuple compare involving NULL |