@alex hey how do I do a full text search in postgres? I'm looking for any post, from anywhere, that has a multiword phrase in it
Follow
@r000t @alex Post in the Mastodon db?
I think it should be enough to create a GIN index:
ALTER TABLE statuses ADD COLUMN tsv tsvector; CREATE INDEX tsv_idx ON statuses USING gin(tsv);
populate it:
UPDATE statuses SET tsv = to_tsvector(text);
and than just run a regular SELECT query on it.
You need to update the index every time a post is added or create a trigger for it.
Or it might be easier to just export the column "text" into a file and run grep on it.
@matrix @alex I found the right ID in my browser history, now I'm wondering if the status was organically deleted
can blocks be sorted by date?