0

I have a table with the columns key_id, account_id, title, bpic, description, legal, address, where the primary key is key_id. But I have a problem regarding the title of some entries, some titles use capital letters and others don't, which results in getting two identical titles. For example, one row has the title "abcd123" and another "Abcd123". I want to know how I can get all the rows but filter out the duplicates using group by, it doesn't matter which one of the two rows is left, I only need to get rid of one of them.

Andrei Gaina
  • 21
  • 3
  • 1
    Does this answer your question? [PostgreSQL GROUP BY LOWER() not working](https://stackoverflow.com/questions/29856589/postgresql-group-by-lower-not-working) – Nick ODell Jul 16 '22 at 18:31
  • @NickODell That would be perfect, but I need data from all columns, not just get the titles – Andrei Gaina Jul 16 '22 at 19:03
  • How about `SELECT DISTINCT ON (LOWER(title), * ... ` like in this question? https://stackoverflow.com/questions/42556344/aggregate-function-that-returns-any-value-for-a-group – Nick ODell Jul 16 '22 at 20:22
  • 1
    The best option would be: 1. fix the data by identifying and correcting/deleting duplicates. 2. define new column (example ltitle) as `ltitle text generated always as (lower (title)) stored` [see demo](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=cab4d432adff1fb65b8086170d43b589). 3 Put a unique constraint on `ltitle`. 4. Run queries against `ltitle` not `title` – Belayer Jul 17 '22 at 03:36

0 Answers0