Postgresql select, show fixed count rows

Question

Simple question. I have a table "tablename" with 3 rows. I need show 5 rows in my select when count rows < 5.

select * from tablename


+------------------+
|colname1 |colname2|
+---------+--------+
|1        |AAA     |
|2        |BBB     |
|3        |CCC     |
+---------+--------+

In this query I show all rows in the table. But I need show 5 rows. 2 rows is empty. For example (I need):

+------------------+
|colname1 |colname2|
+---------+--------+
|1        |AAA     |
|2        |BBB     |
|3        |CCC     |
|         |        |
|         |        |
+---------+--------+

Last 2 rows is empty. It is possible?

I think this would be much easier to handle in your application layer. What language are you using, or is this a pure DBA problem? — Tim Biegeleisen, Nov 11 '15 at 08:01

score 2 · Accepted Answer · answered Nov 11 '15 at 08:03

Something like this:

with num_rows (rn) as (
   select i
   from generate_series(1,5) i -- adjust here the desired number of rows
), numbered_table as (
   select colname1, 
          colname2,
          row_number() over (order by colname1) as rn
   from tablename
)
select t.colname1, t.colname2
from num_rows r
  left outer join numbered_table t on r.rn = t.rn;

This assigns a number for each row in tablename and joins that to a fixed number of rows. If you know that your values in colname1 are always sequential and without gaps (which is highly unlikely) then you can remove the generation of row numbers in the second CTE using row_number().

If you don't care which rows are returned, you can leave out the order by part - but then the rows that are matched will be random. Leaving out the order by will be a bit more efficient.

The above will always return exactly 5 rows, regardless of how many rows tablename contains. If you want at least 5 rows, then you need to flip the outer join:

....
select t.colname1, t.colname2
from numbered_table t
  left outer join num_rows r on r.rn = t.rn;

SQLFiddle example: http://sqlfiddle.com/#!15/e5770/3

Postgresql select, show fixed count rows

1 Answers1