Appendix B: SQL Compatibility Reference

This appendix provides a comprehensive reference for SQL compatibility in Cognica, documenting supported statements, data types, operators, functions, and PostgreSQL compatibility features. Cognica implements a substantial subset of PostgreSQL SQL dialect, enabling compatibility with most PostgreSQL client applications and tools.

B.1 Statement Support Matrix

B.1.1 Data Query Language (DQL)

SELECT Clause Features:

-- Standard SELECT
SELECT column1, column2 FROM table_name;

-- SELECT with expressions
SELECT id, price * quantity AS total FROM orders;

-- DISTINCT
SELECT DISTINCT category FROM products;

-- DISTINCT ON (PostgreSQL extension)
SELECT DISTINCT ON (department) employee_id, name, salary
FROM employees ORDER BY department, salary DESC;

-- Subquery in SELECT
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = c.id)
FROM customers c;

-- Parameterized LIMIT/OFFSET (useful in prepared statements)
SELECT id FROM users LIMIT $1 + 1 OFFSET $2;

B.1.2 Data Manipulation Language (DML)

DML Examples:

-- INSERT with VALUES
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- INSERT with SELECT
INSERT INTO archive SELECT * FROM logs WHERE created_at < '2024-01-01';

-- INSERT with RETURNING
INSERT INTO orders (product_id, quantity) VALUES (1, 5)
RETURNING id, created_at;

-- UPDATE with RETURNING
UPDATE products SET price = price * 1.1 WHERE category = 'electronics'
RETURNING id, name, price;

-- DELETE with RETURNING
DELETE FROM sessions WHERE expires_at < NOW() RETURNING user_id;

-- INSERT ON CONFLICT (upsert)
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

-- INSERT ON CONFLICT DO NOTHING
INSERT INTO tags (name) VALUES ('important')
ON CONFLICT (name) DO NOTHING;

-- COPY for bulk operations
COPY users TO '/tmp/users.csv' WITH (FORMAT CSV, HEADER);
COPY users FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER);

B.1.3 Data Definition Language (DDL)

DDL Examples:

-- CREATE TABLE with constraints
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    total DECIMAL(10,2) CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (customer_id, created_at)
);

-- ALTER TABLE operations
ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders DROP COLUMN notes;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total > 0);
ALTER TABLE orders RENAME COLUMN total TO order_total;
ALTER TABLE orders RENAME TO customer_orders;

-- CREATE INDEX
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

-- CREATE VIEW
CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total) AS revenue,
       COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- CREATE SEQUENCE
CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;

-- CREATE TRIGGER
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

B.1.4 Transaction Control

Transaction Examples:

-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction with savepoint
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 1);
-- Error occurs, rollback to savepoint
ROLLBACK TO SAVEPOINT order_created;

-- Try different item
INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 2);
COMMIT;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Transaction with serializable isolation
COMMIT;

B.1.5 Access Control

Access Control Examples:

-- Create role with login
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT USAGE ON SEQUENCE orders_id_seq TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- Row-level security
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_isolation ON orders
    USING (customer_id = current_setting('app.current_customer')::BIGINT);

-- Grant role membership
GRANT admin TO app_user;

B.2 Data Type Reference

B.2.1 Numeric Types

Numeric Type Notes:

• All integer types are stored as 64-bit integers internally for uniformity
• NUMERIC/DECIMAL are stored as double-precision floating point
• SERIAL types automatically create associated sequences

-- Numeric type usage
CREATE TABLE metrics (
    id BIGSERIAL PRIMARY KEY,
    count INTEGER NOT NULL DEFAULT 0,
    rate DOUBLE PRECISION,
    amount NUMERIC(10, 2)
);

B.2.2 Character Types

Character Type Notes:

• All character types stored as UTF-8 strings internally
• Length constraints enforced at input time
• CHAR pads with spaces to specified length

-- Character type usage
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    code CHAR(10)
);

B.2.3 Date/Time Types

Date/Time Type Notes:

• TIMESTAMP WITHOUT TIME ZONE stores local time
• TIMESTAMPTZ stores UTC, converts on display
• INTERVAL supports year-month and day-time components

-- Date/time type usage
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_date DATE NOT NULL,
    start_time TIME,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    duration INTERVAL
);

-- Date/time literals
SELECT DATE '2024-01-15';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2024-01-15 14:30:00';
SELECT INTERVAL '2 hours 30 minutes';

B.2.4 Boolean Type

-- Boolean usage
CREATE TABLE features (
    name VARCHAR(100) PRIMARY KEY,
    enabled BOOLEAN DEFAULT FALSE,
    visible BOOLEAN DEFAULT TRUE
);

SELECT * FROM features WHERE enabled AND visible;

B.2.5 Binary Types

-- Binary type usage
CREATE TABLE files (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    content BYTEA
);

-- Insert binary data (hex format)
INSERT INTO files (name, content) VALUES ('test', '\x48454C4C4F');

B.2.6 JSON Types

JSON Type Features:

• Full JSONPath support with @? and @@ operators
• JSONB indexing for efficient queries
• Nested object and array access

-- JSON type usage
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    metadata JSON
);

-- JSON queries
SELECT data->'name' FROM documents;                    -- JSON value
SELECT data->>'name' FROM documents;                   -- Text value
SELECT data#>'{address,city}' FROM documents;          -- Path access
SELECT * FROM documents WHERE data @> '{"active": true}';
SELECT * FROM documents WHERE data ? 'email';
SELECT * FROM documents WHERE data @@ '$.price > 100';

B.2.7 Special Types

-- UUID usage
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Array usage
CREATE TABLE tags (
    id BIGSERIAL PRIMARY KEY,
    labels TEXT[] NOT NULL
);

B.2.8 ENUM Types

User-defined enumeration types with automatic vectorized analysis:

-- Create an ENUM type
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

-- Use in table
CREATE TABLE journal (
    id BIGSERIAL PRIMARY KEY,
    entry_date DATE NOT NULL,
    current_mood mood NOT NULL
);

-- Enum comparison follows definition order
SELECT * FROM journal WHERE current_mood > 'ok';  -- Returns 'happy' entries

B.2.9 Range Types

PostgreSQL-compatible range types for representing intervals:

-- Range type usage
CREATE TABLE reservations (
    id BIGSERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    during TSRANGE NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- Range operators
SELECT * FROM reservations WHERE during @> NOW()::timestamp;
SELECT * FROM reservations WHERE during && '[2026-01-01, 2026-02-01)';
SELECT lower(during), upper(during) FROM reservations;

INSERT INTO tags (labels) VALUES (ARRAY['important', 'urgent']);
SELECT * FROM tags WHERE 'urgent' = ANY(labels);

B.3 Operator Reference

B.3.1 Arithmetic Operators

B.3.2 Comparison Operators

B.3.3 Logical Operators

Three-Valued Logic:

B.3.4 Pattern Matching Operators

Pattern Wildcards:

B.3.5 String Operators

B.3.6 JSON/JSONB Operators

JSON Operator Examples:

-- Object field access
SELECT data->'address'->>'city' FROM customers;

-- Path access
SELECT data#>'{items,0,name}' FROM orders;

-- Containment queries
SELECT * FROM products WHERE specs @> '{"color": "red"}';

-- Key existence
SELECT * FROM documents WHERE data ? 'email';

-- JSONPath queries
SELECT * FROM products WHERE data @@ '$.price < 100';
SELECT * FROM products WHERE data @? '$.reviews[*] ? (@.rating > 4)';

B.3.7 Array Operators

B.3.8 Full-Text Search Operators

B.4 Function Reference

B.4.1 Mathematical Functions

Trigonometric Functions:

B.4.2 String Functions

Function Aliases:

Regular Expression Functions:

B.4.3 Date/Time Functions

Extract/Date_Part Units:

B.4.4 JSON Functions

JSON Set-Returning Functions:

B.4.5 Array Functions

B.4.6 Conditional Functions

CASE Expression:

-- Simple CASE
SELECT CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END FROM items;

-- Searched CASE
SELECT CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 100 THEN 'Moderate'
    ELSE 'Expensive'
END FROM products;

B.4.7 Type Conversion Functions

B.4.8 System Functions

B.4.9 Sequence Functions

B.5 Aggregate Functions

B.5.1 General-Purpose Aggregates

B.5.2 Statistical Aggregates

B.5.3 Regression Aggregates

B.5.4 Ordered-Set Aggregates

-- Median calculation
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
FROM employees;

-- Mode calculation
SELECT MODE() WITHIN GROUP (ORDER BY department)
FROM employees;

B.5.5 Hypothetical-Set Aggregates

B.5.6 String and Array Aggregates

-- String aggregation
SELECT department, STRING_AGG(name, ', ' ORDER BY name)
FROM employees GROUP BY department;

-- Array aggregation
SELECT customer_id, ARRAY_AGG(product_id ORDER BY order_date)
FROM orders GROUP BY customer_id;

B.5.7 JSON Aggregates

B.5.8 Bitwise Aggregates

B.5.9 Aggregate Modifiers

FILTER Clause:

-- Conditional aggregation
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    SUM(amount) FILTER (WHERE type = 'credit') AS total_credits
FROM transactions;

DISTINCT in Aggregates:

-- Count distinct values
SELECT COUNT(DISTINCT category) FROM products;

-- Sum distinct values
SELECT SUM(DISTINCT price) FROM products;

B.6 Window Functions

B.6.1 Ranking Functions

-- Ranking example
SELECT name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- Ranking within partitions
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

B.6.2 Value Functions

-- Compare with previous/next row
SELECT date, value,
    LAG(value, 1, 0) OVER (ORDER BY date) AS prev_value,
    LEAD(value, 1, 0) OVER (ORDER BY date) AS next_value,
    value - LAG(value, 1, 0) OVER (ORDER BY date) AS change
FROM metrics;

-- First and last values
SELECT date, category, value,
    FIRST_VALUE(value) OVER w AS first_val,
    LAST_VALUE(value) OVER w AS last_val
FROM sales
WINDOW w AS (PARTITION BY category ORDER BY date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

B.6.3 Aggregate Window Functions

All standard aggregates can be used as window functions:

-- Running totals
SELECT date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM transactions;

-- Partition aggregates
SELECT department, name, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

B.6.4 Window Frame Specification

Frame Types:

Frame Bounds:

Frame Exclusion:

-- Various frame specifications
SELECT value,
    -- Last 3 rows including current
    SUM(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),

    -- All rows with same or lower value
    SUM(value) OVER (ORDER BY value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),

    -- Excluding current row
    AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                     EXCLUDE CURRENT ROW)
FROM data;

B.6.5 Named Windows

SELECT name, department, salary,
    RANK() OVER w AS rank,
    SUM(salary) OVER w AS running_total,
    AVG(salary) OVER w AS running_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

B.7 Table-Valued Functions

B.7.1 Series Generation

-- Integer series
SELECT * FROM generate_series(1, 10);           -- 1 to 10
SELECT * FROM generate_series(1, 10, 2);        -- 1, 3, 5, 7, 9
SELECT * FROM generate_series(10, 1, -1);       -- 10 down to 1

-- Timestamp series
SELECT * FROM generate_series(
    '2024-01-01'::timestamp,
    '2024-01-07'::timestamp,
    '1 day'::interval
);

B.7.2 Array Expansion

-- Unnest array
SELECT unnest(ARRAY[1, 2, 3]);

-- Unnest with ordinality
SELECT * FROM unnest(ARRAY['a', 'b', 'c']) WITH ORDINALITY AS t(value, idx);

B.7.3 JSON Expansion

-- Expand JSON array
SELECT * FROM jsonb_array_elements('[1, 2, 3]'::jsonb);
SELECT * FROM jsonb_array_elements_text('["a", "b", "c"]'::jsonb);

-- Expand JSON object
SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);
SELECT * FROM jsonb_each_text('{"a": "x", "b": "y"}'::jsonb);

-- Get object keys
SELECT * FROM jsonb_object_keys('{"a": 1, "b": 2}'::jsonb);

B.7.4 Regular Expression Expansion

-- Extract all matches
SELECT * FROM regexp_matches('abc123def456', '\d+', 'g');

-- Split string to rows
SELECT * FROM regexp_split_to_table('a1b2c3', '\d');
SELECT * FROM string_to_table('a,b,c', ',');

B.8 JOIN Support

B.8.1 Supported JOIN Types

B.8.2 JOIN Syntax

-- Explicit join condition
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- USING clause (same column name)
SELECT * FROM orders
JOIN customers USING (customer_id);

-- NATURAL join (all common columns)
SELECT * FROM orders
NATURAL JOIN order_items;

-- Multiple joins
SELECT o.id, c.name, p.name AS product
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- Left join with null handling
SELECT c.name, COALESCE(SUM(o.total), 0) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

B.8.3 LATERAL Joins

-- LATERAL subquery can reference outer tables
SELECT c.name, recent.order_date, recent.total
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_date, total
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 3
) recent ON true;

-- LATERAL with table function
SELECT e.name, t.tag
FROM entities e
LEFT JOIN LATERAL unnest(e.tags) AS t(tag) ON true;

B.9 Subquery Support

B.9.1 Scalar Subqueries

-- In SELECT clause
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
FROM customers c;

-- In WHERE clause
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

B.9.2 Table Subqueries

-- Derived table
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 50000;

-- With column aliases
SELECT * FROM (
    SELECT id, name, salary
    FROM employees
) AS e(emp_id, emp_name, emp_salary);

B.9.3 EXISTS and IN Subqueries

-- EXISTS (semi-join)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- NOT EXISTS (anti-join)
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- IN subquery
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = true
);

B.9.4 Correlated Subqueries

-- Correlated scalar subquery
SELECT e.name, e.salary,
       (SELECT AVG(salary) FROM employees e2
        WHERE e2.department = e.department) AS dept_avg
FROM employees e;

-- Correlated EXISTS
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id AND e.salary > 100000
);

B.10 Common Table Expressions (CTE)

B.10.1 Basic CTE

-- Single CTE
WITH active_customers AS (
    SELECT * FROM customers WHERE status = 'active'
)
SELECT * FROM active_customers WHERE created_at > '2024-01-01';

-- Multiple CTEs
WITH
    active_customers AS (
        SELECT * FROM customers WHERE status = 'active'
    ),
    recent_orders AS (
        SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
    )
SELECT c.name, COUNT(o.id) AS recent_order_count
FROM active_customers c
LEFT JOIN recent_orders o ON c.id = o.customer_id
GROUP BY c.name;

B.10.2 Recursive CTE

-- Hierarchical query (org chart)
WITH RECURSIVE org_tree AS (
    -- Base case: top-level employees
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

-- Generate sequence
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT * FROM nums;

B.10.3 CTE Materialization Hints

-- Force materialization
WITH active_users AS MATERIALIZED (
    SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users a1
JOIN active_users a2 ON a1.department = a2.department;

-- Prevent materialization (inline)
WITH user_stats AS NOT MATERIALIZED (
    SELECT user_id, COUNT(*) AS cnt FROM events GROUP BY user_id
)
SELECT * FROM user_stats WHERE cnt > 10;

B.11 Set Operations

B.11.1 UNION

-- Remove duplicates
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- Keep duplicates
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

B.11.2 INTERSECT

-- Common elements
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

-- With ALL
SELECT product_id FROM warehouse_a
INTERSECT ALL
SELECT product_id FROM warehouse_b;

B.11.3 EXCEPT

-- Elements in first but not second
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM active_customers;

-- With ALL
SELECT item_id FROM inventory
EXCEPT ALL
SELECT item_id FROM sold_items;

B.11.4 Combining Set Operations

-- Complex set operations
(SELECT id FROM table_a
 UNION
 SELECT id FROM table_b)
INTERSECT
SELECT id FROM table_c
ORDER BY id;

B.12 GROUP BY Extensions

B.12.1 GROUPING SETS

-- Multiple grouping configurations
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS (
    (region, product),  -- By region and product
    (region),           -- By region only
    (product),          -- By product only
    ()                  -- Grand total
);

B.12.2 ROLLUP

-- Hierarchical grouping
SELECT year, quarter, month, SUM(revenue)
FROM sales
GROUP BY ROLLUP (year, quarter, month);
-- Produces: (year, quarter, month), (year, quarter), (year), ()

B.12.3 CUBE

-- All combinations
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY CUBE (region, product);
-- Produces: (region, product), (region), (product), ()

B.12.4 GROUPING Function

-- Identify grouping level
SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region,
    CASE WHEN GROUPING(product) = 1 THEN 'All Products' ELSE product END AS product,
    SUM(sales)
FROM sales_data
GROUP BY CUBE (region, product);

B.13 PostgreSQL System Catalog Compatibility

B.13.1 pg_catalog Tables

Cognica implements the following PostgreSQL system catalog tables:

B.13.2 information_schema Tables

B.13.3 System Catalog Queries

-- List all tables
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

-- Get column information
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

-- List foreign keys
SELECT tc.constraint_name, tc.table_name, kcu.column_name,
       ccu.table_name AS foreign_table, ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

-- Get table size statistics
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables;

B.14 EXPLAIN Output

B.14.1 Basic EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Output shows:

• Execution plan tree
• Estimated costs (startup and total)
• Estimated row counts
• Access methods used

B.14.2 EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Additional information:

• Actual execution time
• Actual row counts
• Number of loops
• Memory usage

B.14.3 EXPLAIN VERBOSE

EXPLAIN VERBOSE SELECT * FROM orders WHERE customer_id = 123;

Additional details:

• Output column lists
• Schema-qualified names
• Detailed cost breakdown

B.15 Limitations and Differences

B.15.1 Features Not Supported

B.15.2 Behavioral Differences

B.15.3 Extensions

Cognica includes features not in standard PostgreSQL:

B.16 Graph Query Support

Cognica provides property graph capabilities through two complementary interfaces: SQL table functions for programmatic graph operations, and the openCypher query language for declarative graph pattern matching.

B.16.1 Graph Table Functions

Graph operations are exposed as SQL table functions that can be composed with standard SQL clauses including JOINs, WHERE filters, and aggregations.

Graph Management:

Node Operations:

Edge Operations:

Traversal Functions:

Graph Table Function Examples:

-- Create a graph and add data
SELECT * FROM graph_create('social');
SELECT * FROM graph_create_node('social', 'Person', '{"name": "Alice", "age": 30}');
SELECT * FROM graph_create_node('social', 'Person', '{"name": "Bob", "age": 25}');
SELECT * FROM graph_create_edge('social', 'KNOWS', 'alice_id', 'bob_id',
                                 '{"since": 2024}');

-- Query nodes by label
SELECT * FROM graph_nodes('social', 'Person');

-- Update node properties
SELECT * FROM graph_update_node('social', 'alice_id', '{"age": 31}');

-- Update edge properties using LATERAL join
SELECT u.success FROM social_edges e
CROSS JOIN LATERAL graph_update_edge(
    'social', e._id, '{"since": 2025}'
) AS u(success)
WHERE e._type = 'KNOWS';

-- Traverse the graph
SELECT * FROM graph_traverse('social', 'alice_id', 'KNOWS', 'outgoing', 3);

-- Find shortest path
SELECT * FROM graph_shortest_path('social', 'alice_id', 'bob_id');

B.16.2 Cypher Query Language

Cognica supports the openCypher query language through the cypher() table function in FROM clauses. Cypher queries are transparently rewritten into optimized SQL subqueries at parse time, allowing full integration with the SQL query planner and optimizer.

Syntax:

SELECT <columns>
FROM cypher('<graph_name>', $$ <cypher_query> $$) AS (<column_definitions>)

Supported Cypher Clauses:

Node Pattern Matching:

-- Match nodes by label
SELECT * FROM cypher('social', $$
    MATCH (p:Person)
    RETURN p.name AS name, p.age AS age
$$) AS (name text, age bigint);

-- Match with property filter
SELECT * FROM cypher('social', $$
    MATCH (p:Person {name: 'Alice'})
    RETURN p.age AS age
$$) AS (age bigint);

Relationship Pattern Matching:

-- Directed relationship
SELECT * FROM cypher('social', $$
    MATCH (a:Person)-[r:KNOWS]->(b:Person)
    RETURN a.name AS source, b.name AS target
$$) AS (source text, target text);

-- Variable-length relationships
SELECT * FROM cypher('social', $$
    MATCH (a:Person {name: 'Alice'})-[:KNOWS*1..3]->(b:Person)
    RETURN b.name AS name
$$) AS (name text);

-- Path variables with nodes() and relationships()
SELECT * FROM cypher('social', $$
    MATCH p = (a:Person {name: 'Alice'})-[:KNOWS*1..3]->(b:Person)
    RETURN nodes(p) AS path_nodes, relationships(p) AS path_edges,
           length(p) AS hops
$$) AS (path_nodes jsonb, path_edges jsonb, hops bigint);

Graph Mutation:

-- Create nodes
SELECT * FROM cypher('social', $$
    CREATE (p:Person {name: 'Carol', age: 28})
    RETURN p.name AS name
$$) AS (name text);

-- Create relationships
SELECT * FROM cypher('social', $$
    MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Carol'})
    CREATE (a)-[:KNOWS {since: 2025}]->(b)
    RETURN a.name AS source, b.name AS target
$$) AS (source text, target text);

-- Update properties
SELECT * FROM cypher('social', $$
    MATCH (p:Person {name: 'Alice'})
    SET p.age = 31
    RETURN p.name AS name, p.age AS age
$$) AS (name text, age bigint);

-- Merge (match or create)
SELECT * FROM cypher('social', $$
    MERGE (p:Person {name: 'Dave'})
    ON CREATE SET p.age = 35
    ON MATCH SET p.last_seen = timestamp()
    RETURN p.name AS name
$$) AS (name text);

Cypher Functions:

Integration with SQL:

Cypher queries integrate naturally with SQL through the FROM clause, enabling hybrid queries that combine graph pattern matching with relational operations:

-- Join Cypher results with relational tables
SELECT c.*, o.total
FROM cypher('social', $$
    MATCH (p:Person)-[:LIVES_IN]->(c:City)
    RETURN p.name AS person_name, c.name AS city
$$) AS c(person_name text, city text)
JOIN orders o ON o.customer_name = c.person_name;

-- Use CTE with Cypher
WITH friends AS (
    SELECT * FROM cypher('social', $$
        MATCH (a:Person {name: 'Alice'})-[:KNOWS]->(f:Person)
        RETURN f.name AS name
    $$) AS (name text)
)
SELECT * FROM friends ORDER BY name;

B.17 SQL Conformance

B.17.1 SQL Standard Features

Cognica supports the following SQL standard features:

B.17.2 PostgreSQL Extensions

Summary

Cognica provides comprehensive SQL support compatible with PostgreSQL, enabling seamless migration of applications and queries. The implementation covers:

• Full DQL support: SELECT with all clauses, joins, subqueries, CTEs, parameterized LIMIT/OFFSET
• Complete DML: INSERT (with ON CONFLICT upsert), UPDATE, DELETE with RETURNING
• Extensive DDL: Tables, views, indexes (including partial), triggers, functions, schemas
• Transaction control: ACID compliance with isolation levels
• Rich function library: 200+ scalar, aggregate, and window functions
• PostgreSQL compatibility: System catalogs, operators, extensions
• Graph queries: Property graph operations via SQL table functions and openCypher language

Understanding these compatibility features enables developers to leverage existing PostgreSQL knowledge while taking advantage of Cognica's unique capabilities in document storage, full-text search, vector similarity operations, and graph query processing.