Using "LIMIT OFFSET" with array_to_json in PostgreSQL

Question

I've a problem when using limit offset in postgresql. Although I specify limit offset values, it lists all data.

SELECT 
        array_to_json(
            array_agg(
                json_build_object(
                    'nickName', u.username,
                    'date', to_char(p.create_date, 'DD/MM/YYYY'),
                    'time', to_char(p.create_date, 'HH24:MM'),
                    'questionId', p.post_id,
                    'questionContent', p.content,
                    'status', CASE WHEN p.status = 0 THEN 'Waiting for approval' WHEN p.status = 1 THEN 'Approved' WHEN p.status = 0 THEN 'Reject' END,
                    'rejectReason', p.reject_reason,
                    'answerUrl', p.seo_url
                )
            )
        )
  INTO _posts
FROM posts p
  INNER JOIN users u ON u.user_id = p.user_id 
WHERE p.user_id = _user_id
LIMIT 5 OFFSET _page * 5;

Please review my code and tell me where the problem is.

Unrelated, but: create table new_table as select ... is preferred over the non-standard select ... into new_table — user330315
– user330315, Commented Feb 13, 2019 at 13:43

Lucas · Accepted Answer · 2019-02-13 11:04:20Z

2

Without rewriting the whole query for you, json_agg may be more appropriate here.

SELECT json_agg(x) AS json_feed
FROM (
    SELECT
        column AS "columnName", ...
    FROM your_table
    LIMIT 5
) AS x

answered Feb 13, 2019 at 11:04

Lucas

6301 gold badge4 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Using "LIMIT OFFSET" with array_to_json in 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