Postgresql 9.2 error with group by which doesn't appear in postgresql 12

Question

I have a postgresql function with 'GROUP BY' which works fine on my local server (postgresql version is 12.6) but gets the famous error 'column "f.fa_docnum" must appear in the GROUP BY clause or be used in an aggregate function' on the web server, which runs postgresql 9.2.24. I'm completely confused about that. The documentation of the 2 versions are exactly the same for the GROUP BY clause.

Here is the function (a bit simpler than the original one, but it gets the same results) :

SELECT 
            f.fa_docnum,
            f.fa_date, 
            sum(l.lj_credit), 
            sum(l.lj_debit)
            FROM livrejournal l, document d, facture_achat f
            WHERE d.doc_id = l.lj_docid 
            AND d.doc_fa_docnum = f.fa_docnum
            AND f.fa_fournisseur = 59               
            GROUP BY (f.fa_docnum, f.fa_date);

On the production site with 9.2 version, I get no error if I delete 'f.fa_date' column in the GROUP BY clause, while on the local dev site with 12.6 (and formerly lower versions) I get no error in any case. If anybody has an idea about this strange behavior, and maybe some tip or workaround, I would greatly appreciate it.

Unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Jun 27, 2021 at 21:08

user330315 · Accepted Answer · 2021-06-27 21:05:42Z

0

You are only grouping by a single column due to the parentheses around the columns. The expression (f.fa_docnum, f.fa_date) is a single column with an anonymous record type that contains two fields.

It seems that Postgres 9.2 can't detect that one of the fields of the record expression is the primary key of the table and thus throws the error. While Postgres 12 can detect that one of the record's fields is the primary key column of the table.

The solution is to remove the parentheses around the columns. They server no purpose to begin with and are actually an error in your case.

answered Jun 27, 2021 at 21:05

user330315

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

1 Comment

Erik Martre Over a year ago

Yes ! Your answer is perfect, thanks a lot. Unfortunately my reputation is still too low to allow me to upvote it. And for 9.2 version, I know it is obsolete but my host works with Cpanel which hasn't updated Postgresql nor phpPgAdmin, in spite of requests from (probably not so many) users.

Collectives™ on Stack Overflow

Postgresql 9.2 error with group by which doesn't appear in postgresql 12

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related