Php Postgresql, pagination and union

Question

I want to use pagination in php but the problem is there is union in query like:

select * from table1 UNION select * from table 2

How can i achieve pagination in this case

Vao Tsun · Accepted Answer · 2017-11-27 08:37:06Z

1

if you speak of LIMIT .. OFFSET then usage is same. eg:

t=# select oid,right(datname,3) from pg_database
union all
select oid,right(datname,3) from pg_database
order by oid;
    oid    | right
-----------+-------
         1 | te1
         1 | te1
     13289 | te0
     13289 | te0
     13294 | res
     13294 | res
     17635 | ats
     17635 | ats
   1175099 | t
   1175099 | t
  35836773 | pme
  35836773 | pme
 129444516 | s
 129444516 | s
 185505699 | foo
 185505699 | foo
 281585738 | ump
 281585738 | ump
(18 rows)

Time: 0.404 ms
t=# select oid,right(datname,3) from pg_database
union all
select oid,right(datname,3) from pg_database
order by oid
limit 5 offset 3;
  oid  | right
-------+-------
 13289 | te0
 13294 | res
 13294 | res
 17635 | ats
 17635 | ats
(5 rows)

Time: 0.437 ms

Mind that I use UNION ALL, not UNION, so postgres would not hide duplicated rows

answered Nov 27, 2017 at 8:37

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kuldeep Thakur Over a year ago

ok, if i have large no of records coming from union then does it has performance issue ?

Vao Tsun Over a year ago

might be. number of rows is one of variables

Collectives™ on Stack Overflow

Php Postgresql, pagination and union

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related