codimdはさくっとメモを取るのに良いが、検索機能がついていない。dbはPostgreSQLなので、PGroonga - PostgreSQLで高速日本語全文検索！を試してみる。
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+------------+------------+-------------------
codimd | codimd | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | codimd | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | codimd | UTF8 | en_US.utf8 | en_US.utf8 | =c/codimd +
| | | | | codimd=CTc/codimd
template1 | codimd | UTF8 | en_US.utf8 | en_US.utf8 | =c/codimd +
| | | | | codimd=CTc/codimd
List of relations
Schema | Name | Type | Owner
--------+----------------+----------+--------
public | Authors | table | codimd
public | Authors_id_seq | sequence | codimd
public | Notes | table | codimd
public | Revisions | table | codimd
public | SequelizeMeta | table | codimd
public | Sessions | table | codimd
public | Users | table | codimd
Table "public.Notes"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------------------------------
id | uuid | | not null |
ownerId | uuid | | |
content | text | | |
title | text | | |
createdAt | timestamp with time zone | | |
updatedAt | timestamp with time zone | | |
shortid | character varying(255) | | not null | '0000000000'::character varying
permission | "enum_Notes_permission" | | |
viewcount | integer | | | 0
lastchangeuserId | uuid | | |
lastchangeAt | timestamp with time zone | | |
alias | character varying(255) | | |
savedAt | timestamp with time zone | | |
authorship | text | | |
deletedAt | timestamp with time zone | | |
とりあえず興味があるのはNoteテーブルの、titleとcontentなので、インデックスを作る
CREATE INDEX pgroonga_content_index ON "Note" USING pgroonga (content);
CREATE INDEX pgroonga_title_index ON "Note" USING pgroonga (title);
検索はチュートリアル | PGroongaを参照
単一カラム検索
SELECT title, shortid FROM "Notes" WHERE title &@~ 'キーワード';
複数カラムのOR検索
SELECT title, shortid FROM "Notes" WHERE title &@~ 'キーワード' OR conetnt &@~ 'キーワード';
psycopg2からの操作も可能