Preface: Welcome to Cognica SQL

Welcome to the Cognica SQL Reference Manual. This document is designed to be your comprehensive guide to writing SQL queries in Cognica Database. Whether you're a seasoned database administrator or just starting your journey with SQL, this manual will help you understand not just the syntax, but the underlying concepts that make your queries work efficiently.

PostgreSQL Compatibility: Use What You Already Know

Cognica speaks PostgreSQL. If you know PostgreSQL, you already know Cognica SQL.

Cognica implements the PostgreSQL wire protocol, which means:

• Your existing tools just work. Connect with psql, pgAdmin, DBeaver, DataGrip, or any PostgreSQL-compatible client. No special drivers or adapters needed.
• Your existing code just works. Applications using libpq, psycopg2, node-postgres, JDBC, or any PostgreSQL driver can connect to Cognica without modification.
• Your existing queries just work. The vast majority of PostgreSQL SQL syntax is supported. SELECT, INSERT, UPDATE, DELETE, JOIN, subqueries, CTEs, window functions, transactions - they all work as expected.

# Python example - same code works for both PostgreSQL and Cognica
import psycopg2

# Just change the connection string
conn = psycopg2.connect("host=localhost port=5432 dbname=mydb")
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days'")

// Node.js example - identical API
const { Client } = require('pg');
const client = new Client({ host: 'localhost', port: 5432, database: 'mydb' });
await client.connect();
const result = await client.query('SELECT * FROM orders WHERE status = $1', ['pending']);

What This Means for You:

Cognica-Specific Extensions:

While Cognica maintains PostgreSQL compatibility, it also provides extensions for modern workloads:

• Vector Search: SELECT * FROM docs WHERE _all @@ 'embedding:[[0.1, 0.2, ...]]' ORDER BY _meta.score DESC
• Full-Text Search: SELECT * FROM articles WHERE _all @@ 'content:(database performance)'
• Hybrid Search: Combine keyword precision with semantic understanding
• Document Operations: Native JSON/JSONB support with rich query operators
• Data Federation: Query external databases and files as if they were local tables

Hybrid Search - The Best of Both Worlds:

Modern search applications need both keyword precision and semantic understanding. Cognica's unified query syntax makes this elegant:

-- Hybrid search: keyword filter + vector ranking in one query
SELECT id, title, _meta.score
FROM articles
WHERE _all @@ 'content:Python AND content_embedding:[0.1, 0.2, ...]'
ORDER BY _meta.score DESC
LIMIT 10;

-- SQL filters + vector search for precise RAG retrieval
SELECT content, source, _meta.score AS relevance
FROM knowledge_base
WHERE published_date > '2024-01-01'
  AND source_type IN ('official_docs', 'peer_reviewed')
  AND _all @@ 'content_embedding:[query_embedding...]'
ORDER BY _meta.score DESC
LIMIT 5;

This unified syntax is essential for RAG (Retrieval-Augmented Generation) applications - no complex score merging or multiple queries needed.

These extensions use PostgreSQL-compatible syntax wherever possible, so they feel natural to PostgreSQL users.

Who This Manual Is For

This manual is written for:

• Application Developers who need to write queries for their applications and want to understand how to get the best performance
• Data Analysts who want to extract insights from data and need to master complex queries
• Database Administrators who need deep understanding of how queries execute and how to optimize them
• Anyone Learning SQL who wants a thorough, friendly guide that explains concepts clearly

How to Use This Manual

Each chapter builds on previous concepts, but you can also use this as a reference to look up specific topics. Every section includes:

• Clear explanations of what each feature does and why it exists
• Practical examples you can run immediately
• Common mistakes to avoid
• Performance considerations where relevant
• Tips from real-world usage

Let's begin your journey into Cognica SQL!

---