Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Examples

pg_sqlgen works best with OpenAI models. For Ollama, it’s been tested with Llama 3.1 and nomic-embed-text, though other models may provide better output.

Setup with OpenAI

OpenAI keys can be generated here.

-- Create the extension.
CREATE EXTENSION sqlgen CASCADE;

-- Create an instruct model.
SELECT sqlgen.add_model(
    'openai_chat',
    sqlgen.openai_completions_config(api_key => '<your OpenAI API key>')
);

-- Create an encoding model.
SELECT sqlgen.add_model(
    'openai_embed',
    sqlgen.openai_embed_config(api_key => '<your OpenAI API key>')
);

-- Create an engine
SELECT sqlgen.create_engine('example_engine', 'openai_chat', 'openai_embed');

-- Set engine as default
SELECT sqlgen.set_default_engine('example_engine');

-- Execute a generated query
BEGIN;
    SELECT sqlgen.query('Get highest paying customer');
    FETCH ALL FROM sqlgen_query;
COMMIT;

Setup with Ollama

Set up nomic-embed-text and llama3.1 with Ollama.

Start Ollama in a separate console, or via the app.

$ ollama serve

Set up nomic-embed-text and llama3.1.

ollama pull nomic-embed-text
ollama pull llama3.1

Now setup the database extension.

-- Create the extension.
CREATE EXTENSION sqlgen CASCADE;

-- Create an instruct model.
SELECT sqlgen.add_model(
    'ollama_instruct',
    sqlgen.ollama_config(model_name => 'llama3.1')
);

-- Create an encoding model.
SELECT sqlgen.add_model(
    'ollama_embed',
    sqlgen.openai_embeddings_config(model_name => 'nomic-embed-text')
);

-- Create an engine
SELECT sqlgen.create_engine('example_engine', 'ollama_instruct', 'ollama_embed');

-- Set engine as default
SELECT sqlgen.set_default_engine('example_engine');

-- Execute a generated query
BEGIN;
    SELECT sqlgen.query('Get highest paying customer');
    FETCH ALL FROM sqlgen_query;
COMMIT;