postgres_guide.md

Mintoak pgvector PostgreSQL Inspection Commands

Below is a reference guide of all the useful psql and SQL commands to inspect, manage, and query your pgvector data inside the terminal.

---

1. Connecting to the Database

Connect to the database using the PostgreSQL command-line client:

psql -U postgres -h localhost -d mintoak_db

---

2. General Database Inspection (psql Meta-commands)

Run these commands inside the psql interactive prompt:

• List all extensions (verify if vector is installed):

  \dx
  

• List all tables in the database:

  \dt
  

• Inspect the table schema (view column types):

  \d mintoak_content
  

• Exit the psql shell:

  \q
  

---

3. SQL Data Queries

Run these SQL statements to query the knowledge base data:

• Count the total number of document chunks:

  SELECT COUNT(*) FROM mintoak_content;
  

• Retrieve sample document contents:

  SELECT id, document, metadata->>'title' AS title, metadata->>'url' AS url 
  FROM mintoak_content 
  LIMIT 5;
  

• Find documents by specific metadata category:

  SELECT id, document 
  FROM mintoak_content 
  WHERE metadata->>'category' = 'Company Info' 
  LIMIT 3;
  

---

4. Vector Search & Similarity Queries

You can run a raw cosine similarity search using the pgvector <=> distance operator:

• Fetch top 3 most similar documents to a test embedding: (Replace the vector array with your query embedding vector)

  SELECT id, document, (embedding <=> '[0.012, -0.045, 0.089, ... (384 float values)]'::vector) AS distance 
  FROM mintoak_content 
  ORDER BY distance ASC 
  LIMIT 3;
  

• Inspect raw vector values: (Trims the vector array printout for readability)

  SELECT id, subpath(embedding::text, 1, 50) || '...' AS vector_preview 
  FROM mintoak_content 
  LIMIT 3;