将 null 排序为"无关紧要"的表

Question

我有一个有两列的表:col_order (int)和name (text)。我想检索有序的行，这样，当col_order不为空时，它确定顺序，但当它为空时，name确定顺序。我想到了一个如

这样的从句。

order by coalesce( col_order, name )

但是，这不起作用，因为两列具有不同的类型。我正在考虑将两者都转换为bytea，但是:1)转换整数是否有比循环建模256更好的方法，并在函数中堆叠单个字节，以及2)我如何转换"name"以确保某种相同的排序顺序(假设name有顺序……好吧，引用文本会很好，但我没有费心去重建…(目前为UTF8)。

即使这一切都是可能的(欢迎对细节的建议)，这似乎是很多工作。有没有更好的办法?

编辑

Gordon给了我一个很好的答案，但这表明我没有正确地表达这个问题。我想要一个排序顺序的name，其中col_order表示这个顺序被覆盖的地方。这不是一个很好的问题，但这里有一个可接受的解决方案:

col_order| name
----------------
    null | a
       1 | foo
    null | foo1
       2 | bar

Ie——这里如果col_order是空的，名字应该插入在名字最接近的字母顺序之后，但比它更少。否则，可以通过以下方式获得:

order by col_order nulls last, name

编辑2

好吧…为了让你的创意源源不断，这似乎是一个正确的方向:

with x as ( select *, 
    case when col_order is null then null else row_number() over (order by col_order) end as ord
  from temp )
select col_order, name, coalesce( ord, lag(ord,1) over (order by name) + .5) as ord from x; 

当没有col_order时，它从前一行按名称排序。一般来说，这是不对的……我想我必须回到第一行非空col_order…似乎SQL标准对可能这样做的窗口函数有"忽略null"，但在postgres中没有实现。有什么建议吗?

编辑3

下面的代码看起来很接近——但是不起作用。对于递归查询，窗口求值可能有点奇怪。

with recursive x(col_order, name, n) as ( 
  select col_order, name, case when col_order is null then null 
      else row_number() over (order by col_order) * t end from temp, tot
  union all
  select col_order, name, 
    case when row_number() over (order by name) = 1 then 0 
      else lag(n,1) over (order by name) + 1 end from x
  where x.n is null ),
tot as ( select count(*) as t from temp )
select * from x;

Answer 1

使用多个子句:

order by (case when col_order is not null then 1 else 2 end),
         col_order,
         name

当col_order为not null时，则第一个排序键为1。当为null时，则分配2。因此，not-nulls将放在首位。

Answer 2

Ok…下面的方法似乎有效——我将把这个问题"未回答"，尽管等待批评或更好的建议:

从这里使用last_agg聚合:

with
tot as ( select count(*) as t from temp ),
x as (
    select col_order, name,  
        case when col_order is null then null 
            else (row_number() over (order by col_order)) * t end as n,
        row_number() over (order by name) - 1 as i
    from temp, tot )
select x.col_order, x.name, coalesce(x.n,last_agg(y.n order by y.i)+x.i, 0 ) as n
from x
left join x as y on y.name < x.name
group by x.col_order, x.n, x.name, x.i
order by n;