ORDER BY changed column with same name

Question

I have a table artist with several columns like name, type ...
I want the column name to be returned and ordered alphabetically, but the column name should be adjusted. If the name of the artist starts with 'the ' (case insensitive) then it should be removed and placed behind the name in uppercase, for example 'the Beatles' -> 'Beatles, THE'.

This is the code I have (it doesn't work):

SELECT name,
  CASE
    WHEN UPPER(name) LIKE 'THE %' THEN CONCAT(RIGHT(name, length(name)-4),', THE')
  END AS name 
FROM artist
ORDER BY name

Problem 1: name is not replaced, it creates a new column.

Problem 2: new column has same name => column name is ambiguous in order by name.

How can I easily solve those problems?

Answer 1

Use ELSE clause in CASE:

SELECT
  CASE
    WHEN UPPER(name) LIKE 'THE %' THEN CONCAT(RIGHT(name, length(name)-4),', THE')
    ELSE name
  END AS name 
FROM artist
ORDER BY name

Answer 2

If you only want this for ordering, then you can put the logic only in the order by clause:

order by (case when name ilike 'the%'
               then substring(name from 5) || ', The'
               else name
          end)

If you also want this column in the select clause, then use an alias other than name. That is already used for your first column:

select name,
       (case when name ilike 'the%'
             then substring(name from 5) || ', The'
             else name
        end) as munged_name
. . .
order by munged_name

Note: ilike is a Postgres extension for case-insensitive like.

Answer 3

Query

SELECT CASE WHEN name ILIKE 'THE %'  -- simpler
            THEN right(name, -4) || ', THE'  -- simpler, faster
            ELSE name END AS name  -- but better use a distinct alias
      , *
FROM   artist
ORDER  BY 1;  -- positional reference to 1st output column

Rationale

• The simplest and fastest expression to get a string minus n leading characters is: right(string, -n).

• ILIKE is simpler than lower() / upper() LIKE ....

• You don't need concat(), the plain concatenation operator || does the same faster since the expression right(name, -4) is guaranteed to be NOT NULL in this context.

• When confused with SQL visibility rules for column names, or when there are duplicate output column names (which is totally legit in Postgres) you can also use positional references in GROUP BY, ORDER BY or DISTINCT ON (...) clauses:

  • When can we use an identifier number instead of its name in PostgreSQL?
  • How to re-use result for SELECT, WHERE and ORDER BY clauses?

  However, it's hardly wise or useful to have duplicate output column names in the first place. Rather use distinct names.

• If you run this query a lot I suggest a functional index on the same expression for much faster results:

  CREATE INDEX artist_name_sort_idx ON artist 
   ((CASE WHEN name ILIKE 'THE %' THEN right(name, -4) || ', THE' ELSE name END));
  

The expression in the query must be the same to employ this index.

Proper test case

WITH artist(artist_id, name) AS (
   VALUES
      (1, 'The Beatles')   
    , (2, 'tHe bEatles')
    , (3, 'The The')
    , (4, 'Then')
    , (5, 'The X')
    , (6, 'Theodor')
    , (7, 'Abba')
    , (8, 'ZZ TOP')
    , (9, 'The ')  -- unlikely corner case, I don't think it would pay to test for it
    , (10, '')      -- empty string
    , (11, NULL)   -- NULL
   )
SELECT CASE WHEN name ILIKE 'THE %' THEN right(name, -4) || ', THE' ELSE name END AS name
      , *
FROM   artist
ORDER  BY 1;

Result:

     name     | artist_id |    name
--------------+-----------+-------------
              |        10 |
 , THE        |         9 | The           -- remaining corner case
 Abba         |         7 | Abba
 bEatles, THE |         2 | tHe bEatles
 Beatles, THE |         1 | The Beatles
 The, THE     |         3 | The The
 Then         |         4 | Then
 Theodor      |         6 | Theodor
 X, THE       |         5 | The X
 ZZ TOP       |         8 | ZZ TOP
 <NULL>       |        11 | <NULL>