-- data/init.sql

-- CREATE DATABASE postgres;

SET client_encoding = 'UTF8';

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    content_vector TSVECTOR,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_content_gin_jp ON posts USING GIN (content);

---

INSERT INTO posts (title, content, content_vector) VALUES 
('Post 1', 'This is the first post. https://example.com', to_tsvector('This is the first post. https://example.com')),
('Post 2', 'This is the second post. foo@example.com', to_tsvector('This is the second post. foo@example.com')),
(
    'Post 3', 
    'There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.', 
    to_tsvector('There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.')
),
('Post 4', 'これはテストです', to_tsvector('これはテストです')),
('Post 5', 'This is the fifth post. https://example.com', to_tsvector('This is the fifth post. https://example.com')),
('Post 6', 'This is the sixth post. foo@example.com', to_tsvector('This is the sixth post. foo@example.com'));

$ docker run --rm --name postgres -p 15432:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_DB=postgres \
    -v $(pwd)/data:/docker-entrypoint-initdb.d \
    postgres:14

$ docker exec -it postgres bash

-- SELECT id, title, content, ts_rank(content_vector, query) as rank
-- FROM posts, to_tsquery('english', 'post') query
-- WHERE query @@ content_vector;

postgres=# select id, title, content, ts_rank(content_vector, query) as rank from posts, to_tsquery('english', 'post') query where query @@ content_vector;
 id | title  |                   content                   |    rank    
----+--------+---------------------------------------------+------------
  1 | Post 1 | This is the first post.                     | 0.06079271
  2 | Post 2 | This is the second post.                    | 0.06079271
  5 | Post 5 | This is the fifth post. https://example.com | 0.06079271
  6 | Post 6 | This is the sixth post. foo@example.com     | 0.06079271

postgres=# select id, title, content_vector, ts_rank(content_vector, query) as rank from posts, to_tsquery('english', 'post') query where query @@ content_vector;
 id | title  |             content_vector             |    rank    
----+--------+----------------------------------------+------------
  1 | Post 1 | 'first':4 'post':5                     | 0.06079271
  2 | Post 2 | 'post':5 'second':4                    | 0.06079271
  5 | Post 5 | 'example.com':6 'fifth':4 'post':5     | 0.06079271
  6 | Post 6 | 'foo@example.com':6 'post':5 'sixth':4 | 0.06079271

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
 ?column? 
----------
 t

SELECT to_tsvector('fatal error') @@ to_tsquery('fatal <-> error');
 ?column? 
----------
 t

SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal <-> error');
 ?column? 
----------
 f

SELECT phraseto_tsquery('cats ate rats');
       phraseto_tsquery        
-------------------------------
 'cat' <-> 'ate' <-> 'rat'

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description              
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | armenian   | configuration for armenian language
 pg_catalog | basque     | configuration for basque language
 pg_catalog | catalan    | configuration for catalan language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | greek      | configuration for greek language
 pg_catalog | hindi      | configuration for hindi language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language
 pg_catalog | irish      | configuration for irish language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | serbian    | configuration for serbian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language
 pg_catalog | turkish    | configuration for turkish language
 pg_catalog | yiddish    | configuration for yiddish language
(29 rows)