What's the most efficient way to divide a table by a threshold into two sub-queries in PostgreSQL

Question

I have a table test with schema id(int) and height(numeric) in PostgreSQL.

I need to divide them by a certain height then calculate each sub-query.

with above as (
  select id, height
  from test
  where height >= 1
)
below as (
  select id, height
  from test
  where height < 1
)
-- do something with each sub-query

To my understanding the two sub-query above will iterate the table twice.

In a programming language such as python, i can put data in a list then just iterate through the list 1 time by removing and storing > 1 items found in the set to another list, then the remaining list will be the result of "below"

Is there an equivalent way to do this in SQL (To be specific, PostgreSQL)?

Answer 1

In sql you can do it by flagging the two groups with one iteration:

  select id, height , case when height >= 1 then 'above' else 'below' end as heightFlag
  from test 

If you want you can create a new table in sql, p iterate through the original 1 time by removing and storing > 1 items found in the set the new table