Text-to-SQL in PostgreSQL with pg_sqlgen
November 16, 2026
Introduction
During my time as CTO of Mercator, we published Dubo-SQL, which reached #1 on execution accuracy on the BIRD-SQL leaderboard. When the company shut down, I wanted to put what we had learned to use. That led to pg_sqlgen, a text-to-SQL extension for PostgreSQL.
How it works
Database metadata table
Text-to-SQL generation requires schema metadata to match natural language requests to relevant columns. To achieve this, the extension maintains a metadata table with one row per column in the configured schemas. These rows contain column names, types, and comments, along with embeddings of the column definitions and comments.
DDL event triggers are leveraged to keep the metadata up to date. When the schema is modified, the triggers update the affected metadata and regenerate embeddings as needed.
Generation pipeline
Relevant columns
The process starts by determining columns relevant to the request. There are two filtering modes, smart and quick. Smart filtering sends column definitions and the request to an LLM, which selects the columns it considers relevant. Quick filtering embeds the request and compares it with the column-definition embeddings using cosine distance to find the closest matches.
Statement generation
Once the relevant columns have been selected, an LLM is asked to generate an SQL statement. The prompt includes the original request, the selected column definitions, and similar saved queries when available.
Relevant examples can improve accuracy, as our Dubo-SQL experiments showed. They are often scarce because users don't generally maintain them manually. However, this feature works well with agents that frequently access a database. Agents are encouraged to collect examples after checking that a generated statement produced the expected result. These question-and-query pairs can be saved with sqlgen.certify_query() and retrieved for similar requests.
The model then returns the SQL statement it believes represents the user's request.
Error detection and validation
There is no guarantee that generated statements work. Fortunately, PostgreSQL provides tools useful for validating queries. First, pg_parse_query detects syntax errors and identifies the statement type. A single SELECT, INSERT, UPDATE, or DELETE statement also goes through PREPARE, which checks whether PostgreSQL can resolve the referenced tables, columns, and types.
Any errors are sent back to the LLM with the generated SQL and relevant column definitions. The model produces a replacement, which goes through validation again. This repeats until the statement passes or the correction round limit is reached. If validation still fails, the function returns an error.
Judgment and counterexamples
The final step is judgment, which checks whether the generated statement accurately reflects the user's question. The LLM receives the original question, generated SQL, an EXPLAIN plan, relevant column definitions, and any retrieved examples.
If the judge accepts the statement, it is returned to the user. Otherwise, the judge supplies a replacement query, called a counterexample. This goes through error correction and validation before being returned.
Why an extension?
An extension keeps schema lookup, embedding search, and SQL validation inside PostgreSQL. Direct access through SPI avoids the extra database connections and network round trips that an external service would need. Calls to the model still have their own network and inference costs.
Data locality is another consideration. Metadata, embeddings, and saved examples remain stored in PostgreSQL, so there is no separate metadata service or vector database to maintain. Requests still send prompts and supporting context to the configured models. Whether that information leaves the environment depends on where those models run.
The extension also fits into PostgreSQL's authorization model. Generated queries execute with the calling role's privileges, so generating SQL does not grant access to otherwise restricted data. Access to stored schema metadata is controlled separately and is not automatically filtered by each caller's table permissions.
Consistency is another reason to keep this in the database. An external service needs a way to synchronize its schema representation with PostgreSQL. For the DDL operations covered by its event triggers, pg_sqlgen updates metadata in the same transaction as the schema change.
Text-to-SQL often sits behind chat interfaces such as Claude or ChatGPT, or inside tools used by agents. An extension gives those systems access to maintained schema metadata, saved examples, and validation through a database connection. They can reuse that process without having to rediscover the schema and construct the entire generation pipeline for each request.
How to use it
The source is available on GitHub. Prebuilt x86-64 packages for Debian, Ubuntu, Fedora, and RHEL are available on the releases page.
The installation guide covers package installation and building from source. The development guide explains how to set up a development environment. Usage examples cover setup, generating SQL, and executing generated queries.
