Text-to-SQL for PostgreSQL: A Complete Guide (2026)

PostgreSQL is the world’s most widely deployed open-source relational database. It’s also one of the most complex to query well. Window functions, CTEs, JSONB columns, PostGIS geometry types, schema namespaces, partial indexes — Postgres rewards engineers who know it deeply and frustrates everyone else.

That gap is exactly where text-to-SQL steps in. This guide covers how natural language to SQL works specifically on PostgreSQL: the unique challenges the database presents, what separates systems that actually work in production from those that only work in demos, and how to get started without sending your schema to a third-party cloud.

---

What is Text-to-SQL for PostgreSQL?

Text-to-SQL (also called NL2SQL or natural language to SQL) converts a plain-English question into an executable SQL query. Instead of writing:

SELECT
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '90 days'
GROUP BY u.email
ORDER BY lifetime_value DESC
LIMIT 20;

A business user types: “Show me the top 20 new customers by total order value in the last 90 days” — and the system generates that query automatically.

This isn’t new as a concept. Researchers have worked on natural language database interfaces since the 1970s. What changed is the arrival of large language models capable of understanding intent, mapping business concepts to schema elements, and generating syntactically valid SQL reliably enough for everyday use.

PostgreSQL, specifically, has become the primary target for most serious text-to-SQL deployments — because it’s where most production data lives.

---

Why PostgreSQL Is Harder Than It Looks for AI Systems

Most text-to-SQL demos run on clean, toy databases with five tables and obvious column names. Real PostgreSQL deployments look nothing like that.

1. Schema complexity at scale

A production Postgres database might have 200, 500, or more tables spread across multiple schemas. Sending the entire schema to an LLM on every query is expensive, slow, and blows through context windows. A system needs intelligent schema pruning — identifying the handful of tables relevant to a given question before the LLM ever sees them.

This is a real architectural constraint. Savvina caps DDL payloads at 20,000 characters and limits nested STRUCT types to one level of expansion before collapsing them to STRUCT<N fields> — because fully expanding deeply nested columns like BigQuery’s ARRAY<STRUCT<>> types causes LLMs to skip annotating those columns entirely due to token limits.

2. PostgreSQL-specific SQL dialect

Postgres has a rich, non-standard SQL dialect. A system trained primarily on generic SQL will produce subtly wrong queries when it hits Postgres-specific syntax:

• INTERVAL '90 days' vs DATE_SUB(NOW(), INTERVAL 90 DAY) (MySQL)
• ILIKE for case-insensitive matching
• JSONB operators like @> and ->>
• GENERATE_SERIES() for time-series scaffolding
• Window functions with FILTER (WHERE ...) clauses
• DISTINCT ON (column) for latest-record queries
• DATE_TRUNC() for period bucketing
• Type casting with :: operator (not CAST())

A model that doesn’t understand these produces queries that look plausible but fail on execution — or worse, return wrong results silently.

A concrete example: one of the most common runtime errors in production Postgres text-to-SQL deployments is syntax error at or near where the failing token is a backtick. MySQL uses backticks for quoted identifiers; PostgreSQL uses double quotes. A generic model produces MySQL-style queries, they fail, and users see cryptic errors. The right response is to automatically detect this pattern and retry with proper double-quote escaping — which is exactly what Savvina’s execution self-correction layer does.

3. Ambiguous business terminology

The hardest part of text-to-SQL isn’t SQL generation — it’s semantic understanding. When a user asks for “active customers,” what does “active” mean in your schema? Is it a boolean column? A status enum? Customers who ordered in the last 30 days? The LLM cannot know unless you tell it.

This is why a bare LLM — including GPT-4o or Claude — will hallucinate column names and business logic when pointed at a real database. The SQL it produces is syntactically valid for a schema it invented, not yours.

4. Security surface area

SQL injection via natural language is a real threat. If a user types “show me all orders; DROP TABLE orders;” and the system naively executes what it generates, the consequences are obvious. Less obvious is what happens when someone crafts a natural language query designed to produce a CROSS JOIN — which on two 100k-row tables returns 10 billion rows and locks up the database.

A production text-to-SQL system needs multiple enforcement layers, not just convention:

• Block INSERT, UPDATE, DELETE, DROP, TRUNCATE at the validator level, not by asking the LLM nicely
• Block Postgres-specific dangerous functions: pg_read_file, lo_export, dblink_exec, pg_terminate_backend
• Block COPY ... TO and SET ROLE patterns
• Detect complexity violations (unfiltered CROSS JOINs, queries with no WHERE on large tables) and reject before execution

5. Privacy: schema metadata is sensitive

Most commercial text-to-SQL tools work by sending your database schema — table names, column names, relationships — to a cloud API. For many teams this is acceptable. For many others it isn’t.

Consider what lives in a typical Postgres schema at a fintech or healthcare company:

• Table names that reveal architecture: fraud_detection_scores, churn_risk_features
• Column names that reveal business logic: annual_contract_value, renewal_probability
• FK relationships that map your entire data model

Schema metadata is intellectual property. In regulated industries it may be subject to data residency requirements even if it’s “just column names.”

Beyond schema privacy, individual columns may be too sensitive to expose to any LLM at all — even a local one. A column named ssn, credit_card, or salary should ideally never appear in an LLM prompt, and results containing those values should never reach the user unredacted.

---

How a Production Text-to-SQL System Works on PostgreSQL

A system that handles the above reliably has several distinct layers.

Schema introspection

When you connect a text-to-SQL system to Postgres for the first time, it should introspect from catalog tables — never by scanning user data:

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  col.attname AS column_name,
  d.description
FROM pg_catalog.pg_description d
JOIN pg_catalog.pg_class c ON d.objoid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_attribute col
  ON d.objoid = col.attrelid AND d.objsubid = col.attnum
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND d.description IS NOT NULL;

Sample values — critical for teaching the LLM what enum values exist in your data — should come from pg_stats, which is populated by autovacuum and requires zero table scans:

SELECT schemaname, tablename, attname, most_common_vals::text
FROM pg_stats
WHERE schemaname = ANY($1::text[])
  AND most_common_vals IS NOT NULL;

Foreign key relationships come from information_schema.referential_constraints. Column statistics — cardinality, null fraction, correlation — from pg_stats. All of this is catalog-read only. A well-built text-to-SQL system never runs a COUNT(*), SELECT DISTINCT, or full table scan during introspection.

The semantic model

Raw schema introspection is just the starting point. On top of it, a semantic model gets built: a layer that maps business concepts to schema elements, defines column descriptions, identifies primary time axes per table, specifies common join patterns, and records example question/query pairs.

This semantic model is what the LLM reasons over on every query — not the live schema, not all 300 tables, just the business context relevant to the question being asked.

Building it requires some judgment calls. For example: a table with fiscal_year and fiscal_quarter integer columns has a fundamentally different time axis than a table with created_at TIMESTAMP. If the LLM sees created_at on a fiscal period table and uses it for date filtering, the resulting query is technically valid but semantically wrong — it’s filtering by row insert time, not by fiscal period. A good system detects this and annotates created_at on such tables as: “Row insert timestamp only — NOT a business period date. Use fiscal_year, fiscal_quarter for time-based filtering.”

Column-level privacy enforcement

Sensitive columns need two layers of protection:

1. Exclusion from LLM context: columns like ssn, salary, credit_card should never appear in the schema sent to the model. The LLM cannot reference what it doesn’t see.

2. Result masking: even for columns the LLM does know about, query results containing sensitive data should be redacted before reaching the client.

Result masking is harder than it sounds. A naive implementation would just check column names in the result set — but this breaks when the LLM wraps a sensitive column in an aggregate (COUNT(ssn) should probably not be redacted), aliases it (SELECT ssn AS id), or when a row filter injection wraps the original query in SELECT * FROM (...). A robust implementation needs to parse the SELECT list AST, identify output column aliases, skip aggregates wrapping sensitive expressions, and handle the case where the classification is uncertain (wildcard SELECT, unaliased functions) by falling back to column-name matching on the actual result columns.

Row-level security enforcement

For multi-tenant databases or fine-grained access control, a mandatory row filter can be injected at the engine layer — wrapping the generated query in a derived table:

SELECT * FROM (
  <generated query>
) AS _q WHERE tenant_id = 'acme_corp'

This happens after query generation and before execution, so it can’t be bypassed by prompt injection. LIMIT clauses are hoisted outside the subquery so the filter applies before row truncation.

Query validation

Before any generated query touches your database, it should pass through a validator that:

• Confirms it’s a SELECT (not a mutation): blocks INSERT, UPDATE, DELETE, DROP, TRUNCATE, CREATE, ALTER
• Checks for Postgres-specific dangerous functions: pg_read_file, pg_terminate_backend, lo_export, dblink, dblink_exec
• Blocks COPY ... TO patterns (data exfiltration via SQL)
• Blocks SET ROLE and SET SESSION patterns (privilege escalation)
• Optionally checks for unfiltered CROSS JOINs and missing WHERE clauses on known large tables

Execution self-correction

Even after validation, queries fail at runtime. The Postgres error messages are often informative enough for an LLM to fix them automatically:

• syntax error at or near with a backtick token → replace backticks with double quotes
• must appear in the GROUP BY clause → move the column to GROUP BY or wrap in an aggregate
• aggregate functions are not allowed in WHERE → move to HAVING
• division by zero → wrap divisor in NULLIF(expr, 0)
• is ambiguous → qualify the column with its table name, or use ordinal positions in ORDER BY
• does not exist → check column name spelling against the actual schema

A good system maintains a lookup table of these patterns and injects targeted hints into the correction prompt before asking the LLM to retry. It also re-validates the corrected query before executing it — because a self-corrected query that passes schema validation is much more likely to succeed than one that doesn’t.

Zero-result detection

A special case worth handling separately: queries that execute successfully but return zero rows. This is often a legitimate answer (“are there any overdue invoices?” → 0 rows is valid). But it’s often a bug: a date filter using a format that doesn’t match stored values, a string comparison that’s case-sensitive when the data isn’t, a JOIN condition that eliminates all rows.

When a query returns zero rows and the question wasn’t phrased as an existence check, it’s worth asking the LLM to diagnose whether the filter values match the actual data. If it identifies a likely cause and produces a corrected query, run that instead.

Semantic caching

For frequently asked questions, re-running the full LLM pipeline on every request is wasteful. A two-tier cache helps:

1. Exact match: identical questions return cached SQL instantly — no LLM call at all
2. Semantic similarity: questions with different wording but the same intent retrieve the cached query for review before re-executing

In practice this can reduce LLM calls by 40–60% within the first week of use on a team of moderate size.

---

PostgreSQL-Specific Capabilities to Look For

When evaluating any text-to-SQL system for a Postgres stack, verify it handles these correctly:

Time-series queries with GENERATE_SERIES

-- "Show daily signups for the last 30 days, including days with zero"
SELECT
  d.day::date,
  COUNT(u.id) AS signups
FROM GENERATE_SERIES(
  NOW() - INTERVAL '30 days',
  NOW(),
  '1 day'::interval
) AS d(day)
LEFT JOIN users u ON u.created_at::date = d.day::date
GROUP BY d.day
ORDER BY d.day;

A system that doesn’t know GENERATE_SERIES will produce a query that silently omits zero-count days — which is wrong but won’t throw an error.

JSONB queries

-- "Find all orders where the metadata contains a discount code"
SELECT id, total_amount
FROM orders
WHERE metadata @> '{"discount_code": true}';

DISTINCT ON for latest-record queries

-- "Get the most recent order for each customer"
SELECT DISTINCT ON (customer_id)
  customer_id,
  id AS order_id,
  created_at,
  total_amount
FROM orders
ORDER BY customer_id, created_at DESC;

Window functions

-- "Rank customers by revenue within each country"
SELECT
  customer_id,
  country,
  total_revenue,
  RANK() OVER (PARTITION BY country ORDER BY total_revenue DESC) AS country_rank
FROM customer_stats;

Postgres-native date math

DATE_TRUNC('week', CURRENT_DATE)
CURRENT_DATE - INTERVAL '30 days'
CURRENT_DATE - INTERVAL '1 month'  -- not DATE_SUB()

If a system generates MySQL or generic ANSI SQL in response to these patterns, it will fail on Postgres or return wrong results.

---

Seeing It in Practice

The two queries below are real examples generated by Savvina against an e-commerce PostgreSQL database. Both are the kind of questions that would sit in a data team’s backlog for days — not because they’re conceptually hard, but because writing them correctly requires knowing Postgres well.

“Show me the 3-month rolling average revenue by product category.”

WITH category_monthly_sales AS (
    SELECT
        DATE_TRUNC('month', o.ordered_at) AS month,
        c.name AS category_name,
        SUM(oi.line_total) AS total_category_revenue
    FROM ecommerce.orders AS o
    JOIN ecommerce.order_items AS oi ON o.id = oi.order_id
    JOIN ecommerce.products AS p ON oi.product_id = p.id
    JOIN ecommerce.categories AS c ON p.category_id = c.id
    WHERE o.status = 'delivered'
    GROUP BY DATE_TRUNC('month', o.ordered_at), c.name
)
SELECT
    cms.month,
    cms.category_name,
    cms.total_category_revenue,
    AVG(cms.total_category_revenue) OVER (
        PARTITION BY cms.category_name
        ORDER BY cms.month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_month_rolling_avg_revenue
FROM category_monthly_sales AS cms
ORDER BY cms.category_name, cms.month
LIMIT 1000;

This uses a CTE to compute monthly revenue per category, then a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to calculate the rolling average within each category partition. The ROWS BETWEEN frame specification is Postgres-native and something generic models frequently get wrong — producing RANGE BETWEEN instead, which behaves differently when there are duplicate month values.

“Which customers placed at least one order in every month of the last quarter?”

WITH last_quarter_bounds AS (
    SELECT
        DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AS quarter_start,
        DATE_TRUNC('quarter', CURRENT_DATE) AS quarter_end
),
customer_monthly_orders AS (
    SELECT
        o.customer_id,
        DATE_TRUNC('month', o.ordered_at) AS month_of_order
    FROM ecommerce.orders AS o
    JOIN last_quarter_bounds AS lqb
        ON o.ordered_at >= lqb.quarter_start AND o.ordered_at < lqb.quarter_end
    GROUP BY o.customer_id, DATE_TRUNC('month', o.ordered_at)
)
SELECT
    c.id AS customer_id,
    c.first_name,
    c.last_name,
    c.email
FROM customer_monthly_orders AS cmo
JOIN ecommerce.customers AS c ON cmo.customer_id = c.id
GROUP BY c.id, c.first_name, c.last_name, c.email
HAVING COUNT(DISTINCT cmo.month_of_order) = 3
ORDER BY c.id
LIMIT 1000;

This chains two CTEs: the first computes last quarter’s date bounds dynamically using DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' (no hardcoded dates), the second builds the customer-month activity grid. The final HAVING COUNT(DISTINCT month_of_order) = 3 filter is what makes this correct — a naive approach using COUNT(*) would overcount customers who placed multiple orders in a single month.

Both queries were generated from plain-English questions, validated against the schema, and executed without any SQL written by a human.

---

Getting Started with Savvina on PostgreSQL

Savvina is built specifically for this use case: self-hosted, privacy-first text-to-SQL that runs entirely inside your own infrastructure. Deployment takes about 15 minutes:

# 1. Clone and start Savvina
git clone https://github.com/savvina-ai/savvina
cd savvina
docker compose up -d

# 2. Open http://localhost:3000
# 3. Add your PostgreSQL connection string:
#    postgresql://user:password@host:5432/yourdb
# 4. Click "Generate semantic model"
# 5. Start asking questions in plain English

No Kubernetes. No cloud account. No data leaving your network.

What Savvina handles for PostgreSQL specifically:

• Native Postgres dialect (double-quote identifiers, INTERVAL, DATE_TRUNC, ILIKE, GENERATE_SERIES, CTEs, window functions)
• Schema introspection exclusively from catalog tables — information_schema, pg_stats, pg_catalog — no table scans
• Connection pooling via asyncpg with configurable pool sizes and SSL mode options
• Column-level privacy exclusion: mark columns so the LLM never learns they exist, with result masking that correctly handles aliases, aggregates, and row-filter-wrapped queries
• Read-only enforcement at the validator level, blocking mutations and Postgres-specific dangerous functions (pg_read_file, dblink_exec, lo_export, pg_terminate_backend, COPY ... TO)
• Execution self-correction with Postgres-aware error hints (backtick → double-quote, GROUP BY completeness, division-by-zero guards)
• Zero-result detection and automatic query refinement
• Bring-your-own-LLM: connect OpenAI, Anthropic, Mistral, or a local Ollama instance

Once the semantic model is generated, you can enrich it: add column descriptions, define what “active customer” means in your schema, mark columns that shouldn’t appear in LLM context, specify fiscal vs. audit time columns, and add example question/query pairs. The more context you give it, the more accurately it answers domain-specific questions.

---

Common Questions

Does it work with multiple Postgres schemas? Yes. Savvina introspects across all non-system schemas (pg_catalog and information_schema are excluded automatically) and lets you configure which schemas and tables are exposed to the LLM.

What happens when we add new tables or columns? Regenerating the semantic model picks up schema changes. Schema drift is detected by hashing the structural elements of the schema (table names, column names, types) — if the hash changes since the model was last generated, you’re notified that a refresh is available.

Can non-technical users actually use it? That’s the goal. Business users ask questions in plain English through the web UI; the SQL is generated, validated, and executed without them seeing it. Results come back as a table. No SQL knowledge required.

What if the generated SQL is wrong? The web UI exposes the generated SQL so technical users can inspect and correct it. Corrections can be saved as example pairs in the semantic model to improve future accuracy on similar questions.

Is it production-ready for regulated industries? The self-hosted architecture means your data never leaves your infrastructure. Column-level exclusions handle sensitive fields. Read-only enforcement with Postgres-specific validator rules protects against mutations and dangerous functions. Row-level filters handle multi-tenant isolation. Whether that satisfies your specific compliance requirements (HIPAA, GDPR, SOC 2) depends on your overall infrastructure setup — but the architecture is designed with that use case in mind.

---

Where to Go Next

• Introducing Savvina — how the semantic cache and privacy architecture work in detail
• The Rise of Self-Hosted AI Analytics — why enterprises are moving away from cloud AI APIs for data intelligence
• Text-to-SQL vs Traditional BI — how to think about where text-to-SQL fits alongside your existing BI tools
• GitHub Community — ask questions, share schemas, and see how other Postgres teams are deploying

The community edition is free, self-hosted, and Apache 2.0 licensed.