*** a/contrib/pg_trgm/Makefile
--- b/contrib/pg_trgm/Makefile
***************
*** 4,13 **** MODULE_big = pg_trgm
OBJS = trgm_op.o trgm_gist.o trgm_gin.o trgm_regexp.o $(WIN32RES)
EXTENSION = pg_trgm
! DATA = pg_trgm--1.2.sql pg_trgm--1.0--1.1.sql pg_trgm--1.1--1.2.sql pg_trgm--unpackaged--1.0.sql
PGFILEDESC = "pg_trgm - trigram matching"
! REGRESS = pg_trgm
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 4,13 ----
OBJS = trgm_op.o trgm_gist.o trgm_gin.o trgm_regexp.o $(WIN32RES)
EXTENSION = pg_trgm
! DATA = pg_trgm--1.3.sql pg_trgm--1.0--1.1.sql pg_trgm--1.1--1.2.sql pg_trgm--1.2--1.3.sql pg_trgm--unpackaged--1.0.sql
PGFILEDESC = "pg_trgm - trigram matching"
! REGRESS = pg_trgm pg_substring_trgm
ifdef USE_PGXS
PG_CONFIG = pg_config
*** /dev/null
--- b/contrib/pg_trgm/data/trgm2.data
***************
*** 0 ****
--- 1,1083 ----
+ Baikal
+ Baikal
+ Baikaluobbal
+ Lake Baikal
+ Baikalakko
+ Baikal Business Centre
+ Baikal Listvyanka Hotel
+ Baikal Airfield
+ Baikalovo
+ Transbaikalia
+ Baikal Mountains
+ Baikal Hotel Moscow
+ Zabaikalie
+ Pribaikalskaya
+ Baikal Plaza
+ Rubaikale
+ Tandobai Algad
+ Daikalay
+ Bakall
+ Stubaital
+ Neustift im Stubaital
+ Neustift im Stubaital
+ Anonyme Appartments Stubaital
+ Barkaladja Pool
+ Awabakal Nature Reserve
+ Awabakal Field Studies Centre
+ Barkala
+ Bailallie
+ Barkala
+ Barkala
+ Bailallie
+ Barkala Park
+ Barkala
+ Barkala
+ Barkala
+ Purba Kalaujan
+ Nabakalas
+ Barkal
+ Baikanthapur
+ Baikarjhuti
+ Baika
+ Baikari
+ Bakalia Char
+ Dakshin Bakalia
+ Barkal
+ Purba Kalmegha
+ Purba Kalmegha
+ Baikari
+ Barkal
+ Efreytor-Bakalovo
+ Baykalsko
+ Baykal
+ Baskaltsi
+ Bakalite
+ Bajkal
+ Efrejtor Bakalovo
+ Kampong Bakaladong
+ Riacho do Sambaibal
+ Sambaibal
+ Barkalabava
+ Zabaykal
+ Bakalar Lake
+ Kaikalahun Indian Reserve 25
+ Tumba-Kalamba
+ Kamba-Kalele
+ Boyagbakala
+ Bombakalo
+ Batikalengbe
+ Bakalukudu
+ Bakalawa
+ Bakala
+ Bakala
+ Matamba-Kalenge
+ Kusu-Bakali
+ Kambakala
+ Bakali
+ Abakalu
+ Bakala
+ Bonagbakala
+ Bakalua
+ Bikala Madila
+ Bikala
+ Bikala
+ Bumba-Kaloki
+ Tumba-Kalunga
+ Kabankala
+ Mambakala
+ Tumba-Kalumba
+ Kabakala
+ Buyamba-Kalala
+ Bikalabwa
+ Kamba-Kala
+ Bomba-Kalende
+ Mwalaba-Kalamba
+ Matamba-Kalenga
+ Kabankala
+ Kabankala
+ Bumba-Kalumba
+ Kabankala
+ Bikalange
+ Kabikala
+ Mubikale
+ Kanampumba-Kalawa
+ Tshiabakale
+ Bikala
+ Bakaly
+ Bakalongo
+ Bakale
+ Bakala Koupi
+ Bakala
+ Bakala
+ Bakala
+ Bambakala
+ Bakalou
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Bakala
+ Tsibakala
+ Kimbakala
+ Bakala
+ Bikala
+ Bakala
+ Dabakalakoro
+ Dabakala
+ Bakalafoulou
+ Ngao Bakala
+ Bikala
+ Ngao Bakala
+ Bakala
+ Barkala
+ Mobaika
+ Baimalou
+ Xibaitaling
+ Baikai
+ Baikang
+ Baitaling
+ Baikan
+ Baimaling Linchang
+ Baimalong
+ Baikanzui
+ Baiyali
+ Baimaling
+ Baikan
+ Baimalang Donggang
+ Baimaling
+ Baimaling
+ Baimaling
+ Baikangshuoma
+ Baitaliao
+ Baimaling
+ Taikale
+ Babainale
+ Bailale
+ Baibale
+ Baika
+ Baiwale
+ Baikan
+ Baikang
+ Baikangnei
+ Baikai
+ Baitali
+ Xiabaikan
+ Bailalong
+ Baikai
+ Baika
+ Bakala
+ Baimaluo
+ Baikacun
+ Baisala
+ Bailalin
+ Baimala
+ Baidalong
+ Dabaika
+ Caikalong
+ Cuobaikacun
+ Baikadangcun
+ Baikang
+ Baimalin
+ Baikang
+ Subaika
+ Baykal
+ Gabakkale
+ Barkallou
+ Rokba Kala Sardoun
+ Embatkala
+ Bodega Tabaibal
+ Golba Kalo
+ Haikala
+ Haikala
+ Kaikale
+ Waikaloulevu
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou Creek
+ Waikalou
+ Ndelaikalou
+ Ndelaikalokalo
+ Bay of Backaland
+ Bankali
+ Bankali
+ Baika
+ Ker Samba Kalla
+ Demba Kali
+ Bakalarr
+ Baipal
+ Kalibakalako
+ Dalabakala
+ Bankalan
+ Bikal
+ Sembaikan
+ Praikalogu
+ Tanjung Ompaikalio
+ Bonebabakal
+ Tanjung Batikala
+ Pulau Bakalanpauno
+ Teluk Bakalan
+ Bakaltua Bank
+ Bakalrejo
+ Bakalan
+ Bakalan
+ Bakalan
+ Sungai Bakaladiyan
+ Bakal
+ Buku Baikole
+ Pulau Baika
+ Tanjung Bakalinga
+ Pulau Bakalan
+ Bakala
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Kebakkalang
+ Ngambakalang
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Mota Sabakal
+ Bakalan
+ Bakalan
+ Bakalan Lor
+ Bakalan
+ Bakalan
+ Babakalo
+ Buyu Rapanbakalai
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Kalimundubakalan
+ Bakalan
+ Bakal
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalpokok
+ Bakaldukuh
+ Tanabakal
+ Tanjung Aikaluin
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalrejo
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan Kidul
+ Bakalan
+ Bakalan Lor
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan Lor
+ Bakalan Kidul
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Kebakalan
+ Kebakalan
+ Bakalan
+ Desa Kebakalan
+ Kebakalan
+ Bakalan Kulon
+ Bakalan
+ Bakalan
+ Gunung Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Kalibakal
+ Bakaljaya
+ Bakalan
+ Bakalan
+ Bakalan
+ Gunung Bakalan
+ Trobakal
+ Bakalan
+ Bakalan Kulon
+ Bakalan Wetan
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakal
+ Kebakalan
+ Bakalan
+ Desa Bakal
+ Bakalan
+ Bakal
+ Bakal
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Alue Bakkala
+ Uruk Bakal
+ Bakalbuah
+ Kwala Bakala
+ Bakal Lama
+ Bakal Julu
+ Bakal Batu
+ Moncong Baika
+ Bakalan
+ Bakalan
+ Desa Bakalrejo
+ Sampangbakalan
+ Bakalam
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalankrapyak
+ Bakalan
+ Bakalan
+ Lebakkalapa Tonggoh
+ Trembakal
+ Bakal
+ Desa Bakalan
+ Bakalan
+ Bakalan Tengah
+ Bakalan Kidul
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Kali Bakalan
+ Desa Cemengbakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalrejo
+ Bakalan
+ Desa Bakalanpule
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Gunung Bakal
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Desa Tambakkalisogo
+ Tambakkalisogo
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalan
+ Bakalan
+ Kali Bakalan
+ Bakalan
+ Desa Kebakalan
+ Kebakalan
+ Bakalan
+ Kali Bakalan
+ Desa Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalanrayung
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Salu Bakalaeng
+ Bakalaeng
+ Danau Bakalan
+ Selat Bakalan
+ Selat Bakalanpauno
+ Laikalanda
+ Bakalinga
+ Tanjung Mbakalang
+ Tanjung Mbakalang
+ Laikalanda
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Desa Bakalankrajan
+ Bakalan
+ Desa Bakalan
+ Bakalan Dua
+ Kali Purbakala
+ Desa Bakalanwringinpitu
+ Bakalan
+ Tukad Kubakal
+ Praikalangga
+ Banjar Kubakal
+ Eat Bakal
+ Sungai Bakala
+ Kombakalada
+ Bankalan
+ Sori Rabakalo
+ Kahambikalela
+ Baikarara
+ Baikapaka
+ Bakalan
+ Tukad Bakalan
+ Teluk Haludubakal
+ Yabakalewa
+ Praikalumbang
+ Waikalowo
+ Praikalubu
+ Loko Praikalubu
+ Ramuk Ombakalada
+ Praikalebung
+ Praikaleka
+ Praikalumbang
+ Bakalan
+ Andabakal
+ Praikalau
+ Praikalokat
+ Praikalimbung
+ Bambakalo
+ Leubakkalian
+ Pematang Baitalimbangan
+ Lebakalil
+ Gereba Kaler
+ Krajan Bakalan
+ Bakalan Lor
+ Bakalan
+ Bakalan
+ Bakal
+ Bakalan Barat
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan
+ Bakalan Kulon
+ Bakalan Wetan
+ Bakalan
+ Bakalan
+ Muarabakal
+ Umbulan Maharobakal
+ Bakaldalam
+ Talang Bakal
+ Pematang Bakalpanang
+ Baidaloen
+ Jatibakal
+ Tubu Bakalekuk
+ Dola Peimambakal
+ Bakalang
+ Teluk Bakalang
+ Salu Baidale
+ Bakalerek
+ Ile Bakalibu
+ Parbakalan
+ Praikalembu
+ Palindi Laikali
+ Praikalu
+ Sori Labakalate
+ Air Bakal-kecil
+ Sungaikalung
+ Sungaikalong
+ Pematang Bakalpanjang
+ Bakaldalam
+ Payabakal
+ Waikala
+ Sungaikali
+ Sungai Pebakalan
+ Parit Membakal
+ Bakalpakebo
+ Baikat Abu Jaraban
+ Maikalganj
+ Maikala Range
+ Bakalha
+ Baitalpur
+ Baikanthpur
+ Baihal
+ Barkala Reserved Forest
+ Barkala
+ Babaipalli
+ Kaikalapettai
+ Kambainallur
+ Bakkalale
+ Kaikalui
+ Baijalpur
+ Barkal
+ Barkal
+ Bankalah
+ Nehalla Bankalah Reserved Forest
+ Barkala Rao
+ Barkala
+ Barkala
+ Baijalpur
+ Barkali
+ Barkali
+ Baidal
+ Barkal
+ Barkaleh
+ Darreh Pumba Kal
+ Bahkalleh
+ Wibakale
+ Gaikali
+ Gagaba Kalo
+ Barkalare
+ Bakal
+ Bakkalmal
+ Bakaly
+ Gora Bakalyadyr
+ Rodnik Bakalybulak
+ Urochishche Bakaly
+ Sopka Bakaly
+ Gory Bakaly
+ Bakaly
+ Bugor Arba-Kalgan
+ Ozero Baykal
+ Kolodets Tabakkalgan
+ Walangivattu Vaikal
+ Vattevaikal Anicut
+ Vaikali Tevar Kulam
+ Vaikalitevan Kulam
+ Vaikaladichchenai
+ Uchchodaikallu
+ Sellapattu Vaikal
+ Savata Vaikal
+ Puttadivali Vaikal
+ Palukadu Vaikal
+ Mulaikallu Kulam
+ Koraikallimadu
+ Koraikalapu Kulam
+ Karaiyamullivaikal
+ Karaivaikal Kulam
+ Kanawali Vaikal
+ Habakkala
+ Chalam Vaikal Aru
+ Ambakala Wewa
+ Alaikallupoddakulam
+ Alaikallupodda Alankulam
+ Akamadi Vaikal
+ Alaikalluppodda Kulam
+ Vaikaliththevakulam
+ Baikole
+ Baikai
+ Sidi Mohammed el Bakali
+ Sidi Mohammed Bakkal
+ Sidi Bakal
+ Oulad el Bakkal
+ Zaouia Oulad Bakal
+ Azib el Bakkali
+ Bakal
+ Tombakala
+ Malaikaly
+ Ambadikala
+ Ambadikala
+ Bakalica
+ Bakalica
+ Bakalnica
+ Bakal
+ Abankala
+ Kombakala
+ Bawkalut
+ Bakaleko
+ Bawkalut Chaung
+ Baukala
+ Bankala Hill
+ Bakali
+ Cerro Bainaltzin
+ Sungai Bakal
+ Bukit Ubaibalih
+ Kampong Sombakal
+ Kampung Lebai Ali
+ Batikal
+ Bakalalan Airport
+ Bakal
+ Bankali
+ Bakala
+ Maikali
+ Bikal
+ Barkala
+ Bakalum
+ Bakali
+ Bakalambani
+ Abakaliki
+ Tsaunin Maikalaji
+ Abakaliki
+ Bakale
+ Baikaha
+ Llano Limbaika
+ Barkald
+ Barkald stasjon
+ Barkaleitet
+ Barkaldfossen
+ Barkaldvola
+ Bakkalegskardet
+ Baikajavri
+ Baikajavri
+ Barkald
+ Barkalden
+ Bakkalia
+ Barkald
+ Siljabaika
+ Aikaluokta
+ Blombakkali
+ Bakkalia
+ Bavkalasis
+ Baikajohka
+ Bakkalykkja
+ Bakalauri
+ Bakalauri1
+ Bakalauri2
+ Bakalauri3
+ Bakalauri4
+ Bakalauri5
+ Bakalauri6
+ Bakalauri7
+ Bakalauri8
+ Bakalauri9
+ Bakal
+ Bakal
+ Bakalsen
+ Baika
+ Baiyaldi
+ Bakale
+ Naikala
+ Baikanda
+ Barkalne
+ Baijalpur
+ Bakalipur
+ Bakaldum
+ Bakal
+ Raikal
+ Baikatte
+ Maikal
+ Bakalbhar
+ Waikalabubu Bay
+ Baikai Island
+ Abikal
+ Boikalakalawa Bay
+ Maikal River
+ Bakalao Asibi Point
+ Kabankalan
+ Kabankalan
+ Kabankalan
+ City of Kabankalan
+ Bikal
+ Bankal
+ Bankal
+ Bankal
+ Bakalod Island
+ Bakalao Point
+ Bakalan River
+ Bakal Dos
+ Bakal Uno
+ Daang Bakal
+ Bankal School
+ Kabankalan
+ Bakal Tres
+ Daang Bakal
+ Kabankalan City Public Plaza
+ Ranra Tabai Algad
+ Bairkal Jabal
+ Bairkal Dhora
+ Bairkal
+ Zaibai Algad
+ Gulba Kalle
+ Ragha Bakalzai
+ Dabbarkal Sar
+ Tabai Algad
+ Haikalzai
+ Wuchobai Algad
+ Jabba Kalai
+ Goth Soba Kaloi
+ Baikar Tsarai
+ Dudgaikal
+ Baixale Kamar
+ Zebai Algad
+ Bakal Khel
+ Goth Haikal
+ Haikal
+ Jaba Kalle
+ Bakalovina
+ Bakalovina
+ Salabaikasy
+ Guba Kalita
+ Guba Kalgalaksha
+ Guba Kaldo
+ Bakalovo
+ Baykalovo
+ Baykal
+ Baykal
+ Baskalino
+ Sopka Barkaleptskaya
+ Bakaly
+ Bakaly
+ Bakalovskaya Ferma
+ Bakalovo
+ Bakalinskiy Rayon
+ Sovkhoz Bakalinskiy
+ Bakalinskiy
+ Bakaldy
+ Bakaldinskoye
+ Urochishche Bakaldikha
+ Bakal
+ Zabaykalovskiy
+ Barkalova
+ Barkalovka
+ Gora Barkalova
+ Baykalovo
+ Gora Barkalyu
+ Bikalamakhi
+ Stantsiya Bakal
+ Baykalovskiy Rayon
+ Baykalovskiy
+ Baykalovsk
+ Baykalovo
+ Baykalovo
+ Baykalovo
+ Baykal
+ Bakala
+ Bakalda
+ Bakalinskiy
+ Ozero Baykal
+ Ozero Baykal
+ Boloto Malyy Baykal
+ Boloto Baykal
+ Zabaykalka
+ Stantsiya Baykal
+ Ozero Baykal
+ Baykal
+ Baykal
+ Baykal
+ Baykalo-Amurskaya Zheleznaya Doroga
+ Baykal
+ Baykal
+ Kolkhoz Krasnyy Baykal
+ Zaliv Baykal
+ Ozero Baykal
+ Bakalino
+ Ovrag Bakalda
+ Bakaldovshchina
+ Baykalovo
+ Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Prud Novyy Baykal
+ Ozero Baykal
+ Ozero Baykal
+ Baykal
+ Bakaleyka
+ Bakalka
+ Baykal
+ Bakaly TV Mast
+ Urochishche Bakalovo
+ Kambaika
+ Maloye Baykalovo
+ Baykal
+ Boloto Baykal
+ Ozero Baykal
+ Bakaly
+ Bakalinskiy Leskhoz
+ Ozero Baykal
+ Bakaly
+ Bakaly
+ Bikalikha
+ Bakalka
+ Kordon Barkalo
+ Bakalda
+ Sanatoriy Baykal
+ Port Baykal
+ Baykalikha
+ Polevoy Stan Baykal
+ Baykal
+ Ozero Baykal
+ Bakalovka
+ Ramada Makkah Shubaika
+ Mount Tohebakala
+ Tambakale Island
+ Mbanitambaika Island
+ Mbakalaka Island
+ Kumbakale
+ Kaikaloka
+ Kelesaikal
+ Nasb Gabakallah
+ Jabal Barkal
+ Barkal
+ Jabal Abakallah
+ Al Barkali
+ Shabakal Abbass
+ Mabaikuli
+ Gbonkobankala
+ Bambakalema
+ Bambakalia
+ Baiwala
+ Babakalia
+ Babakalia
+ Baikama
+ Bankala
+ Baikama
+ Bankalol
+ Kundebakali
+ Yumbaikamadu
+ Tabakali
+ Keur Samba Kala
+ Daba Kalharereh
+ Barkale
+ Bakalshile
+ Bakaloolay
+ Buur Bakaley
+ Bakaley
+ Buur Bakale
+ Bakale
+ Bakalaale
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Bakal
+ Jabal Mobakali
+ Khor Bakallii
+ Korombaital
+ Barkala
+ Ambakali
+ Ba Kaliin
+ Barkal
+ Bakala
+ Barkale
+ Mbay Bakala
+ Tagobikala
+ Fayzabadkala
+ Aghbai Allazy
+ Aghbai Alikagar
+ Gora Fayzabadkala
+ Daraikalot
+ Aghbai Alakisirak
+ Beikala
+ Foho Berbakalau
+ Mota Caicabaisala
+ Beikala
+ Sungai Utabailale
+ Sungai Utabailale
+ Urochishche Bakalarnyn-Ayasy
+ Urochishche Batkali
+ Khrebet Batkali
+ Ras Barkallah
+ Baykal
+ Babakale
+ Fabrikalar
+ Bakalukalu Shan
+ Bakalukalu
+ Laikala
+ Bakalovo
+ Waikalakaka
+ Columbus Bakalar Municipal Airport
+ Bakalar Library
+ Bakkala Cemetery
+ Clifton T Barkalow Elementary School
+ Barkalow Hollow
+ Kailuapuhi Waikalua Homesteads
+ Kawaikalia Gulch
+ Waikalae
+ Waikaloa Stream
+ Waikalua-Loko Fish Pond
+ Halekou Waikaluakai Homesteads
+ East Waikalua
+ Omar Haikal Islamic Academy
+ Bakalar Air Force Base (historical)
+ Koshbakaly
+ Bagkalen
+ Gora Baikara
+ Sibankala
+ Mfumbaika
+ Mbakalungu
+ Chumbaika
+ Tombankala
+ Ntombankala School
+ Bakalabwa Pans
+ Khobai al Janhra
+ Holiday Inn Dubai Al Barsha
+ Novotel Dubai Al Barsha
+ Holiday Inn Dubai Al Barsha
+ Doubletree Res.Dubai-Al Barsha
+ Doubletree By Hilton Hotel and Apartments Dubai Al Barsha
+ Doubletree By Hilton Dubai Al Barsha Hotel and Res
+ Park Inn By Radisson Dubai Al Barsha
+ Ramee Rose Hotel Dubai Al Barsha
+ Aparthotel Adagio Premium Dubai Al Barsha
+ Ataikala
+ Selman Marrakech
+ Riad Ain Marrakech
+ Taj Palace Marrakech
+ Delano Marrakech
+ Pullman Marrakech Palmeraie Resort And Spa
+ Lalla Calipau Marrakech
+ Hotel Fashion Marrakech
+ Four Seasons Resort Marrakech
+ Adama Resort Marrakech
+ Pullman Marrakech Palmeraie Re
+ Ramada Resort Marrakech Douar Al Hana
+ Hotel Zahia Marrakech
+ Hotel Marrakech Le Tichka
+ Le Chems Marrakech
+ Beachcomber Royal Palm Marrakech
+ Residence Marrakech
+ Riad Hermes Marrakech
+ Riad La Lune De Marrakech
+ Hotel Marrakech Le Sangho Privilege
+ Tempoo Hotel Marrakech
+ Ag Hotel & Spa Marrakech
+ Palm Appart Club Marrakech
+ Hotel Ibis Moussafir Marrakech Palmeraie
+ Ibis Marrakech Gare Voyageurs
+ Marrakech Ryads Parc And Spa
+ Terra Mia Marrakech Riad
+ Residence Dar Lamia Marrakech
+ Pullman Marrakech Palmeraie Rs
+ Moussaf Marrakech Centre Gare
+ Tempoo Hotel Marrakech Adults Only
+ Sahara Palace Marrakech
+ Moroccan House Marrakech
+ El Andalouss And Spa Marrakech
+ Suite Novotel Marrakech Rs
+ Dar Catalina Marrakech Hotel Non Refundable Room
+ Marrakech Hotel
+ Oued Tammarrakech
+ Tammarrakech
+ Cercle de Marrakech-Banlieue
+ Marrakech-Tensift-Al Haouz
+ Koudia Marrakech
+ Hotel Tichka Salam Marrakech
+ L'Atlas Marrakech
+ Royal Mirage Deluxe Marrakech
+ Golden Tulip Farah Marrakech
+ Ryad Mogador Marrakech
+ Coralia Club Marrakech Palmariva
+ La Sultana Marrakech
+ Marrakech-Medina
+ Marrakech
+ Museum of Marrakech
+ Douar Marrakechiyinc
+ Ibis Marrakech Centre Gare
+ Golden Tulip Rawabi Marrakech
+ Murano Resort Marrakech
+ Marrakech Garden Hotel
+ Pullman Marrakech Palmerai Resort & Spa
+ The Pearl Marrakech
+ Palais Calipau Marrakech
+ Hostal Equity Point Marrakech
+ Sofitel Marrakech Lounge And Spa
+ Pullman Marrakech Hotel And Spa
+ Sofitel Marrakech Palais Imperial
+ Hotel Ibis Moussafir Marrakech Centre Gare
+ Red Hotel Marrakech
+ Riad Zenith Marrakech
+ Ksar Catalina Marrakech Hotel
+ Blue Sea Hotel Marrakech Ryads Parc & Spa
+ Bluebay Marrakech
+ Pullman Marrakech Palmeraie Resort & Spa Hotel
+ Sahara Palace Marrakech
+ Riad Litzy Marrakech
+ Sultana Hotel & Spa Marrakech
+ Albatros Club Marrakech
+ Hotel Sangho Club Marrakech
+ Suite Novotel Marrakech Hotel
+ Riad Utopia Suites & Spa Marrakech
+ Riad Fatinat Marrakech
+ Riad Dar El Aila Marrakech
+ Es Saadi And Casino De Marrakech
+ Dar Catalina Marrakech Hotel
+ Grace Marrakech
+ Marrakesh Apartments
+ Marrakesh Country Club
+ Koudiat Lmerrakechiyine
+ Sidi Mohammed el Marrakchi
+ Marrakesh
+ Marrakchien
+ Marrakchia
+ Marrakesh Menara Airport
+ Marrakesh Hua Hin Resort & Spa
+ Marrakesh Hua Hin Resort And Spa
+ Marrakesh Resort And Spa (Pool Suite)
+ Marrakesh Huahin Resort & Spa
+ Ibis Moussafir Marrakesh Centre Gare Hotel
+ Maerak-chi
+ Dar Hammou Ben Merrakchi
+ Lalla el Marakchia
+ Khrebet Marrakh
+ Sungai Maru Kechil
+ Marrache
+ Goth Marracha
+ Maramech Hill
+ Maramech Woods Nature Preserve
+ Oued Karakech
+ Samarra School
+ Jangal-e Marakeh Sar
*** /dev/null
--- b/contrib/pg_trgm/expected/pg_substring_trgm.out
***************
*** 0 ****
--- 1,2211 ----
+ CREATE TABLE test_trgm2(t text COLLATE "C");
+ \copy test_trgm2 from 'data/trgm2.data'
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ create index trgm_idx2 on test_trgm2 using gist (t gist_trgm_ops);
+ set enable_seqscan=off;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ drop index trgm_idx2;
+ create index trgm_idx2 on test_trgm2 using gin (t gin_trgm_ops);
+ set enable_seqscan=off;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ (56 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+ t | sml
+ ------------------------------+-----
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ (17 rows)
+
+ select set_substring_limit(0.5);
+ set_substring_limit
+ ---------------------
+ 0.5
+ (1 row)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ Bakal Batu | 0.571429
+ Zabaykalka | 0.571429
+ Zabaykalovskiy | 0.571429
+ (59 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ t | sml
+ ----------------------------------+----------
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ Kabakala | 0.583333
+ Bankalah | 0.5
+ Bankalan | 0.5
+ Bankalan | 0.5
+ Kamba-Kala | 0.5
+ Keur Samba Kala | 0.5
+ Nehalla Bankalah Reserved Forest | 0.5
+ Rokba Kala Sardoun | 0.5
+ (25 rows)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ t | sml
+ -------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ Bakal Batu | 0.571429
+ Zabaykalka | 0.571429
+ Zabaykalovskiy | 0.571429
+ (59 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+ t | sml
+ ----------------------------------+----------
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ Kabakala | 0.583333
+ Bankalah | 0.5
+ Bankalan | 0.5
+ Bankalan | 0.5
+ Kamba-Kala | 0.5
+ Keur Samba Kala | 0.5
+ Nehalla Bankalah Reserved Forest | 0.5
+ Rokba Kala Sardoun | 0.5
+ (25 rows)
+
+ select set_substring_limit(0.3);
+ set_substring_limit
+ ---------------------
+ 0.3
+ (1 row)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ t | sml
+ -----------------------------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ Bakal Batu | 0.571429
+ Zabaykalka | 0.571429
+ Zabaykalovskiy | 0.571429
+ Air Bakal-kecil | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal Dos | 0.444444
+ Bakal Julu | 0.444444
+ Bakal Khel | 0.444444
+ Bakal Lama | 0.444444
+ Bakal Tres | 0.444444
+ Bakal Uno | 0.444444
+ Daang Bakal | 0.444444
+ Daang Bakal | 0.444444
+ Desa Bakal | 0.444444
+ Eat Bakal | 0.444444
+ Gunung Bakal | 0.444444
+ Sidi Bakal | 0.444444
+ Stantsiya Bakal | 0.444444
+ Sungai Bakal | 0.444444
+ Talang Bakal | 0.444444
+ Uruk Bakal | 0.444444
+ Zaouia Oulad Bakal | 0.444444
+ Al Barkali | 0.428571
+ Aparthotel Adagio Premium Dubai Al Barsha | 0.428571
+ Baikal Business Centre | 0.428571
+ Bay of Backaland | 0.428571
+ Boikalakalawa Bay | 0.428571
+ Doubletree By Hilton Dubai Al Barsha Hotel and Res | 0.428571
+ Doubletree By Hilton Hotel and Apartments Dubai Al Barsha | 0.428571
+ Doubletree Res.Dubai-Al Barsha | 0.428571
+ Holiday Inn Dubai Al Barsha | 0.428571
+ Holiday Inn Dubai Al Barsha | 0.428571
+ Jabal Barkal | 0.428571
+ Novotel Dubai Al Barsha | 0.428571
+ Park Inn By Radisson Dubai Al Barsha | 0.428571
+ Ramee Rose Hotel Dubai Al Barsha | 0.428571
+ Waikalabubu Bay | 0.428571
+ Baikal | 0.4
+ Baikal | 0.4
+ Baikal Airfield | 0.4
+ Baikal Hotel Moscow | 0.4
+ Baikal Listvyanka Hotel | 0.4
+ Baikal Mountains | 0.4
+ Baikal Plaza | 0.4
+ Bajkal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal School | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Lake Baikal | 0.4
+ Mbay Bakala | 0.4
+ Oulad el Bakkal | 0.4
+ Sidi Mohammed Bakkal | 0.4
+ Bairkal | 0.363636
+ Bairkal Dhora | 0.363636
+ Bairkal Jabal | 0.363636
+ Batikal | 0.363636
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala Koupi | 0.333333
+ Bakalaale | 0.333333
+ Bakalabwa Pans | 0.333333
+ Bakalaeng | 0.333333
+ Bakalafoulou | 0.333333
+ Bakalalan Airport | 0.333333
+ Bakalam | 0.333333
+ Bakalambani | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan Barat | 0.333333
+ Bakalan Dua | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan River | 0.333333
+ Bakalan Tengah | 0.333333
+ Bakalan Wetan | 0.333333
+ Bakalan Wetan | 0.333333
+ Bakalang | 0.333333
+ Bakalao Asibi Point | 0.333333
+ Bakalao Point | 0.333333
+ Bakalar Air Force Base (historical) | 0.333333
+ Bakalar Lake | 0.333333
+ Bakalar Library | 0.333333
+ Bakalarr | 0.333333
+ Bakalauri | 0.333333
+ Bakalauri1 | 0.333333
+ Bakalauri2 | 0.333333
+ Bakalauri3 | 0.333333
+ Bakalauri4 | 0.333333
+ Bakalauri5 | 0.333333
+ Bakalauri6 | 0.333333
+ Bakalauri7 | 0.333333
+ Bakalauri8 | 0.333333
+ Bakalauri9 | 0.333333
+ Bakalawa | 0.333333
+ Bakalbhar | 0.333333
+ Bakalbuah | 0.333333
+ Bakalda | 0.333333
+ Bakalda | 0.333333
+ Bakaldalam | 0.333333
+ Bakaldalam | 0.333333
+ Bakaldinskoye | 0.333333
+ Bakaldovshchina | 0.333333
+ Bakaldukuh | 0.333333
+ Bakaldum | 0.333333
+ Bakaldy | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakaleko | 0.333333
+ Bakalerek | 0.333333
+ Bakaley | 0.333333
+ Bakaleyka | 0.333333
+ Bakalha | 0.333333
+ Bakali | 0.333333
+ Bakali | 0.333333
+ Bakali | 0.333333
+ Bakalia Char | 0.333333
+ Bakalica | 0.333333
+ Bakalica | 0.333333
+ Bakalinga | 0.333333
+ Bakalino | 0.333333
+ Bakalinskiy | 0.333333
+ Bakalinskiy | 0.333333
+ Bakalinskiy Leskhoz | 0.333333
+ Bakalinskiy Rayon | 0.333333
+ Bakalipur | 0.333333
+ Bakalite | 0.333333
+ Bakaljaya | 0.333333
+ Bakalka | 0.333333
+ Bakalka | 0.333333
+ Bakall | 0.333333
+ Bakalnica | 0.333333
+ Bakalod Island | 0.333333
+ Bakalongo | 0.333333
+ Bakaloolay | 0.333333
+ Bakalou | 0.333333
+ Bakalovina | 0.333333
+ Bakalovina | 0.333333
+ Bakalovka | 0.333333
+ Bakalovo | 0.333333
+ Bakalovo | 0.333333
+ Bakalovo | 0.333333
+ Bakalovskaya Ferma | 0.333333
+ Bakalpakebo | 0.333333
+ Bakalpokok | 0.333333
+ Bakalrejo | 0.333333
+ Bakalsen | 0.333333
+ Bakalshile | 0.333333
+ Bakaltua Bank | 0.333333
+ Bakalua | 0.333333
+ Bakalukalu | 0.333333
+ Bakalukalu Shan | 0.333333
+ Bakalukudu | 0.333333
+ Bakalum | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly TV Mast | 0.333333
+ Buur Bakale | 0.333333
+ Buur Bakaley | 0.333333
+ Columbus Bakalar Municipal Airport | 0.333333
+ Dakshin Bakalia | 0.333333
+ Danau Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalankrajan | 0.333333
+ Desa Bakalankrapyak | 0.333333
+ Desa Bakalanpule | 0.333333
+ Desa Bakalanrayung | 0.333333
+ Desa Bakalanwringinpitu | 0.333333
+ Desa Bakalrejo | 0.333333
+ Desa Bakalrejo | 0.333333
+ Desa Bakalrejo | 0.333333
+ Efrejtor Bakalovo | 0.333333
+ Efreytor-Bakalovo | 0.333333
+ Gora Bakalyadyr | 0.333333
+ Gory Bakaly | 0.333333
+ Gunung Bakalan | 0.333333
+ Gunung Bakalan | 0.333333
+ Ile Bakalibu | 0.333333
+ Kali Bakalan | 0.333333
+ Kali Bakalan | 0.333333
+ Kali Bakalan | 0.333333
+ Kampong Bakaladong | 0.333333
+ Khor Bakallii | 0.333333
+ Krajan Bakalan | 0.333333
+ Kusu-Bakali | 0.333333
+ Kwala Bakala | 0.333333
+ Ngao Bakala | 0.333333
+ Ngao Bakala | 0.333333
+ Ovrag Bakalda | 0.333333
+ Pematang Bakalpanang | 0.333333
+ Pematang Bakalpanjang | 0.333333
+ Pulau Bakalan | 0.333333
+ Pulau Bakalanpauno | 0.333333
+ Ragha Bakalzai | 0.333333
+ Rodnik Bakalybulak | 0.333333
+ Salu Bakalaeng | 0.333333
+ Selat Bakalan | 0.333333
+ Selat Bakalanpauno | 0.333333
+ Sidi Mohammed el Bakali | 0.333333
+ Sopka Bakaly | 0.333333
+ Sovkhoz Bakalinskiy | 0.333333
+ Sungai Bakala | 0.333333
+ Sungai Bakaladiyan | 0.333333
+ Tanjung Bakalinga | 0.333333
+ Teluk Bakalan | 0.333333
+ Teluk Bakalang | 0.333333
+ Tubu Bakalekuk | 0.333333
+ Tukad Bakalan | 0.333333
+ Urochishche Bakalarnyn-Ayasy | 0.333333
+ Urochishche Bakaldikha | 0.333333
+ Urochishche Bakalovo | 0.333333
+ Urochishche Bakaly | 0.333333
+ Bakkalmal | 0.307692
+ Alue Bakkala | 0.3
+ Azib el Bakkali | 0.3
+ Ba Kaliin | 0.3
+ Bagkalen | 0.3
+ Bahkalleh | 0.3
+ Baikalakko | 0.3
+ Baikalovo | 0.3
+ Baikaluobbal | 0.3
+ Bakkala Cemetery | 0.3
+ Bakkalale | 0.3
+ Bakkalegskardet | 0.3
+ Bakkalia | 0.3
+ Bakkalia | 0.3
+ Bakkalykkja | 0.3
+ Bankala | 0.3
+ Bankala Hill | 0.3
+ Bankalah | 0.3
+ Bankalan | 0.3
+ Bankalan | 0.3
+ Bankali | 0.3
+ Bankali | 0.3
+ Bankali | 0.3
+ Bankalol | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala Park | 0.3
+ Barkala Rao | 0.3
+ Barkala Reserved Forest | 0.3
+ Barkalabava | 0.3
+ Barkaladja Pool | 0.3
+ Barkalare | 0.3
+ Barkald | 0.3
+ Barkald | 0.3
+ Barkald | 0.3
+ Barkald stasjon | 0.3
+ Barkalden | 0.3
+ Barkaldfossen | 0.3
+ Barkaldvola | 0.3
+ Barkale | 0.3
+ Barkale | 0.3
+ Barkaleh | 0.3
+ Barkaleitet | 0.3
+ Barkali | 0.3
+ Barkali | 0.3
+ Barkallou | 0.3
+ Barkalne | 0.3
+ Barkalova | 0.3
+ Barkalovka | 0.3
+ Barkalow Hollow | 0.3
+ Baskalino | 0.3
+ Baskaltsi | 0.3
+ Baukala | 0.3
+ Bavkalasis | 0.3
+ Bawkalut | 0.3
+ Bawkalut Chaung | 0.3
+ Bikal | 0.3
+ Bikal | 0.3
+ Bikal | 0.3
+ Clifton T Barkalow Elementary School | 0.3
+ Gora Barkalova | 0.3
+ Gora Barkalyu | 0.3
+ Khrebet Batkali | 0.3
+ Kordon Barkalo | 0.3
+ Nehalla Bankalah Reserved Forest | 0.3
+ Ras Barkallah | 0.3
+ Sopka Barkaleptskaya | 0.3
+ Urochishche Batkali | 0.3
+ (582 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ t | sml
+ ----------------------------------+----------
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ Kabakala | 0.583333
+ Bankalah | 0.5
+ Bankalan | 0.5
+ Bankalan | 0.5
+ Kamba-Kala | 0.5
+ Keur Samba Kala | 0.5
+ Nehalla Bankalah Reserved Forest | 0.5
+ Rokba Kala Sardoun | 0.5
+ Kabikala | 0.461538
+ Buyamba-Kalala | 0.454545
+ Mwalaba-Kalamba | 0.454545
+ Bakala Koupi | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal School | 0.4
+ Bankali | 0.4
+ Bankali | 0.4
+ Bankali | 0.4
+ Bankalol | 0.4
+ Jabba Kalai | 0.4
+ Kanampumba-Kalawa | 0.4
+ Purba Kalaujan | 0.4
+ Tumba-Kalamba | 0.4
+ Daba Kalharereh | 0.363636
+ Gagaba Kalo | 0.363636
+ Jaba Kalle | 0.363636
+ Dabakala | 0.333333
+ Dalabakala | 0.333333
+ Kambakala | 0.333333
+ Ker Samba Kalla | 0.333333
+ Fayzabadkala | 0.307692
+ Gora Fayzabadkala | 0.307692
+ Guba Kalgalaksha | 0.307692
+ Habakkala | 0.307692
+ Kaikalahun Indian Reserve 25 | 0.307692
+ Kaikalapettai | 0.307692
+ Alue Bakkala | 0.3
+ Ambadikala | 0.3
+ Ambadikala | 0.3
+ Ambakala Wewa | 0.3
+ Ataikala | 0.3
+ Ba Kaliin | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakkala Cemetery | 0.3
+ Bambakala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala Park | 0.3
+ Barkala Rao | 0.3
+ Barkala Reserved Forest | 0.3
+ Baukala | 0.3
+ Beikala | 0.3
+ Beikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala Madila | 0.3
+ Bomba-Kalende | 0.3
+ Bonagbakala | 0.3
+ Boyagbakala | 0.3
+ Bugor Arba-Kalgan | 0.3
+ Bumba-Kaloki | 0.3
+ Bumba-Kalumba | 0.3
+ Darreh Pumba Kal | 0.3
+ Demba Kali | 0.3
+ Embatkala | 0.3
+ Gereba Kaler | 0.3
+ Golba Kalo | 0.3
+ Goth Soba Kaloi | 0.3
+ Guba Kaldo | 0.3
+ Guba Kalita | 0.3
+ Gulba Kalle | 0.3
+ Haikala | 0.3
+ Haikala | 0.3
+ Kali Bakalan | 0.3
+ Kali Bakalan | 0.3
+ Kali Bakalan | 0.3
+ Kali Purbakala | 0.3
+ Kalibakal | 0.3
+ Kalibakalako | 0.3
+ Kalimundubakalan | 0.3
+ Kamba-Kalele | 0.3
+ Kimbakala | 0.3
+ Kombakala | 0.3
+ Kwala Bakala | 0.3
+ Laikala | 0.3
+ Maikala Range | 0.3
+ Mambakala | 0.3
+ Matamba-Kalenga | 0.3
+ Matamba-Kalenge | 0.3
+ Mbay Bakala | 0.3
+ Mount Tohebakala | 0.3
+ Naikala | 0.3
+ Ngao Bakala | 0.3
+ Ngao Bakala | 0.3
+ Purba Kalmegha | 0.3
+ Purba Kalmegha | 0.3
+ Sungai Bakala | 0.3
+ Tagobikala | 0.3
+ Tanjung Batikala | 0.3
+ Tombakala | 0.3
+ Tsibakala | 0.3
+ Tumba-Kalumba | 0.3
+ Tumba-Kalunga | 0.3
+ Waikala | 0.3
+ (161 rows)
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ t | sml
+ -----------------------------------------------------------+----------
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Baykal | 1
+ Boloto Baykal | 1
+ Boloto Baykal | 1
+ Boloto Malyy Baykal | 1
+ Kolkhoz Krasnyy Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Ozero Baykal | 1
+ Polevoy Stan Baykal | 1
+ Port Baykal | 1
+ Prud Novyy Baykal | 1
+ Sanatoriy Baykal | 1
+ Stantsiya Baykal | 1
+ Zaliv Baykal | 1
+ Baykalikha | 0.857143
+ Baykalo-Amurskaya Zheleznaya Doroga | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovo | 0.857143
+ Baykalovsk | 0.857143
+ Baykalovskiy | 0.857143
+ Baykalovskiy Rayon | 0.857143
+ Baykalsko | 0.857143
+ Maloye Baykalovo | 0.857143
+ Zabaykal | 0.714286
+ Bakal Batu | 0.571429
+ Zabaykalka | 0.571429
+ Zabaykalovskiy | 0.571429
+ Air Bakal-kecil | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal | 0.444444
+ Bakal Dos | 0.444444
+ Bakal Julu | 0.444444
+ Bakal Khel | 0.444444
+ Bakal Lama | 0.444444
+ Bakal Tres | 0.444444
+ Bakal Uno | 0.444444
+ Daang Bakal | 0.444444
+ Daang Bakal | 0.444444
+ Desa Bakal | 0.444444
+ Eat Bakal | 0.444444
+ Gunung Bakal | 0.444444
+ Sidi Bakal | 0.444444
+ Stantsiya Bakal | 0.444444
+ Sungai Bakal | 0.444444
+ Talang Bakal | 0.444444
+ Uruk Bakal | 0.444444
+ Zaouia Oulad Bakal | 0.444444
+ Al Barkali | 0.428571
+ Aparthotel Adagio Premium Dubai Al Barsha | 0.428571
+ Baikal Business Centre | 0.428571
+ Bay of Backaland | 0.428571
+ Boikalakalawa Bay | 0.428571
+ Doubletree By Hilton Dubai Al Barsha Hotel and Res | 0.428571
+ Doubletree By Hilton Hotel and Apartments Dubai Al Barsha | 0.428571
+ Doubletree Res.Dubai-Al Barsha | 0.428571
+ Holiday Inn Dubai Al Barsha | 0.428571
+ Holiday Inn Dubai Al Barsha | 0.428571
+ Jabal Barkal | 0.428571
+ Novotel Dubai Al Barsha | 0.428571
+ Park Inn By Radisson Dubai Al Barsha | 0.428571
+ Ramee Rose Hotel Dubai Al Barsha | 0.428571
+ Waikalabubu Bay | 0.428571
+ Baikal | 0.4
+ Baikal | 0.4
+ Baikal Airfield | 0.4
+ Baikal Hotel Moscow | 0.4
+ Baikal Listvyanka Hotel | 0.4
+ Baikal Mountains | 0.4
+ Baikal Plaza | 0.4
+ Bajkal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal School | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Barkal | 0.4
+ Lake Baikal | 0.4
+ Mbay Bakala | 0.4
+ Oulad el Bakkal | 0.4
+ Sidi Mohammed Bakkal | 0.4
+ Bairkal | 0.363636
+ Bairkal Dhora | 0.363636
+ Bairkal Jabal | 0.363636
+ Batikal | 0.363636
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala | 0.333333
+ Bakala Koupi | 0.333333
+ Bakalaale | 0.333333
+ Bakalabwa Pans | 0.333333
+ Bakalaeng | 0.333333
+ Bakalafoulou | 0.333333
+ Bakalalan Airport | 0.333333
+ Bakalam | 0.333333
+ Bakalambani | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan | 0.333333
+ Bakalan Barat | 0.333333
+ Bakalan Dua | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kidul | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Kulon | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan Lor | 0.333333
+ Bakalan River | 0.333333
+ Bakalan Tengah | 0.333333
+ Bakalan Wetan | 0.333333
+ Bakalan Wetan | 0.333333
+ Bakalang | 0.333333
+ Bakalao Asibi Point | 0.333333
+ Bakalao Point | 0.333333
+ Bakalar Air Force Base (historical) | 0.333333
+ Bakalar Lake | 0.333333
+ Bakalar Library | 0.333333
+ Bakalarr | 0.333333
+ Bakalauri | 0.333333
+ Bakalauri1 | 0.333333
+ Bakalauri2 | 0.333333
+ Bakalauri3 | 0.333333
+ Bakalauri4 | 0.333333
+ Bakalauri5 | 0.333333
+ Bakalauri6 | 0.333333
+ Bakalauri7 | 0.333333
+ Bakalauri8 | 0.333333
+ Bakalauri9 | 0.333333
+ Bakalawa | 0.333333
+ Bakalbhar | 0.333333
+ Bakalbuah | 0.333333
+ Bakalda | 0.333333
+ Bakalda | 0.333333
+ Bakaldalam | 0.333333
+ Bakaldalam | 0.333333
+ Bakaldinskoye | 0.333333
+ Bakaldovshchina | 0.333333
+ Bakaldukuh | 0.333333
+ Bakaldum | 0.333333
+ Bakaldy | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakale | 0.333333
+ Bakaleko | 0.333333
+ Bakalerek | 0.333333
+ Bakaley | 0.333333
+ Bakaleyka | 0.333333
+ Bakalha | 0.333333
+ Bakali | 0.333333
+ Bakali | 0.333333
+ Bakali | 0.333333
+ Bakalia Char | 0.333333
+ Bakalica | 0.333333
+ Bakalica | 0.333333
+ Bakalinga | 0.333333
+ Bakalino | 0.333333
+ Bakalinskiy | 0.333333
+ Bakalinskiy | 0.333333
+ Bakalinskiy Leskhoz | 0.333333
+ Bakalinskiy Rayon | 0.333333
+ Bakalipur | 0.333333
+ Bakalite | 0.333333
+ Bakaljaya | 0.333333
+ Bakalka | 0.333333
+ Bakalka | 0.333333
+ Bakall | 0.333333
+ Bakalnica | 0.333333
+ Bakalod Island | 0.333333
+ Bakalongo | 0.333333
+ Bakaloolay | 0.333333
+ Bakalou | 0.333333
+ Bakalovina | 0.333333
+ Bakalovina | 0.333333
+ Bakalovka | 0.333333
+ Bakalovo | 0.333333
+ Bakalovo | 0.333333
+ Bakalovo | 0.333333
+ Bakalovskaya Ferma | 0.333333
+ Bakalpakebo | 0.333333
+ Bakalpokok | 0.333333
+ Bakalrejo | 0.333333
+ Bakalsen | 0.333333
+ Bakalshile | 0.333333
+ Bakaltua Bank | 0.333333
+ Bakalua | 0.333333
+ Bakalukalu | 0.333333
+ Bakalukalu Shan | 0.333333
+ Bakalukudu | 0.333333
+ Bakalum | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly | 0.333333
+ Bakaly TV Mast | 0.333333
+ Buur Bakale | 0.333333
+ Buur Bakaley | 0.333333
+ Columbus Bakalar Municipal Airport | 0.333333
+ Dakshin Bakalia | 0.333333
+ Danau Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalan | 0.333333
+ Desa Bakalankrajan | 0.333333
+ Desa Bakalankrapyak | 0.333333
+ Desa Bakalanpule | 0.333333
+ Desa Bakalanrayung | 0.333333
+ Desa Bakalanwringinpitu | 0.333333
+ Desa Bakalrejo | 0.333333
+ Desa Bakalrejo | 0.333333
+ Desa Bakalrejo | 0.333333
+ Efrejtor Bakalovo | 0.333333
+ Efreytor-Bakalovo | 0.333333
+ Gora Bakalyadyr | 0.333333
+ Gory Bakaly | 0.333333
+ Gunung Bakalan | 0.333333
+ Gunung Bakalan | 0.333333
+ Ile Bakalibu | 0.333333
+ Kali Bakalan | 0.333333
+ Kali Bakalan | 0.333333
+ Kali Bakalan | 0.333333
+ Kampong Bakaladong | 0.333333
+ Khor Bakallii | 0.333333
+ Krajan Bakalan | 0.333333
+ Kusu-Bakali | 0.333333
+ Kwala Bakala | 0.333333
+ Ngao Bakala | 0.333333
+ Ngao Bakala | 0.333333
+ Ovrag Bakalda | 0.333333
+ Pematang Bakalpanang | 0.333333
+ Pematang Bakalpanjang | 0.333333
+ Pulau Bakalan | 0.333333
+ Pulau Bakalanpauno | 0.333333
+ Ragha Bakalzai | 0.333333
+ Rodnik Bakalybulak | 0.333333
+ Salu Bakalaeng | 0.333333
+ Selat Bakalan | 0.333333
+ Selat Bakalanpauno | 0.333333
+ Sidi Mohammed el Bakali | 0.333333
+ Sopka Bakaly | 0.333333
+ Sovkhoz Bakalinskiy | 0.333333
+ Sungai Bakala | 0.333333
+ Sungai Bakaladiyan | 0.333333
+ Tanjung Bakalinga | 0.333333
+ Teluk Bakalan | 0.333333
+ Teluk Bakalang | 0.333333
+ Tubu Bakalekuk | 0.333333
+ Tukad Bakalan | 0.333333
+ Urochishche Bakalarnyn-Ayasy | 0.333333
+ Urochishche Bakaldikha | 0.333333
+ Urochishche Bakalovo | 0.333333
+ Urochishche Bakaly | 0.333333
+ Bakkalmal | 0.307692
+ Alue Bakkala | 0.3
+ Azib el Bakkali | 0.3
+ Ba Kaliin | 0.3
+ Bagkalen | 0.3
+ Bahkalleh | 0.3
+ Baikalakko | 0.3
+ Baikalovo | 0.3
+ Baikaluobbal | 0.3
+ Bakkala Cemetery | 0.3
+ Bakkalale | 0.3
+ Bakkalegskardet | 0.3
+ Bakkalia | 0.3
+ Bakkalia | 0.3
+ Bakkalykkja | 0.3
+ Bankala | 0.3
+ Bankala Hill | 0.3
+ Bankalah | 0.3
+ Bankalan | 0.3
+ Bankalan | 0.3
+ Bankali | 0.3
+ Bankali | 0.3
+ Bankali | 0.3
+ Bankalol | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala Park | 0.3
+ Barkala Rao | 0.3
+ Barkala Reserved Forest | 0.3
+ Barkalabava | 0.3
+ Barkaladja Pool | 0.3
+ Barkalare | 0.3
+ Barkald | 0.3
+ Barkald | 0.3
+ Barkald | 0.3
+ Barkald stasjon | 0.3
+ Barkalden | 0.3
+ Barkaldfossen | 0.3
+ Barkaldvola | 0.3
+ Barkale | 0.3
+ Barkale | 0.3
+ Barkaleh | 0.3
+ Barkaleitet | 0.3
+ Barkali | 0.3
+ Barkali | 0.3
+ Barkallou | 0.3
+ Barkalne | 0.3
+ Barkalova | 0.3
+ Barkalovka | 0.3
+ Barkalow Hollow | 0.3
+ Baskalino | 0.3
+ Baskaltsi | 0.3
+ Baukala | 0.3
+ Bavkalasis | 0.3
+ Bawkalut | 0.3
+ Bawkalut Chaung | 0.3
+ Bikal | 0.3
+ Bikal | 0.3
+ Bikal | 0.3
+ Clifton T Barkalow Elementary School | 0.3
+ Gora Barkalova | 0.3
+ Gora Barkalyu | 0.3
+ Khrebet Batkali | 0.3
+ Kordon Barkalo | 0.3
+ Nehalla Bankalah Reserved Forest | 0.3
+ Ras Barkallah | 0.3
+ Sopka Barkaleptskaya | 0.3
+ Urochishche Batkali | 0.3
+ (582 rows)
+
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+ t | sml
+ ----------------------------------+----------
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ Kabankala | 1
+ City of Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan | 0.9
+ Kabankalan City Public Plaza | 0.9
+ Abankala | 0.7
+ Bankala | 0.6
+ Bankala Hill | 0.6
+ Gbonkobankala | 0.6
+ Ntombankala School | 0.6
+ Sibankala | 0.6
+ Tombankala | 0.6
+ Kabakala | 0.583333
+ Bankalah | 0.5
+ Bankalan | 0.5
+ Bankalan | 0.5
+ Kamba-Kala | 0.5
+ Keur Samba Kala | 0.5
+ Nehalla Bankalah Reserved Forest | 0.5
+ Rokba Kala Sardoun | 0.5
+ Kabikala | 0.461538
+ Buyamba-Kalala | 0.454545
+ Mwalaba-Kalamba | 0.454545
+ Bakala Koupi | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal | 0.4
+ Bankal School | 0.4
+ Bankali | 0.4
+ Bankali | 0.4
+ Bankali | 0.4
+ Bankalol | 0.4
+ Jabba Kalai | 0.4
+ Kanampumba-Kalawa | 0.4
+ Purba Kalaujan | 0.4
+ Tumba-Kalamba | 0.4
+ Daba Kalharereh | 0.363636
+ Gagaba Kalo | 0.363636
+ Jaba Kalle | 0.363636
+ Dabakala | 0.333333
+ Dalabakala | 0.333333
+ Kambakala | 0.333333
+ Ker Samba Kalla | 0.333333
+ Fayzabadkala | 0.307692
+ Gora Fayzabadkala | 0.307692
+ Guba Kalgalaksha | 0.307692
+ Habakkala | 0.307692
+ Kaikalahun Indian Reserve 25 | 0.307692
+ Kaikalapettai | 0.307692
+ Alue Bakkala | 0.3
+ Ambadikala | 0.3
+ Ambadikala | 0.3
+ Ambakala Wewa | 0.3
+ Ataikala | 0.3
+ Ba Kaliin | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakala | 0.3
+ Bakkala Cemetery | 0.3
+ Bambakala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala | 0.3
+ Barkala Park | 0.3
+ Barkala Rao | 0.3
+ Barkala Reserved Forest | 0.3
+ Baukala | 0.3
+ Beikala | 0.3
+ Beikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala | 0.3
+ Bikala Madila | 0.3
+ Bomba-Kalende | 0.3
+ Bonagbakala | 0.3
+ Boyagbakala | 0.3
+ Bugor Arba-Kalgan | 0.3
+ Bumba-Kaloki | 0.3
+ Bumba-Kalumba | 0.3
+ Darreh Pumba Kal | 0.3
+ Demba Kali | 0.3
+ Embatkala | 0.3
+ Gereba Kaler | 0.3
+ Golba Kalo | 0.3
+ Goth Soba Kaloi | 0.3
+ Guba Kaldo | 0.3
+ Guba Kalita | 0.3
+ Gulba Kalle | 0.3
+ Haikala | 0.3
+ Haikala | 0.3
+ Kali Bakalan | 0.3
+ Kali Bakalan | 0.3
+ Kali Bakalan | 0.3
+ Kali Purbakala | 0.3
+ Kalibakal | 0.3
+ Kalibakalako | 0.3
+ Kalimundubakalan | 0.3
+ Kamba-Kalele | 0.3
+ Kimbakala | 0.3
+ Kombakala | 0.3
+ Kwala Bakala | 0.3
+ Laikala | 0.3
+ Maikala Range | 0.3
+ Mambakala | 0.3
+ Matamba-Kalenga | 0.3
+ Matamba-Kalenge | 0.3
+ Mbay Bakala | 0.3
+ Mount Tohebakala | 0.3
+ Naikala | 0.3
+ Ngao Bakala | 0.3
+ Ngao Bakala | 0.3
+ Purba Kalmegha | 0.3
+ Purba Kalmegha | 0.3
+ Sungai Bakala | 0.3
+ Tagobikala | 0.3
+ Tanjung Batikala | 0.3
+ Tombakala | 0.3
+ Tsibakala | 0.3
+ Tumba-Kalumba | 0.3
+ Tumba-Kalunga | 0.3
+ Waikala | 0.3
+ (161 rows)
+
*** a/contrib/pg_trgm/expected/pg_trgm.out
--- b/contrib/pg_trgm/expected/pg_trgm.out
***************
*** 59,65 **** select similarity('---', '####---');
0
(1 row)
! CREATE TABLE test_trgm(t text);
\copy test_trgm from 'data/trgm.data'
select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
t | sml
--- 59,65 ----
0
(1 row)
! CREATE TABLE test_trgm(t text COLLATE "C");
\copy test_trgm from 'data/trgm.data'
select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
t | sml
***************
*** 3467,3473 **** select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu198
qwertyu0988 | 0.333333
(1 row)
! create table test2(t text);
insert into test2 values ('abcdef');
insert into test2 values ('quark');
insert into test2 values (' z foo bar');
--- 3467,3473 ----
qwertyu0988 | 0.333333
(1 row)
! create table test2(t text COLLATE "C");
insert into test2 values ('abcdef');
insert into test2 values ('quark');
insert into test2 values (' z foo bar');
*** /dev/null
--- b/contrib/pg_trgm/pg_trgm--1.2--1.3.sql
***************
*** 0 ****
--- 1,53 ----
+ /* contrib/pg_trgm/pg_trgm--1.2--1.3.sql */
+
+ -- complain if script is sourced in psql, rather than via ALTER EXTENSION
+ \echo Use "ALTER EXTENSION pg_trgm UPDATE TO '1.3'" to load this file. \quit
+
+ CREATE FUNCTION set_substring_limit(float4)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT VOLATILE;
+
+ CREATE FUNCTION show_substring_limit()
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE;
+
+ CREATE FUNCTION substring_similarity(text,text)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE FUNCTION substring_similarity_op(text,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE; -- stable because depends on trgm_substring_limit
+
+ CREATE FUNCTION substring_similarity_commutator_op(text,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE; -- stable because depends on trgm_substring_limit
+
+ CREATE OPERATOR <% (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = substring_similarity_op,
+ COMMUTATOR = '%>',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OPERATOR %> (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = substring_similarity_commutator_op,
+ COMMUTATOR = '<%',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
+ OPERATOR 7 %> (text, text);
+
+ ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ OPERATOR 7 %> (text, text);
*** a/contrib/pg_trgm/pg_trgm--1.2.sql
--- /dev/null
***************
*** 1,188 ****
- /* contrib/pg_trgm/pg_trgm--1.2.sql */
-
- -- complain if script is sourced in psql, rather than via CREATE EXTENSION
- \echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit
-
- CREATE FUNCTION set_limit(float4)
- RETURNS float4
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT VOLATILE;
-
- CREATE FUNCTION show_limit()
- RETURNS float4
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT STABLE;
-
- CREATE FUNCTION show_trgm(text)
- RETURNS _text
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE FUNCTION similarity(text,text)
- RETURNS float4
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE FUNCTION similarity_op(text,text)
- RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT STABLE; -- stable because depends on trgm_limit
-
- CREATE OPERATOR % (
- LEFTARG = text,
- RIGHTARG = text,
- PROCEDURE = similarity_op,
- COMMUTATOR = '%',
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE FUNCTION similarity_dist(text,text)
- RETURNS float4
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR <-> (
- LEFTARG = text,
- RIGHTARG = text,
- PROCEDURE = similarity_dist,
- COMMUTATOR = '<->'
- );
-
- -- gist key
- CREATE FUNCTION gtrgm_in(cstring)
- RETURNS gtrgm
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE FUNCTION gtrgm_out(gtrgm)
- RETURNS cstring
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE TYPE gtrgm (
- INTERNALLENGTH = -1,
- INPUT = gtrgm_in,
- OUTPUT = gtrgm_out
- );
-
- -- support functions for gist
- CREATE FUNCTION gtrgm_consistent(internal,text,int,oid,internal)
- RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_distance(internal,text,int,oid)
- RETURNS float8
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_compress(internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_decompress(internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_penalty(internal,internal,internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_picksplit(internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_union(bytea, internal)
- RETURNS _int4
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gtrgm_same(gtrgm, gtrgm, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- -- create the operator class for gist
- CREATE OPERATOR CLASS gist_trgm_ops
- FOR TYPE text USING gist
- AS
- OPERATOR 1 % (text, text),
- FUNCTION 1 gtrgm_consistent (internal, text, int, oid, internal),
- FUNCTION 2 gtrgm_union (bytea, internal),
- FUNCTION 3 gtrgm_compress (internal),
- FUNCTION 4 gtrgm_decompress (internal),
- FUNCTION 5 gtrgm_penalty (internal, internal, internal),
- FUNCTION 6 gtrgm_picksplit (internal, internal),
- FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal),
- STORAGE gtrgm;
-
- -- Add operators and support functions that are new in 9.1. We do it like
- -- this, leaving them "loose" in the operator family rather than bound into
- -- the gist_trgm_ops opclass, because that's the only state that can be
- -- reproduced during an upgrade from 9.0 (see pg_trgm--unpackaged--1.0.sql).
-
- ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
- OPERATOR 2 <-> (text, text) FOR ORDER BY pg_catalog.float_ops,
- OPERATOR 3 pg_catalog.~~ (text, text),
- OPERATOR 4 pg_catalog.~~* (text, text),
- FUNCTION 8 (text, text) gtrgm_distance (internal, text, int, oid);
-
- -- Add operators that are new in 9.3.
-
- ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
- OPERATOR 5 pg_catalog.~ (text, text),
- OPERATOR 6 pg_catalog.~* (text, text);
-
- -- support functions for gin
- CREATE FUNCTION gin_extract_value_trgm(text, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal, internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE FUNCTION gin_trgm_consistent(internal, int2, text, int4, internal, internal, internal, internal)
- RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- -- create the operator class for gin
- CREATE OPERATOR CLASS gin_trgm_ops
- FOR TYPE text USING gin
- AS
- OPERATOR 1 % (text, text),
- FUNCTION 1 btint4cmp (int4, int4),
- FUNCTION 2 gin_extract_value_trgm (text, internal),
- FUNCTION 3 gin_extract_query_trgm (text, internal, int2, internal, internal, internal, internal),
- FUNCTION 4 gin_trgm_consistent (internal, int2, text, int4, internal, internal, internal, internal),
- STORAGE int4;
-
- -- Add operators that are new in 9.1.
-
- ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
- OPERATOR 3 pg_catalog.~~ (text, text),
- OPERATOR 4 pg_catalog.~~* (text, text);
-
- -- Add operators that are new in 9.3.
-
- ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
- OPERATOR 5 pg_catalog.~ (text, text),
- OPERATOR 6 pg_catalog.~* (text, text);
-
- -- Add functions that are new in 9.6 (pg_trgm 1.2).
-
- CREATE FUNCTION gin_trgm_triconsistent(internal, int2, text, int4, internal, internal, internal)
- RETURNS "char"
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
- FUNCTION 6 (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal);
--- 0 ----
*** /dev/null
--- b/contrib/pg_trgm/pg_trgm--1.3.sql
***************
*** 0 ****
--- 1,241 ----
+ /* contrib/pg_trgm/pg_trgm--1.3.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit
+
+ CREATE FUNCTION set_limit(float4)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT VOLATILE;
+
+ CREATE FUNCTION show_limit()
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE;
+
+ CREATE FUNCTION show_trgm(text)
+ RETURNS _text
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE FUNCTION similarity(text,text)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE FUNCTION similarity_op(text,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE; -- stable because depends on trgm_limit
+
+ CREATE OPERATOR % (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = similarity_op,
+ COMMUTATOR = '%',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE FUNCTION set_substring_limit(float4)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT VOLATILE;
+
+ CREATE FUNCTION show_substring_limit()
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE;
+
+ CREATE FUNCTION substring_similarity(text,text)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE FUNCTION substring_similarity_op(text,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE; -- stable because depends on trgm_substring_limit
+
+ CREATE FUNCTION substring_similarity_commutator_op(text,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE; -- stable because depends on trgm_substring_limit
+
+ CREATE OPERATOR <% (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = substring_similarity_op,
+ COMMUTATOR = '%>',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OPERATOR %> (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = substring_similarity_commutator_op,
+ COMMUTATOR = '<%',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE FUNCTION similarity_dist(text,text)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR <-> (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = similarity_dist,
+ COMMUTATOR = '<->'
+ );
+
+ -- gist key
+ CREATE FUNCTION gtrgm_in(cstring)
+ RETURNS gtrgm
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE FUNCTION gtrgm_out(gtrgm)
+ RETURNS cstring
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE TYPE gtrgm (
+ INTERNALLENGTH = -1,
+ INPUT = gtrgm_in,
+ OUTPUT = gtrgm_out
+ );
+
+ -- support functions for gist
+ CREATE FUNCTION gtrgm_consistent(internal,text,int,oid,internal)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_distance(internal,text,int,oid)
+ RETURNS float8
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_compress(internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_decompress(internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_penalty(internal,internal,internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_picksplit(internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_union(bytea, internal)
+ RETURNS _int4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gtrgm_same(gtrgm, gtrgm, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ -- create the operator class for gist
+ CREATE OPERATOR CLASS gist_trgm_ops
+ FOR TYPE text USING gist
+ AS
+ OPERATOR 1 % (text, text),
+ FUNCTION 1 gtrgm_consistent (internal, text, int, oid, internal),
+ FUNCTION 2 gtrgm_union (bytea, internal),
+ FUNCTION 3 gtrgm_compress (internal),
+ FUNCTION 4 gtrgm_decompress (internal),
+ FUNCTION 5 gtrgm_penalty (internal, internal, internal),
+ FUNCTION 6 gtrgm_picksplit (internal, internal),
+ FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal),
+ STORAGE gtrgm;
+
+ -- Add operators and support functions that are new in 9.1. We do it like
+ -- this, leaving them "loose" in the operator family rather than bound into
+ -- the gist_trgm_ops opclass, because that's the only state that can be
+ -- reproduced during an upgrade from 9.0 (see pg_trgm--unpackaged--1.0.sql).
+
+ ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
+ OPERATOR 2 <-> (text, text) FOR ORDER BY pg_catalog.float_ops,
+ OPERATOR 3 pg_catalog.~~ (text, text),
+ OPERATOR 4 pg_catalog.~~* (text, text),
+ FUNCTION 8 (text, text) gtrgm_distance (internal, text, int, oid);
+
+ -- Add operators that are new in 9.3.
+
+ ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
+ OPERATOR 5 pg_catalog.~ (text, text),
+ OPERATOR 6 pg_catalog.~* (text, text);
+
+ -- Add operators that are new in 9.6 (pg_trgm 1.3).
+
+ ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
+ OPERATOR 7 %> (text, text);
+
+ -- support functions for gin
+ CREATE FUNCTION gin_extract_value_trgm(text, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal, internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE FUNCTION gin_trgm_consistent(internal, int2, text, int4, internal, internal, internal, internal)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ -- create the operator class for gin
+ CREATE OPERATOR CLASS gin_trgm_ops
+ FOR TYPE text USING gin
+ AS
+ OPERATOR 1 % (text, text),
+ FUNCTION 1 btint4cmp (int4, int4),
+ FUNCTION 2 gin_extract_value_trgm (text, internal),
+ FUNCTION 3 gin_extract_query_trgm (text, internal, int2, internal, internal, internal, internal),
+ FUNCTION 4 gin_trgm_consistent (internal, int2, text, int4, internal, internal, internal, internal),
+ STORAGE int4;
+
+ -- Add operators that are new in 9.1.
+
+ ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ OPERATOR 3 pg_catalog.~~ (text, text),
+ OPERATOR 4 pg_catalog.~~* (text, text);
+
+ -- Add operators that are new in 9.3.
+
+ ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ OPERATOR 5 pg_catalog.~ (text, text),
+ OPERATOR 6 pg_catalog.~* (text, text);
+
+ -- Add functions that are new in 9.6 (pg_trgm 1.2).
+
+ CREATE FUNCTION gin_trgm_triconsistent(internal, int2, text, int4, internal, internal, internal)
+ RETURNS "char"
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ FUNCTION 6 (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal);
+
+ -- Add operators that are new in 9.6 (pg_trgm 1.3).
+
+ ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ OPERATOR 7 %> (text, text);
*** a/contrib/pg_trgm/pg_trgm.control
--- b/contrib/pg_trgm/pg_trgm.control
***************
*** 1,5 ****
# pg_trgm extension
comment = 'text similarity measurement and index searching based on trigrams'
! default_version = '1.2'
module_pathname = '$libdir/pg_trgm'
relocatable = true
--- 1,5 ----
# pg_trgm extension
comment = 'text similarity measurement and index searching based on trigrams'
! default_version = '1.3'
module_pathname = '$libdir/pg_trgm'
relocatable = true
*** /dev/null
--- b/contrib/pg_trgm/sql/pg_substring_trgm.sql
***************
*** 0 ****
--- 1,37 ----
+ CREATE TABLE test_trgm2(t text COLLATE "C");
+
+ \copy test_trgm2 from 'data/trgm2.data'
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+
+ create index trgm_idx2 on test_trgm2 using gist (t gist_trgm_ops);
+ set enable_seqscan=off;
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+
+ drop index trgm_idx2;
+ create index trgm_idx2 on test_trgm2 using gin (t gin_trgm_ops);
+ set enable_seqscan=off;
+
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+
+ select set_substring_limit(0.5);
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
+
+ select set_substring_limit(0.3);
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where 'Baykal' <% t order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where 'Kabankala' <% t order by sml desc, t;
+ select t,substring_similarity('Baykal',t) as sml from test_trgm2 where t %> 'Baykal' order by sml desc, t;
+ select t,substring_similarity('Kabankala',t) as sml from test_trgm2 where t %> 'Kabankala' order by sml desc, t;
*** a/contrib/pg_trgm/sql/pg_trgm.sql
--- b/contrib/pg_trgm/sql/pg_trgm.sql
***************
*** 13,19 **** select similarity('wow',' WOW ');
select similarity('---', '####---');
! CREATE TABLE test_trgm(t text);
\copy test_trgm from 'data/trgm.data'
--- 13,19 ----
select similarity('---', '####---');
! CREATE TABLE test_trgm(t text COLLATE "C");
\copy test_trgm from 'data/trgm.data'
***************
*** 40,46 **** select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu098
select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
! create table test2(t text);
insert into test2 values ('abcdef');
insert into test2 values ('quark');
insert into test2 values (' z foo bar');
--- 40,46 ----
select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
! create table test2(t text COLLATE "C");
insert into test2 values ('abcdef');
insert into test2 values ('quark');
insert into test2 values (' z foo bar');
*** a/contrib/pg_trgm/trgm.h
--- b/contrib/pg_trgm/trgm.h
***************
*** 26,37 ****
#define DIVUNION
/* operator strategy numbers */
! #define SimilarityStrategyNumber 1
! #define DistanceStrategyNumber 2
! #define LikeStrategyNumber 3
! #define ILikeStrategyNumber 4
! #define RegExpStrategyNumber 5
! #define RegExpICaseStrategyNumber 6
typedef char trgm[3];
--- 26,38 ----
#define DIVUNION
/* operator strategy numbers */
! #define SimilarityStrategyNumber 1
! #define DistanceStrategyNumber 2
! #define LikeStrategyNumber 3
! #define ILikeStrategyNumber 4
! #define RegExpStrategyNumber 5
! #define RegExpICaseStrategyNumber 6
! #define SubstringSimilarityStrategyNumber 7
typedef char trgm[3];
***************
*** 103,117 **** typedef char *BITVECP;
#define GETARR(x) ( (trgm*)( (char*)x+TRGMHDRSIZE ) )
#define ARRNELEM(x) ( ( VARSIZE(x) - TRGMHDRSIZE )/sizeof(trgm) )
typedef struct TrgmPackedGraph TrgmPackedGraph;
extern float4 trgm_limit;
extern uint32 trgm2int(trgm *ptr);
extern void compact_trigram(trgm *tptr, char *str, int bytelen);
extern TRGM *generate_trgm(char *str, int slen);
extern TRGM *generate_wildcard_trgm(const char *str, int slen);
! extern float4 cnt_sml(TRGM *trg1, TRGM *trg2);
extern bool trgm_contained_by(TRGM *trg1, TRGM *trg2);
extern bool *trgm_presence_map(TRGM *query, TRGM *key);
extern TRGM *createTrgmNFA(text *text_re, Oid collation,
--- 104,125 ----
#define GETARR(x) ( (trgm*)( (char*)x+TRGMHDRSIZE ) )
#define ARRNELEM(x) ( ( VARSIZE(x) - TRGMHDRSIZE )/sizeof(trgm) )
+ #ifdef DIVUNION
+ #define CALCSML(count, len1, len2) ((float4) (count)) / ((float4) ((len1) + (len2) - (count)))
+ #else
+ #define CALCSML(count, len1, len2) ((float4) (count)) / ((float4) (((len1) > (len2)) ? (len1) : (len2)))
+ #endif
+
typedef struct TrgmPackedGraph TrgmPackedGraph;
extern float4 trgm_limit;
+ extern float4 trgm_substring_limit;
extern uint32 trgm2int(trgm *ptr);
extern void compact_trigram(trgm *tptr, char *str, int bytelen);
extern TRGM *generate_trgm(char *str, int slen);
extern TRGM *generate_wildcard_trgm(const char *str, int slen);
! extern float4 cnt_sml(TRGM *trg1, TRGM *trg2, bool inexact);
extern bool trgm_contained_by(TRGM *trg1, TRGM *trg2);
extern bool *trgm_presence_map(TRGM *query, TRGM *key);
extern TRGM *createTrgmNFA(text *text_re, Oid collation,
*** a/contrib/pg_trgm/trgm_gin.c
--- b/contrib/pg_trgm/trgm_gin.c
***************
*** 89,94 **** gin_extract_query_trgm(PG_FUNCTION_ARGS)
--- 89,95 ----
switch (strategy)
{
case SimilarityStrategyNumber:
+ case SubstringSimilarityStrategyNumber:
trg = generate_trgm(VARDATA(val), VARSIZE(val) - VARHDRSZ);
break;
case ILikeStrategyNumber:
***************
*** 183,188 **** gin_trgm_consistent(PG_FUNCTION_ARGS)
--- 184,190 ----
switch (strategy)
{
case SimilarityStrategyNumber:
+ case SubstringSimilarityStrategyNumber:
/* Count the matches */
ntrue = 0;
for (i = 0; i < nkeys; i++)
***************
*** 272,277 **** gin_trgm_triconsistent(PG_FUNCTION_ARGS)
--- 274,280 ----
switch (strategy)
{
case SimilarityStrategyNumber:
+ case SubstringSimilarityStrategyNumber:
/* Count the matches */
ntrue = 0;
for (i = 0; i < nkeys; i++)
*** a/contrib/pg_trgm/trgm_gist.c
--- b/contrib/pg_trgm/trgm_gist.c
***************
*** 191,196 **** gtrgm_consistent(PG_FUNCTION_ARGS)
--- 191,197 ----
bool res;
Size querysize = VARSIZE(query);
gtrgm_consistent_cache *cache;
+ float4 nlimit;
/*
* We keep the extracted trigrams in cache, because trigram extraction is
***************
*** 218,223 **** gtrgm_consistent(PG_FUNCTION_ARGS)
--- 219,225 ----
switch (strategy)
{
case SimilarityStrategyNumber:
+ case SubstringSimilarityStrategyNumber:
qtrg = generate_trgm(VARDATA(query),
querysize - VARHDRSZ);
break;
***************
*** 286,300 **** gtrgm_consistent(PG_FUNCTION_ARGS)
switch (strategy)
{
case SimilarityStrategyNumber:
! /* Similarity search is exact */
! *recheck = false;
if (GIST_LEAF(entry))
{ /* all leafs contains orig trgm */
! float4 tmpsml = cnt_sml(key, qtrg);
/* strange bug at freebsd 5.2.1 and gcc 3.3.3 */
! res = (*(int *) &tmpsml == *(int *) &trgm_limit || tmpsml > trgm_limit) ? true : false;
}
else if (ISALLTRUE(key))
{ /* non-leaf contains signature */
--- 288,304 ----
switch (strategy)
{
case SimilarityStrategyNumber:
! case SubstringSimilarityStrategyNumber:
! /* Similarity search is exact. Substring similarity search is inexact */
! *recheck = (strategy == SubstringSimilarityStrategyNumber) ? true : false;
! nlimit = (strategy == SimilarityStrategyNumber) ? trgm_limit : trgm_substring_limit;
if (GIST_LEAF(entry))
{ /* all leafs contains orig trgm */
! float4 tmpsml = cnt_sml(qtrg, key, *recheck);
/* strange bug at freebsd 5.2.1 and gcc 3.3.3 */
! res = (*(int *) &tmpsml == *(int *) &nlimit || tmpsml > nlimit) ? true : false;
}
else if (ISALLTRUE(key))
{ /* non-leaf contains signature */
***************
*** 308,314 **** gtrgm_consistent(PG_FUNCTION_ARGS)
if (len == 0)
res = false;
else
! res = (((((float8) count) / ((float8) len))) >= trgm_limit) ? true : false;
}
break;
case ILikeStrategyNumber:
--- 312,318 ----
if (len == 0)
res = false;
else
! res = (((((float8) count) / ((float8) len))) >= nlimit) ? true : false;
}
break;
case ILikeStrategyNumber:
***************
*** 463,469 **** gtrgm_distance(PG_FUNCTION_ARGS)
case DistanceStrategyNumber:
if (GIST_LEAF(entry))
{ /* all leafs contains orig trgm */
! res = 1.0 - cnt_sml(key, qtrg);
}
else if (ISALLTRUE(key))
{ /* all leafs contains orig trgm */
--- 467,473 ----
case DistanceStrategyNumber:
if (GIST_LEAF(entry))
{ /* all leafs contains orig trgm */
! res = 1.0 - cnt_sml(key, qtrg, false);
}
else if (ISALLTRUE(key))
{ /* all leafs contains orig trgm */
*** a/contrib/pg_trgm/trgm_op.c
--- b/contrib/pg_trgm/trgm_op.c
***************
*** 15,27 ****
--- 15,33 ----
PG_MODULE_MAGIC;
float4 trgm_limit = 0.3f;
+ float4 trgm_substring_limit = 0.6f;
PG_FUNCTION_INFO_V1(set_limit);
PG_FUNCTION_INFO_V1(show_limit);
PG_FUNCTION_INFO_V1(show_trgm);
PG_FUNCTION_INFO_V1(similarity);
+ PG_FUNCTION_INFO_V1(substring_similarity);
PG_FUNCTION_INFO_V1(similarity_dist);
PG_FUNCTION_INFO_V1(similarity_op);
+ PG_FUNCTION_INFO_V1(set_substring_limit);
+ PG_FUNCTION_INFO_V1(show_substring_limit);
+ PG_FUNCTION_INFO_V1(substring_similarity_op);
+ PG_FUNCTION_INFO_V1(substring_similarity_commutator_op);
Datum
***************
*** 41,46 **** show_limit(PG_FUNCTION_ARGS)
--- 47,69 ----
PG_RETURN_FLOAT4(trgm_limit);
}
+ Datum
+ set_substring_limit(PG_FUNCTION_ARGS)
+ {
+ float4 nlimit = PG_GETARG_FLOAT4(0);
+
+ if (nlimit < 0 || nlimit > 1.0)
+ elog(ERROR, "wrong limit, should be between 0 and 1");
+ trgm_substring_limit = nlimit;
+ PG_RETURN_FLOAT4(trgm_substring_limit);
+ }
+
+ Datum
+ show_substring_limit(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_FLOAT4(trgm_substring_limit);
+ }
+
static int
comp_trgm(const void *a, const void *b)
{
***************
*** 166,203 **** make_trigrams(trgm *tptr, char *str, int bytelen, int charlen)
return tptr;
}
! TRGM *
! generate_trgm(char *str, int slen)
{
! TRGM *trg;
char *buf;
! trgm *tptr;
! int len,
! charlen,
bytelen;
char *bword,
*eword;
- /*
- * Guard against possible overflow in the palloc requests below. (We
- * don't worry about the additive constants, since palloc can detect
- * requests that are a little above MaxAllocSize --- we just need to
- * prevent integer overflow in the multiplications.)
- */
- if ((Size) (slen / 2) >= (MaxAllocSize / (sizeof(trgm) * 3)) ||
- (Size) slen >= (MaxAllocSize / pg_database_encoding_max_length()))
- ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("out of memory")));
-
- trg = (TRGM *) palloc(TRGMHDRSIZE + sizeof(trgm) * (slen / 2 + 1) *3);
- trg->flag = ARRKEY;
- SET_VARSIZE(trg, TRGMHDRSIZE);
-
if (slen + LPADDING + RPADDING < 3 || slen == 0)
! return trg;
! tptr = GETARR(trg);
/* Allocate a buffer for case-folded, blank-padded words */
buf = (char *) palloc(slen * pg_database_encoding_max_length() + 4);
--- 189,208 ----
return tptr;
}
! static int
! generate_trgm_only(trgm *trg, char *str, int slen)
{
! trgm *tptr;
char *buf;
! int charlen,
bytelen;
char *bword,
*eword;
if (slen + LPADDING + RPADDING < 3 || slen == 0)
! return 0;
! tptr = trg;
/* Allocate a buffer for case-folded, blank-padded words */
buf = (char *) palloc(slen * pg_database_encoding_max_length() + 4);
***************
*** 237,243 **** generate_trgm(char *str, int slen)
pfree(buf);
! if ((len = tptr - GETARR(trg)) == 0)
return trg;
/*
--- 242,281 ----
pfree(buf);
! return tptr - trg;
! }
!
! static void
! protect_out_of_mem(int slen)
! {
! /*
! * Guard against possible overflow in the palloc requests below. (We
! * don't worry about the additive constants, since palloc can detect
! * requests that are a little above MaxAllocSize --- we just need to
! * prevent integer overflow in the multiplications.)
! */
! if ((Size) (slen / 2) >= (MaxAllocSize / (sizeof(trgm) * 3)) ||
! (Size) slen >= (MaxAllocSize / pg_database_encoding_max_length()))
! ereport(ERROR,
! (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
! errmsg("out of memory")));
! }
!
! TRGM *
! generate_trgm(char *str, int slen)
! {
! TRGM *trg;
! int len;
!
! protect_out_of_mem(slen);
!
! trg = (TRGM *) palloc(TRGMHDRSIZE + sizeof(trgm) * (slen / 2 + 1) *3);
! trg->flag = ARRKEY;
!
! len = generate_trgm_only(GETARR(trg), str, slen);
! SET_VARSIZE(trg, CALCGTSIZE(ARRKEY, len));
!
! if (len == 0)
return trg;
/*
***************
*** 254,259 **** generate_trgm(char *str, int slen)
--- 292,534 ----
return trg;
}
+ /* Trigram with position */
+ typedef struct
+ {
+ trgm t;
+ int i;
+ } ptrgm;
+
+ /*
+ * Make array of positional trigrams from two trigram arrays. The first array
+ * is required substing which positions don't matter and replaced with -1.
+ * The second array is haystack where we search and have to store its
+ * positions.
+ */
+ static ptrgm *
+ make_positional_trgm(trgm *trg1, int len1, trgm *trg2, int len2)
+ {
+ ptrgm *result;
+ int i, len = len1 + len2;
+
+ result = (ptrgm *) palloc(sizeof(ptrgm) * len);
+
+ for (i = 0; i < len1; i++)
+ {
+ memcpy(&result[i].t, &trg1[i], sizeof(trgm));
+ result[i].i = -1;
+ }
+
+ for (i = 0; i < len2; i++)
+ {
+ memcpy(&result[i + len1].t, &trg2[i], sizeof(trgm));
+ result[i + len1].i = i;
+ }
+
+ return result;
+ }
+
+ /*
+ * Compare position trigrams: compare trigrams first and position second.
+ */
+ static int
+ comp_ptrgm(const void *v1, const void *v2)
+ {
+ const ptrgm *p1 = (const ptrgm *)v1;
+ const ptrgm *p2 = (const ptrgm *)v2;
+ int cmp;
+
+ cmp = CMPTRGM(p1->t, p2->t);
+ if (cmp != 0)
+ return cmp;
+
+ if (p1->i < p2->i)
+ return -1;
+ else if (p1->i == p2->i)
+ return 0;
+ else
+ return 1;
+ }
+
+ /*
+ * Iterative procedure if finding maximum similarity with substring.
+ */
+ static float4
+ iterate_substring_similarity(int *trg2indexes,
+ bool *found,
+ int ulen1,
+ int len2,
+ int len)
+ {
+ int *lastpos,
+ i,
+ ulen2 = 0,
+ count = 0,
+ upper = -1,
+ lower = -1;
+ float4 smlr_cur,
+ smlr_max = 0.0f;
+
+ /* Memorise last position of each trigram */
+ lastpos = (int *) palloc(sizeof(int) * len);
+ memset(lastpos, -1, sizeof(int) * len);
+
+ for (i = 0; i < len2; i++)
+ {
+ /* Get index of next trigram */
+ int trgindex = trg2indexes[i];
+
+ /* Update last position of this trigram */
+ if (lower >= 0 || found[trgindex])
+ {
+ if (lastpos[trgindex] < 0)
+ {
+ ulen2++;
+ if (found[trgindex])
+ count++;
+ }
+ lastpos[trgindex] = i;
+ }
+
+ /* Adjust lower bound if this trigram is present in required substing */
+ if (found[trgindex])
+ {
+ int prev_lower,
+ tmp_ulen2,
+ tmp_lower,
+ tmp_count;
+
+ upper = i;
+ if (lower == -1)
+ {
+ lower = i;
+ ulen2 = 1;
+ }
+
+ smlr_cur = CALCSML(count, ulen1, ulen2);
+
+ /* Also try to adjust upper bound for greater similarity */
+ tmp_count = count;
+ tmp_ulen2 = ulen2;
+ prev_lower = lower;
+ for (tmp_lower = lower; tmp_lower <= upper; tmp_lower++)
+ {
+ float smlr_tmp = CALCSML(tmp_count, ulen1, tmp_ulen2);
+ int tmp_trgindex;
+
+ if (smlr_tmp > smlr_cur)
+ {
+ smlr_cur = smlr_tmp;
+ ulen2 = tmp_ulen2;
+ lower = tmp_lower;
+ count = tmp_count;
+ }
+ tmp_trgindex = trg2indexes[tmp_lower];
+ if (lastpos[tmp_trgindex] == tmp_lower)
+ {
+ tmp_ulen2--;
+ if (found[tmp_trgindex])
+ tmp_count--;
+ }
+ }
+
+ for (tmp_lower = prev_lower; tmp_lower < lower; tmp_lower++)
+ {
+ int tmp_trgindex;
+ tmp_trgindex = trg2indexes[tmp_lower];
+ if (lastpos[tmp_trgindex] == tmp_lower)
+ lastpos[tmp_trgindex] = -1;
+ }
+
+ smlr_max = Max(smlr_max, smlr_cur);
+ }
+ }
+
+ pfree(lastpos);
+
+ return smlr_max;
+ }
+
+ /*
+ * Calculate substring similarity.
+ */
+ static float4
+ calc_substring_similarity(char *str1, int slen1, char *str2, int slen2)
+ {
+ bool *found;
+ ptrgm *ptrg;
+ trgm *trg1;
+ trgm *trg2;
+ int len1,
+ len2,
+ len,
+ i,
+ j,
+ ulen1;
+ int *trg2indexes;
+ float4 result;
+
+ protect_out_of_mem(slen1 + slen2);
+
+ /* Make positional trigrams */
+ trg1 = (trgm *) palloc(sizeof(trgm) * (slen1 / 2 + 1) * 3);
+ trg2 = (trgm *) palloc(sizeof(trgm) * (slen2 / 2 + 1) * 3);
+
+ len1 = generate_trgm_only(trg1, str1, slen1);
+ len2 = generate_trgm_only(trg2, str2, slen2);
+
+ ptrg = make_positional_trgm(trg1, len1, trg2, len2);
+ len = len1 + len2;
+ qsort(ptrg, len, sizeof(ptrgm), comp_ptrgm);
+
+ pfree(trg1);
+ pfree(trg2);
+
+ /*
+ * Merge positional trigrams array: enumerate each trigram and find its
+ * presence in required substring.
+ */
+ trg2indexes = (int *) palloc(sizeof(int) * len2);
+ found = (bool *) palloc0(sizeof(bool) * len);
+
+ ulen1 = 0;
+ j = 0;
+ for (i = 0; i < len; i++)
+ {
+ if (i > 0)
+ {
+ int cmp = CMPTRGM(ptrg[i - 1].t, ptrg[i].t);
+ if (cmp != 0)
+ {
+ if (found[j])
+ ulen1++;
+ j++;
+ }
+ }
+
+ if (ptrg[i].i >= 0)
+ {
+ trg2indexes[ptrg[i].i] = j;
+ }
+ else
+ {
+ found[j] = true;
+ }
+ }
+ if (found[j])
+ ulen1++;
+
+ /* Run iterative procedure to find maximum similarity with substring */
+ result = iterate_substring_similarity(trg2indexes, found, ulen1, len2, len);
+
+ pfree(trg2indexes);
+ pfree(found);
+ pfree(ptrg);
+
+ return result;
+ }
+
+
/*
* Extract the next non-wildcard part of a search string, ie, a word bounded
* by '_' or '%' meta-characters, non-word characters or string end.
***************
*** 426,442 **** generate_wildcard_trgm(const char *str, int slen)
bytelen;
const char *eword;
! /*
! * Guard against possible overflow in the palloc requests below. (We
! * don't worry about the additive constants, since palloc can detect
! * requests that are a little above MaxAllocSize --- we just need to
! * prevent integer overflow in the multiplications.)
! */
! if ((Size) (slen / 2) >= (MaxAllocSize / (sizeof(trgm) * 3)) ||
! (Size) slen >= (MaxAllocSize / pg_database_encoding_max_length()))
! ereport(ERROR,
! (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
! errmsg("out of memory")));
trg = (TRGM *) palloc(TRGMHDRSIZE + sizeof(trgm) * (slen / 2 + 1) *3);
trg->flag = ARRKEY;
--- 701,707 ----
bytelen;
const char *eword;
! protect_out_of_mem(slen);
trg = (TRGM *) palloc(TRGMHDRSIZE + sizeof(trgm) * (slen / 2 + 1) *3);
trg->flag = ARRKEY;
***************
*** 557,563 **** show_trgm(PG_FUNCTION_ARGS)
}
float4
! cnt_sml(TRGM *trg1, TRGM *trg2)
{
trgm *ptr1,
*ptr2;
--- 822,828 ----
}
float4
! cnt_sml(TRGM *trg1, TRGM *trg2, bool inexact)
{
trgm *ptr1,
*ptr2;
***************
*** 591,604 **** cnt_sml(TRGM *trg1, TRGM *trg2)
}
}
! #ifdef DIVUNION
! return ((float4) count) / ((float4) (len1 + len2 - count));
! #else
! return ((float4) count) / ((float4) ((len1 > len2) ? len1 : len2));
! #endif
!
}
/*
* Returns whether trg2 contains all trigrams in trg1.
* This relies on the trigram arrays being sorted.
--- 856,869 ----
}
}
! /* if inexact then len2 is equal to count */
! if (inexact)
! return CALCSML(count, len1, count);
! else
! return CALCSML(count, len1, len2);
}
+
/*
* Returns whether trg2 contains all trigrams in trg1.
* This relies on the trigram arrays being sorted.
***************
*** 693,699 **** similarity(PG_FUNCTION_ARGS)
trg1 = generate_trgm(VARDATA(in1), VARSIZE(in1) - VARHDRSZ);
trg2 = generate_trgm(VARDATA(in2), VARSIZE(in2) - VARHDRSZ);
! res = cnt_sml(trg1, trg2);
pfree(trg1);
pfree(trg2);
--- 958,964 ----
trg1 = generate_trgm(VARDATA(in1), VARSIZE(in1) - VARHDRSZ);
trg2 = generate_trgm(VARDATA(in2), VARSIZE(in2) - VARHDRSZ);
! res = cnt_sml(trg1, trg2, false);
pfree(trg1);
pfree(trg2);
***************
*** 704,709 **** similarity(PG_FUNCTION_ARGS)
--- 969,990 ----
}
Datum
+ substring_similarity(PG_FUNCTION_ARGS)
+ {
+ text *in1 = PG_GETARG_TEXT_PP(0);
+ text *in2 = PG_GETARG_TEXT_PP(1);
+ float4 res;
+
+ res = calc_substring_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
+ VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2));
+
+ PG_FREE_IF_COPY(in1, 0);
+ PG_FREE_IF_COPY(in2, 1);
+
+ PG_RETURN_FLOAT4(res);
+ }
+
+ Datum
similarity_dist(PG_FUNCTION_ARGS)
{
float4 res = DatumGetFloat4(DirectFunctionCall2(similarity,
***************
*** 722,724 **** similarity_op(PG_FUNCTION_ARGS)
--- 1003,1025 ----
PG_RETURN_BOOL(res >= trgm_limit);
}
+
+ Datum
+ substring_similarity_op(PG_FUNCTION_ARGS)
+ {
+ float4 res = DatumGetFloat4(DirectFunctionCall2(substring_similarity,
+ PG_GETARG_DATUM(0),
+ PG_GETARG_DATUM(1)));
+
+ PG_RETURN_BOOL(res >= trgm_substring_limit);
+ }
+
+ Datum
+ substring_similarity_commutator_op(PG_FUNCTION_ARGS)
+ {
+ float4 res = DatumGetFloat4(DirectFunctionCall2(substring_similarity,
+ PG_GETARG_DATUM(1),
+ PG_GETARG_DATUM(0)));
+
+ PG_RETURN_BOOL(res >= trgm_substring_limit);
+ }
*** a/doc/src/sgml/pgtrgm.sgml
--- b/doc/src/sgml/pgtrgm.sgml
***************
*** 85,90 ****
--- 85,101 ----
+ substring_similarity(text, text)substring_similarity
+ real
+
+ Returns a number that indicates how similar the first string
+ to the most similar substring of the second string. The range of
+ the result is zero (indicating that the two strings are completely
+ dissimilar) to one (indicating that the first string is identical
+ to substring of the second substring).
+
+
+
show_trgm(text)show_trgm
text[]
***************
*** 111,116 ****
--- 122,145 ----
Returns the same value passed in.
+
+ show_substring_limit()show_substring_limit
+ real
+
+ Returns the current similarity threshold used by the <%
+ operator. This sets the minimum substring similarity between
+ two phrases.
+
+
+
+ set_substring_limit(real)set_substring_limit
+ real
+
+ Sets the current substring similarity threshold that is used by
+ the <% operator. The threshold must be between
+ 0 and 1 (default is 0.6). Returns the same value passed in.
+
+
***************
*** 137,142 ****
--- 166,180 ----
+ text <% text
+ boolean
+
+ Returns true if its arguments have a substring similarity
+ that is greater than the current substring similarity threshold set by
+ set_substring_limit.
+
+
+
text <-> text
real
***************
*** 204,209 **** SELECT t, t <-> 'word' AS dist
--- 242,262 ----
+ Also you can use an index on the t column for substring
+ similarity. For example:
+
+ SELECT t, substring_similarity('word', t) AS sml
+ FROM test_trgm
+ WHERE 'word' <% t
+ ORDER BY sml DESC, t;
+
+ This will return all values in the text column that have a substring
+ which sufficiently similar to word, sorted from best
+ match to worst. The index will be used to make this a fast operation
+ even over very large data sets.
+
+
+
Beginning in PostgreSQL 9.1, these index types also support
index searches for LIKE and ILIKE, for example