Thread: nulls in GIN index

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.

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

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.

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?

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com