Search code examples
sqlpostgresqlsql-update

SQL initcap on text[]


I am trying to uppercase all the words in an array of text in my table:

update locations
set aka  = initcap(aka) 

But initcap() doesn't support text arrays. How can I achieve the same effect?


Solution

  • Cast to text and back:

    UPDATE locations
    SET    aka = initcap(aka::text)::text[]
    WHERE  aka IS DISTINCT FROM initcap(aka::text)::text[];
    

    But don't touch rows that wouldn't change, to make it cheaper. See:

    I assume you are aware the initcap() does not exactly "uppercase" strings. It ...

    Converts the first letter of each word to upper case and the rest to lower case.