8

I am trying to use GROUP BY in PostgreSQL 9.4.1 and not having as much success as I hoped.

There are several folks on the web that claim this should work, but I can't get the same results. All I want is a case-insensitive GROUP BY but every time I add LOWER() it complains with:

ERROR: column "people.fn" must appear in the GROUP BY clause or be used in an aggregate function

CREATE DATABASE TEST;
CREATE TABLE people (id INTEGER, fn TEXT, ln TEXT); /* ID, firstname, lastname */

INSERT INTO people (id, fn, ln) VALUES(1,'Mike','f');
INSERT INTO people (id, fn, ln) VALUES(2,'adam','b');
INSERT INTO people (id, fn, ln) VALUES(3,'bill','b');
INSERT INTO people (id, fn, ln) VALUES(4,'Bill','r');
INSERT INTO people (id, fn, ln) VALUES(5,'mike','d');
INSERT INTO people (id, fn, ln) VALUES(6,'mike','c');
INSERT INTO people (id, fn, ln) VALUES(7,'Mike','T');

SELECT fn FROM people GROUP BY LOWER(fn);  /* will not run */
SELECT fn FROM people GROUP BY fn;  /* runs, but not what I want */

Here's what I get:

adam
mike
Mike
bill
Bill

Here's what I want:

Mike
adam
bill

Obviously, there's something I'm missing. And no, I can't just sanitize the data as I put it into the database. What should I read to understand this?

Community
  • 1
  • 1
kmort
  • 2,848
  • 2
  • 32
  • 54

1 Answers1

13

Generally, if you want to select something in the aggregate query, you have to group by this "something". In your case, you can get results you want by selecting lower(fn):

select lower(fn)
from people
group by lower(fn)

Luckily, PostgreSQL allows you to group by alias, so you don't have to repeat lower(fn) twice:

select lower(fn) as lfn
from people
group by lfn

sql fiddle demo

As @okaram mentioned in the comments, if you don't need any other aggrgation on the table, you'd better use distinct:

select distinct lower(fn)
from people
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Wonderful. I knew it would be something simple. Thanks Roman. – kmort Apr 24 '15 at 20:25
  • This is because there could be several values in the same group, and SQL cannot decide which want you want; Mike mike MIKE would all map to mike, so the same group (mike) would have more than one value, which can't happen – okaram Apr 24 '15 at 20:27
  • 1
    And you probably want DISTINCT rather than GROUP BY here, as in SELECT DISTINCT LOWER(fn) FROM people – okaram Apr 24 '15 at 20:27