CODEWITHSUNDEEP
phppostgresqlpagination
Kuldeep Thakur
Kuldeep Thakur

Reputation: 215

Php Postgresql, pagination and union

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

Upvotes: 0

Views: 825

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51519

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

Upvotes: 1

Related Questions