Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Source - https://stackoverflow.com/q/79899661
-- Posted by Clodoaldo Pinto, modified by community. See post 'Timeline' for change history
-- Retrieved 2026-03-02, License - CC BY-SA 4.0

create extension if not exists unaccent;
drop text search configuration if exists portugues_sem_acento;
create text search configuration portugues_sem_acento (copy = portuguese);
alter text search configuration portugues_sem_acento
    alter mapping for numhword, word, asciiword, numword
    with unaccent, portuguese_stem;

CREATE EXTENSION

DROP TEXT SEARCH CONFIGURATION

CREATE TEXT SEARCH CONFIGURATION

ALTER TEXT SEARCH CONFIGURATION

alter database postgres set default_text_search_config to 'portugues_sem_acento';
select current_setting('default_text_search_config',false)
     , to_tsvector('Cação')
     , to_tsquery('cacao')
     , to_tsvector('Cação') @@ to_tsquery('cacao') as "@@";

ALTER DATABASE

current_setting
to_tsvector
to_tsquery
@@

pg_catalog.simple
'cação':1
'cacao'
f

SELECT 1

set default_text_search_config to 'portugues_sem_acento';

select current_setting('default_text_search_config',false)
     , to_tsvector('Cação')
     , to_tsquery('cacao')
     , to_tsvector('Cação') @@ to_tsquery('cacao') as "@@";

SET

current_setting
to_tsvector
to_tsquery
@@

public.portugues_sem_acento
'caca':1
'caca'
t

SELECT 1

SELECT * FROM ts_debug('portugues_sem_acento', 'Cação');

alias
description
token
dictionaries
dictionary
lexemes

word
Word, all letters
Cação
{unaccent,portuguese_stem}
unaccent
{Cacao}

SELECT 1