PostgreSQL 8.3 on OS X: I like the full text indexing/search features

I built the latest version from source, with one problem: I was only able to install readline from source using "--disable-shared" so I ended up also building PostgreSQL statically linked - oh well so much for being in hurry, I have 2 gigs of RAM on my MacBook, so what is a little memory between friends :-)

I have been waiting for version 8.3 because of the full text indexing/search features. Here is the Text Search documentation - enjoy! Here is a little sample of the SQL extensions to support indexing and search:
test=# create table test (id integer, name varchar(30), email varchar(30));
CREATE TABLE

test=# create index test_name_idx on test using gin(to_tsvector('english', name));
CREATE INDEX
test=# insert into test values (1, 'Mark Watson', 'mark@mark.com');
INSERT 0 1
test=# insert into test values (2, 'Carol Watson', 'carol@mark.com');
INSERT 0 1
test=# select * from test where to_tsvector(name) @@ to_tsquery('mark');
 id |    name     |     email     
----+-------------+---------------
  1 | Mark Watson | mark@mark.com
(1 row)

test=# select * from test where to_tsvector(name) @@ to_tsquery('watsons');
 id |     name     |     email      
----+--------------+----------------
  1 | Mark Watson  | mark@mark.com
  2 | Carol Watson | carol@mark.com
(2 rows)

test=# test=# select * from test where to_tsvector(name) @@ to_tsquery('mark & watson');
 id |    name     |     email     
----+-------------+---------------
  1 | Mark Watson | mark@mark.com
(1 row)

test=# select * from test where to_tsvector(name) @@ to_tsquery('mark | watson');
 id |     name     |     email      
----+--------------+----------------
  1 | Mark Watson  | mark@mark.com
  2 | Carol Watson | carol@mark.com
(2 rows)

test=#
Obviously, if you were creating a new table with many rows, add the index after the data is added to the table. "gin" refers to a complete inverted word index. Specifying 'english' ensures that a word stemmer if used that understands English language conventions. Note that a search for 'watsons' matches because the search terms are stemmed before search.

The search syntax looks odd, but I expect to get used to it quickly. For Rails: I use "acts_like_ferret" a lot; I'll wait a month to see if any handy plugin is written for PostgreSQL specific search - I would rather that someone else write it. I need to check out acts_as_tsearch, but I don't think that it is updated yet to work with the final 8.3 release.

Comments

Post a Comment

Popular posts from this blog

My Dad's work with Robert Oppenheimer and Edward Teller

Robert Oppenheimer and Edward Teller facilitated my Dad getting a professorship at UC Berkeley when I was 3 years old. Oppenheimer left Berkeley but Teller was a good friend of my father and I remember him being in our home. Three weeks ago, my wife and I were just leaving to see the new Oppenheimer movie when my Dad called. He mentioned that when I was in grade school he was invited to give a talk at Princeton. After his talk Oppenheimer talked with my Dad and invited him to have dinner at his house. My Dad said Oppenheimer was not well (I think he died of throat cancer soon afterwards) but his wife Kitty carried the conversation. My Dad, Ken Watson, passed away 10 days ago on August 18, 2023.
Read more

Time and Attention Fragmentation in Our Digital Lives

  As humans we have evolved over a few million years to be both attentive and reactive to danger, live in social communities, and spend much of our time being in the present moment gathering and eating food and socializing. The behavior of rapidly changing short attention to content on social media, too many good short form things to watch on streaming video entertainment platforms, are all rewiring our brains in an unnatural and unhealthy way. I fight back, but in really simple ways that entail little ceremony: Almost every morning I spend 30 minutes scanning Hacker News (about 10 minutes), Apple News (about 5 minutes), and the remaining time on Twitter and Mastodon finding interesting new (mostly tech) things. I make notes in a temporary Apple Note: links of things I may want to research, try, or simply read that day. I like to get this all done at once, and then not feel like I need to interrupt my activities during the day to “catch up” on what is happening in the world. In a w...
Read more

I am moving back to the Google platform, less excited by what Apple is offering

I have been been playing with the Apple Intelligence beta’s in iPadOS and macOS and while I like the direction Apple is heading I am getting more use from Google’s Gemini, both for general analysis of very large input contexts, as well as effective integration my content in Gmail, Google Calendar, and Google Docs. While I find the latest Pixel phone to be compelling, I will stick with Apple hardware since I don’t want to take the time to move my data and general workflow to a Pixel phone. The iPhone is the strongest lock-in that Apple has on me because of the time investment to change. The main reason I am feeling less interested in the Apple ecosystem and platform is that I believe that our present day work flows are intimately wrapped up with the effective use of LLMs, and it is crazy to limit oneself to just one or two vendors. I rely on running local models on Ollama, super fast APIs from Groq (I love Groq for running most of the better open weight models), and other APIs from Mist...
Read more