postgresqlpostgres-10

How to write select statement with a condition of case-insensitive from a select query output

I'm getting data like this

from the query select distinct(trim(name)) as loc from locations order by trim(name)

Now I want to remove duplicates using lower() or upper() and select the first record . If I use lower function like below,

select distinct(lower(A.loc)) from( select distinct(trim(name)) as loc from locations order by trim(name) ) as A order by lower(A.loc);

it gives result converted to lower as below.

Solution

  • demo:db<>fiddle

    SELECT DISTINCT ON (lower(city))
    city
FROM
    cities

    DISTINCT ON takes an arbitrary column and gives out the first one of duplicates. In this case a column is created internally with all lower case. Then the first record is taken, but only the original column.

    NOTE You have no special order. It will be taken a random order (which is for example how data is stored internally). To achieve the upper case value you have to write:

    SELECT DISTINCT ON (lower(city))
    city
FROM
    cities
ORDER BY lower(city), city DESC

    demo:db<>fiddle

    The ORDER BY lower(city) is necessary because the DISTINCT ON needs the given columns to be the first ordered. After that you can order by any other column. ORDER BY column DESC moves the upper cases top.