[GENERAL] Searching email, Full Text Search prefix, not expected results
Hi guys, I'm going to crazy about FTS with prefix agains email values on
tsvector. Follow how to reproduce:
For the next tsvector:
=# select to_tsvector('u...@company.com')
to_tsvector
--
'u...@company.com':1
I expects TRUE for all next tsqueryes:
select to_tsvector('u...@company.com') @@ to_tsquery('u:*');
select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
select to_tsvector('u...@company.com') @@ to_tsquery('use:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@com:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compa:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compan:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:*');
But NOT, there are some NOT expected and confusing me results:
=# select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
?column?
--
t
(1 row)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
?column?
--
t
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
?column?
--
t
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:
*');
?column?
--
t TRUE OOhHHH I'm going crazy!!!
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
?column?
--
t TRUE ... Yes I'm crazy.
Please some ligths about it.
(I follow the official docs in
http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my
knowledge)
Thx!
Re: [GENERAL] Searching email, Full Text Search prefix, not expected results
But tsvector recognices email format as natural. And I'm not looking for a
substring. Please see the queries are incremental with the search string,
and see last four results ... I think some think it's no working properly.
El 14 de marzo de 2012 19:05, Daniel Vázquez daniel2d2...@gmail.comescribió:
Hi guys, I'm going to crazy about FTS with prefix agains email values on
tsvector. Follow how to reproduce:
For the next tsvector:
=# select to_tsvector('u...@company.com')
to_tsvector
--
'u...@company.com':1
I expects TRUE for all next tsqueryes:
select to_tsvector('u...@company.com') @@ to_tsquery('u:*');
select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
select to_tsvector('u...@company.com') @@ to_tsquery('use:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@com:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compa:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@compan:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.:*');
select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:*');
select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
But NOT, there are some NOT expected and confusing me results:
=# select to_tsvector('u...@company.com') @@ to_tsquery('us:*');
?column?
--
t
(1 row)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user:*');
?column?
--
t
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@:*');
?column?
--
t
select to_tsvector('u...@company.com') @@ to_tsquery('user@comp:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.
:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('user@company.c
:*');
?column?
--
f FALSE (I expects TRUE)
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.co:
*');
?column?
--
t TRUE OOhHHH I'm going crazy!!!
=# select to_tsvector('u...@company.com') @@ to_tsquery('u...@company.com:
*');
?column?
--
t TRUE ... Yes I'm crazy.
Please some ligths about it.
(I follow the official docs in
http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my
knowledge)
Thx!
--
Daniel Vázquez
SICONET (A Bull Group Company)
Torre Agbar. Avda. Diagonal, 211 - planta 23
08018 - Barcelona
telf: + 34 93 2272727 (Ext. 2952)
fax: + 34 93 2272728
www.bull.es - www.siconet.es
daniel.vazq...@bull.es
[GENERAL] Mix characters with utf-8 characters on the same query
Hi guys!
There's a way to mix characters with utf-8 characters on the same query.
Some thing like this:
Character: . (dot)
UTF-8: *\u002E*
(requisite* can't use regex*)
For this normal query:
select * from foo where email like 'em...@company.com '
Some thing like this:
select * from foo where email like 'email@company*\u002e*com '
But really I need on Full Text Search query:
select * from foo where (full_text_search_vector) @@
(to_tsquery('spanish', 'email@company*\u002e*com:*'))
Please don't think about why we're trying to match a simple dot in this
way.
Thx!
[GENERAL] Unaccent: create new dictionary based template
Hi guys!
I'want to run unaccent function with a custom unaccent dictionary
I know that we can do:
Select unaccent('áéíóúñ')
Select unaccent('unaccent', 'áéíóúñ')
Select unaccent('custom_unaccent', 'áéíóúñ)
PG Docs say that we can set the rules for default installed unaccent
dictionary:
ALTER TEXT SEARCH DICTIONARY unaccent (RULES='custom_unaccent');
And PG Docs too say that we can create another dictionary based on the
template. I think this is the right way to maintain several custom unaccent
needs without override default unaccent rules. But I don't know how to
create unaccent dictionary from a template.
What dictionary type we must to create?
Please can show me what are the commands sentences we must to execute to
create this unaccent_custom dictionary and it will be ready for use
in Select unaccent('custom_unaccent', 'áéíóúñ) ??
Thx
[GENERAL] initdb $PGDATA not working
Hi! I've set my PGDATA variable in profile export PGDATA=/home/mydata/pgsql/data Testing variable for correct set in enviroment $ echo $PGDATA /home/mydata/pgsql/data but when execute: $ sudo /etc/init.d/postgresql-9.1 initdb or execute: $ sudo /etc/init.d/postgresql-9.1 initdb -D /home/mydata/pgsql/data The cluster always is created on default path /var/lib/pgsql/9.1/data/ Will install in CentOS 5.7 Thx
Re: [GENERAL] initdb $PGDATA not working
THX all !! My fault is I set the $PGDATA enviroment variable in /etc/sysconfig/pgsql/**postgresql like usual in 8.4 renaming the file to postgresql-9.1 solve the isue. Thanks guys! El 9 de febrero de 2012 22:25, Daniel Vázquez daniel2d2...@gmail.comescribió: Hi! I've set my PGDATA variable in profile export PGDATA=/home/mydata/pgsql/data Testing variable for correct set in enviroment $ echo $PGDATA /home/mydata/pgsql/data but when execute: $ sudo /etc/init.d/postgresql-9.1 initdb or execute: $ sudo /etc/init.d/postgresql-9.1 initdb -D /home/mydata/pgsql/data The cluster always is created on default path /var/lib/pgsql/9.1/data/ Will install in CentOS 5.7 Thx
[GENERAL] Full Text Search, avoiding lexemes search
Hi guys!
Full text search, searches by lexemes, this minds that if you are finding
for gato word you are really finding for {gat} lexeme.
I you construct vectors for the words gato, gatos, gata, gatas, all
have the same lexema {gat}
Then the search gato that is to say the search {gat} matches with all
previous vectors.
There some way (configuration, query) to match only for gato and
avoid gatos gata gatas, with FTS ??
Or match only for gato gatos buy no for gata gatas?
Tnks!
[GENERAL] Full Text Search, avoiding lexemes search
Hi guys!
Full text search, searches by lexemes, this minds that if you are finding
for gato word you are really finding for {gat} lexeme.
I you construct vectors for the words gato, gatos, gata, gatas, all
have the same lexema {gat}
Then the search gato that is to say the search {gat} matches with all
previous vectors.
There some way (configuration, query) to match only for gato and
avoid gatos gata gatas, with FTS ??
Or match only for gato gatos buy no for gata gatas?
Tnks!
