Re: tuple compare involving NULL
| От | David Johnston |
|---|---|
| Тема | Re: tuple compare involving NULL |
| Дата | |
| Msg-id | CAKFQuwb6W7BU-rdet9iPc+GE=s0SGPE+2wa0YR9rMMBtqY6cTw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: tuple compare involving NULL (Tob <me@ibotty.net>) |
| Список | pgsql-novice |
hi,sorry to hear that.This isn't really useful context, to me at least.i don't understand what you mean with artificial. id _is_ the primary key.It tells me nothing of why you need an artificial PK
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.
they do. i don't control the database layout and i have to support pagination involving NULLs in order by columns.
> or why you think a and b need to allow null.
Fair enough - though this limits your options.
it is to allow ascending order on the first and descending order on the second. 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 where clause is odd with its mix of q and t in the same row value
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.
В списке pgsql-novice по дате отправления: