digoal
2017-11-27
PostgreSQL , Greenplum
Greenplum并不支持nulls first或last语法，例如:
select * from tbl order by id nulls first;
select id, last_value(key) over (partition by gid order by crt_time nulls first) from tbl ;
这两句在PostgreSQL可以支持，但是在Greenplum中不支持。
在PG中支持多列排序，同时支持boolean类型，true 大于 false。
利用这个特性，我们可以在排序时调整字段nulls排在前面还是后面。
select * from tbl order by (id is not null), id;
相当于
select * from tbl order by id nulls first;
select * from tbl order by (id is null), id;
相当于
select * from tbl order by id nulls last;
这样就能实现nulls first或last了。
postgres=# select (id is null),* from tbl order by (id is null), id;
?column? | id
----------+----
f | 1
f | 2
f | 3
t |
(4 rows)
postgres=# select (id is not null),* from tbl order by (id is not null), id;
?column? | id
----------+----
f |
t | 1
t | 2
t | 3
(4 rows)
窗口函数中使用也一样。
select id, last_value(key) over (partition by gid order by (crt_time is not null), crt_time) from tbl ;
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
