Convert all records in postgres to Titlecase, first letter uppercase

Question

I have a simple table in PostgreSQL called keywords with a simple text field called name. I want to convert all names of keywords in first letter uppercase. Is there a way to do it from psql console?

Answer 1

There is an initcap() function, if you're meaning to uppercase the first letter of each keyword and to lowercase the following characters:

update foo set bar = initcap(bar) 

Else combine substring() and upper():

update foo set bar = upper(substring(bar from 1 for 1)) ||           substring(bar from 2 for length(bar)) 

http://www.postgresql.org/docs/current/static/functions-string.html

Answer 2

select initcap('r. lópez viña tondonia Rioja White Viña'); 

This does give the correct answer (R. López Viña Tondonia Rioja White Viña) in our version of Postgres (9.0.7).

Answer 3

@denis, Gave the Right Answer!

But in my case I use PgAdmin3 , so after selecting the database there is SQL query Options , So there we can directly add the above query in it.

I had a table called subcategory_subcategory(name of table) in that i wanted to change a column values whose name was Item_name(name of column ) , so my query was like this

update subcategory_subcategory
set Item_name = initcap(Item_name)

Answer 4

The initcap function capitalizes letters after special characters (dashes, apostrophes, etc). I only want to capitalize after a space.

Similar to Denis' answer, this function will convert the first letter of every word (separated by a space)

CREATE OR REPLACE FUNCTION titlecase(instr varchar) RETURNS varchar AS $$
DECLARE
  strarray varchar[] := string_to_array(inStr, ' ');
  outstrarray varchar[];
  word varchar;
BEGIN
  FOREACH word IN ARRAY strarray
  LOOP
    outstrarray := array_append(outstrarray, (upper(left(word,1))::varchar || 
lower(right(word,-1))::varchar)::varchar);
  END LOOP;
  RETURN array_to_string(outstrarray,' ','');
END;
$$ LANGUAGE 'plpgsql';