ts_vector GROUP BY Месяц

Question

Учитывая следующий SQL

SELECT 'food' keyword, sum(ndoc) documents, sum(nentry) occurances
FROM ts_stat('SELECT tsvector FROM posts WHERE created_at BETWEEN ''2018-06-01'' AND ''2018-10-01''')
WHERE word IN ('food');

Я хотел бы сгруппировать результат по месяцам, чтобы конечный результат содержал 3 столбца, в которых 1-й столбец будет месяц, второй и третий - ndoc и nentry.

Я попытался со следующим sql, без удачи:

SELECT created_at, "food" keyword, sum(ndoc) documents, sum(nentry) occurances
FROM ts_stat('SELECT tsvector, created_at FROM posts WHERE created_at BETWEEN ''2018-06-01'' AND ''2018-10-01''')
WHERE word IN ('makanan')
GROUP BY MONTH(created_at);

Может кто-нибудь мне помочь?

РЕДАКТИРОВАТЬ

Вот мой DDL

create table posts
(
    id serial not null
        constraint posts_pkey
            primary key,
    . 
    . . 
    . . .
    . . 
    . 
    created_at timestamp,
    updated_at timestamp,
    location geography(Point,4326),
    tsvector tsvector
)
;

create index index_posts_on_city_id_and_category_id
    on posts (city_id, category_id)
;

create index index_posts_on_city_id_and_district_id
    on posts (city_id, district_id)
;

create index index_posts_on_city_id
    on posts (city_id)
;

create index index_posts_on_category_id
    on posts (category_id)
;

create index index_posts_on_district_id_and_category_id
    on posts (district_id, category_id)
;

create index index_posts_on_subcategory_id
    on posts (subcategory_id)
;

create index index_posts_on_location
    on posts (location)
;

create index index_posts_on_tsvector
    on posts (tsvector)
;

Answer 1

Попробуйте ниже, используя EXTRACT(MONTH FROM created_at)

 SELECT EXTRACT(MONTH FROM created_at) AS mon,
       'food' AS keyword,
        sum(ndoc) documents,
        sum(nentry) occurances
FROM ts_stat('SELECT tsvector, created_at FROM posts WHERE created_at BETWEEN ''2018-06-01'' AND ''2018-10-01''')
WHERE word IN ('makanan')
GROUP BY EXTRACT(MONTH FROM created_at);

Answer 2

Это то, что вы хотите?

SELECT 
    to_char(created_at, 'MM') as Month, 
    'food' keyword,
    sum(ndoc) documents, 
    sum(nentry) occurances
FROM 
    ts_stat('SELECT tsvector, created_at FROM posts WHERE created_at BETWEEN ''2018-06-01'' AND ''2018-10-01''')
WHERE 
    word IN ('makanan')
GROUP BY 
    to_char(created_at, 'MM');