Reuse a query to use it for operation on LIMIT and OFFSET on postgresql

Question

Using PostgreSQL 9.4, I have a table like this:

CREATE TABLE products
AS
  SELECT id::uuid, title, kind, created_at
  FROM ( VALUES
    ( '61c5292d-41f3-4e86-861a-dfb5d8225c8e', 'foo', 'standard' , '2017/04/01' ),
    ( 'def1d3f9-3e55-4d1b-9b42-610d5a46631a', 'bar', 'standard' , '2017/04/02' ),
    ( 'cc1982ab-c3ee-4196-be01-c53e81b53854', 'qwe', 'standard' , '2017/04/03' ),
    ( '919c03b5-5508-4a01-a97b-da9de0501f46', 'wqe', 'standard' , '2017/04/04' ),
    ( 'b3d081a3-dd7c-457f-987e-5128fb93ce13', 'tyu', 'other'    , '2017/04/05' ),
    ( 'c6e9e647-e1b4-4f04-b48a-a4229a09eb64', 'ert', 'irregular', '2017/04/06' )
  ) AS t(id,title,kind,created_at);

Need to split the data into n same size parts. if this table had a regular id will be easier, but since it has uuid then I can't use modulo operations (as far as I know).

So far I did this:

SELECT * FROM products 
WHERE kind = 'standard'
ORDER BY created_at
LIMIT(
  SELECT count(*) 
  FROM products 
  WHERE kind = 'standard'
)/2
OFFSET(
  (
    SELECT count(*)
    FROM products 
    WHERE kind = 'standard'
  )/2
)*1;

Works fine but doing the same query 3 times I don't think is a good idea, the count is not "expensive" but every time someone wants to modify/update the query will need to do it in the 3 sections.

Note that currently n is set as 2 and the offset is set to 1 but both can take other values. Also limit rounds down so there may be a missing value, I can fix it using other means but having it on the query will be nice.

You can see the example here

Evan Carroll · Accepted Answer · 2017-04-14 04:45:34Z

Just to dispel a myth you can never use an serial and modulus to get parts because a serial isn't guaranteed to be gapless. You can use row_number() though.

SELECT row_number() OVER () % 3 AS parts, * FROM products;
 parts |                  id                  | title |   kind    | created_at 
-------+--------------------------------------+-------+-----------+------------
     1 | 61c5292d-41f3-4e86-861a-dfb5d8225c8e | foo   | standard  | 2017/04/01
     2 | def1d3f9-3e55-4d1b-9b42-610d5a46631a | bar   | standard  | 2017/04/02
     0 | cc1982ab-c3ee-4196-be01-c53e81b53854 | qwe   | standard  | 2017/04/03
     1 | 919c03b5-5508-4a01-a97b-da9de0501f46 | wqe   | standard  | 2017/04/04
     2 | b3d081a3-dd7c-457f-987e-5128fb93ce13 | tyu   | other     | 2017/04/05
     0 | c6e9e647-e1b4-4f04-b48a-a4229a09eb64 | ert   | irregular | 2017/04/06
(6 rows)

This won't get equal parts unless parts goes into count equally.

Collectives™ on Stack Overflow

Reuse a query to use it for operation on LIMIT and OFFSET on postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related