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:
```bash
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):
  ```sql
  \dx
  ```
* **List all tables** in the database:
  ```sql
  \dt
  ```
* **Inspect the table schema** (view column types):
  ```sql
  \d mintoak_content
  ```
* **Exit the psql shell**:
  ```sql
  \q
  ```

---

## 3. SQL Data Queries
Run these SQL statements to query the knowledge base data:

* **Count the total number of document chunks**:
  ```sql
  SELECT COUNT(*) FROM mintoak_content;
  ```

* **Retrieve sample document contents**:
  ```sql
  SELECT id, document, metadata->>'title' AS title, metadata->>'url' AS url 
  FROM mintoak_content 
  LIMIT 5;
  ```

* **Find documents by specific metadata category**:
  ```sql
  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)*
  ```sql
  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)*
  ```sql
  SELECT id, subpath(embedding::text, 1, 50) || '...' AS vector_preview 
  FROM mintoak_content 
  LIMIT 3;
  ```