Lists: | pgsql-hackers |
---|
From: | worthy7 <worthy(dot)vii(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | nulls in GIN index |
Date: | 2014-08-10 22:19:49 |
Message-ID: | 1407709189891-5814384.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
http://www.postgresql.org/docs/9.1/static/gin-implementation.html
"""As of PostgreSQL 9.1, NULL key values can be included in the index. Also,
placeholder NULLs are included in the index for indexed items that are NULL
or contain no keys according to extractValue. This allows searches that
should find empty items to do so."""
How do I define an index that includes nulls then?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/nulls-in-GIN-index-tp5814384.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
From: | Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
---|---|
To: | worthy7 <worthy(dot)vii(at)gmail(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: nulls in GIN index |
Date: | 2014-08-11 07:17:37 |
Message-ID: | 53E86E11.9020106@vmware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 08/11/2014 01:19 AM, worthy7 wrote:
>
> http://www.postgresql.org/docs/9.1/static/gin-implementation.html
> """As of PostgreSQL 9.1, NULL key values can be included in the index. Also,
> placeholder NULLs are included in the index for indexed items that are NULL
> or contain no keys according to extractValue. This allows searches that
> should find empty items to do so."""
>
> How do I define an index that includes nulls then?
You don't need to do anything special, any NULL values will be indexed
automatically.
- Heikki
From: | worthy7 <worthy(dot)vii(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: nulls in GIN index |
Date: | 2014-08-11 09:17:56 |
Message-ID: | 1407748676120-5814416.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Perhaps I'm missing something
Table has 2 columns, text and ftstext
text: "how are you"
ftstest: (nothing)
Because "how" and "are" and "you" are too common to be tsvectored. Which is
fine.
So if a user searches for "how are you":
select * from tbl_lines WHERE
ftstext @@ plainto_tsquery('English', 'how are you')
Returns nothing. Which I somewhat understand, but I want it to return all
the rows with nothing in the ftstext.
plainto_tsquery('English', 'how are you') = ''
and the ftstext of some rows is also = ''
So why doesn't the index return all these rows when a null string is
searched.
I think you can see what im trying to achieve, how do I do it?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/nulls-in-GIN-index-tp5814384p5814416.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: nulls in GIN index |
Date: | 2014-08-11 15:13:58 |
Message-ID: | VisenaEmail.2a.8edce5afe0de16c5.147c5a0b718@tc7-on |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
På mandag 11. august 2014 kl. 11:17:56, skrev worthy7 <worthy(dot)vii(at)gmail(dot)com
<mailto:worthy(dot)vii(at)gmail(dot)com>>: Perhaps I'm missing something
Table has 2 columns, text and ftstext
text: "how are you"
ftstest: (nothing)
Because "how" and "are" and "you" are too common to be tsvectored. Which is
fine.
So if a user searches for "how are you":
select * from tbl_lines WHERE
ftstext @@ plainto_tsquery('English', 'how are you')
Returns nothing. Which I somewhat understand, but I want it to return all
the rows with nothing in the ftstext.
plainto_tsquery('English', 'how are you') = ''
and the ftstext of some rows is also = ''
So why doesn't the index return all these rows when a null string is
searched.
I think you can see what im trying to achieve, how do I do it? Use the
'simple' dictionary: my_fts_column @@ to_tsquery('simple', 'how are you') --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>