group by in postgres sql with error must appear in the GROUP BY clause or be used in an aggregate function

Question

I have a query below:

SELECT "post".*, "schedule"."from_time" AS "scheduleFrom", "schedule"."to_time" AS "scheduleTo", "schedule"."id" AS "scheduleId" 
FROM "post" LEFT JOIN "post_schedule" ON "post"."id" = "post_schedule"."post_id" LEFT JOIN "schedule" ON "post_schedule"."schedule_id" = "schedule"."id" 
WHERE ("post"."type"='session') AND ("schedule"."from_time" >= 1497441023 or "post_schedule"."is_repeat" = TRUE) 
AND (post.id = 72 or post.id = 219 or post.id = 227 or post.id = 220 or post.id = 221)
AND (schedule.status = '1') 
Group by post.id

It's work fine with mysql, but in postgres I have an error like that: must appear in the GROUP BY clause or be used in an aggregate function.

This is table post

CREATE TABLE public.post
(
    id integer NOT NULL DEFAULT nextval('post_id_seq'::regclass),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    excerpt character varying(255) COLLATE pg_catalog."default",
    content text COLLATE pg_catalog."default",
    author_id integer,
    published_at integer,
    slug character varying(255) COLLATE pg_catalog."default",
    type character varying(10) COLLATE pg_catalog."default",
    meta_title character varying(255) COLLATE pg_catalog."default",
    meta_description character varying(255) COLLATE pg_catalog."default",
    meta_keywords character varying(255) COLLATE pg_catalog."default",
    status smallint NOT NULL DEFAULT 10,
    created_at integer NOT NULL,
    updated_at integer NOT NULL,
    created_by integer NOT NULL,
    updated_by integer NOT NULL,
    CONSTRAINT post_pkey PRIMARY KEY (id),
    CONSTRAINT "fk-post-author_id" FOREIGN KEY (author_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

post_schedule

CREATE TABLE public.post_schedule
(
    id integer NOT NULL DEFAULT nextval('post_schedule_id_seq'::regclass),
    post_id integer NOT NULL,
    schedule_id integer NOT NULL,
    is_repeat boolean DEFAULT false,
    CONSTRAINT post_schedule_pkey PRIMARY KEY (id),
    CONSTRAINT "fk-post_schedule-post_id" FOREIGN KEY (post_id)
        REFERENCES public.post (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "fk-post_schedule-schedule_id" FOREIGN KEY (schedule_id)
        REFERENCES public.schedule (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

and table schedule

CREATE TABLE public.schedule
(
    id integer NOT NULL DEFAULT nextval('schedule_id_seq'::regclass),
    name character varying(255) COLLATE pg_catalog."default",
    status character varying(255) COLLATE pg_catalog."default",
    "from" character varying(255) COLLATE pg_catalog."default",
    "to" character varying(255) COLLATE pg_catalog."default",
    created_at integer NOT NULL,
    updated_at integer NOT NULL,
    created_by integer NOT NULL,
    updated_by integer NOT NULL,
    from_time integer,
    to_time integer,
    CONSTRAINT schedule_pkey PRIMARY KEY (id)
)

example for data when I query with non group by: here

How can I get first row foreach post.id?

You haven't used any aggregate function in your select. You therefore don't need the group by clause. If you wish to group things together, then use group by with the appropriate aggregate function e.g. count(*), unless it's already a single column select (in which case the group by is still redundant). See [here](http://www.postgresqltutorial.com/postgresql-group-by/). "How can I group by post.id but not add any field into group by?" - by what criteria are you looking to group them? By count, maximum, minimum, etc. If all you want is for them to be ordered, then use order by, not group by. — ManoDestra, Jun 14 '17 at 13:18
It works "fine" in MySQL because MySQL will return random data for this type of (invalid) query. — , Jun 14 '17 at 13:19
Example with no group by, the query is return 3 post with post.id =220, but I just need only first row, can you help me? — Hoan Thanh, Jun 14 '17 at 13:30
Use an aggregate function such as max(id), something like that. You will have to add all the other columns into your group by clause. Or use some form of inner query with a limit 1 clause. Be clear and figure out what you actually need, then retrieve only that. If you want people to give a solution, then you may have to post the table design and some sample data (obfuscated, if necessary).. — ManoDestra, Jun 14 '17 at 13:42
... [or even more questions like this](https://stackoverflow.com/search?tab=votes&q=%5bpostgresql%5d%20must%20appear%20in%20the%20GROUP%20BY%20clause%20or%20be%20used%20in%20an%20aggregate%20function) — pozs, Jun 14 '17 at 16:07

group by in postgres sql with error must appear in the GROUP BY clause or be used in an aggregate function

0 Answers0