Ask Your PostgreSQL Database in Plain English

Every company with a Postgres database has the same bottleneck: one or two people who can write the query, and everyone else waiting on them. A PostgreSQL natural language query system exists to remove that queue. You type "how many accounts signed up last month and never activated," and something translates that into SQL, runs it, and hands back rows.

The translation is the easy part. Getting the right answer is hard, and the failure mode is nasty: the query runs, returns a clean number, and the number is wrong. No error, no warning, just a plausible figure that quietly walks into a board deck.

This article is about how the mechanism actually works against Postgres, where it breaks, how to make it safe by construction rather than by good intentions, and what habits separate a useful answer from a confident guess.

How a PostgreSQL natural language query actually works

There is no magic layer. Every system doing this, whether it is a chat product, an open-source library, or something you built yourself, runs roughly the same four stages.

Stage one: schema introspection

Before a model can write SQL, it needs to know what exists. That means reading the catalog, not guessing. A competent implementation pulls:

• Tables and columns with their data types, from information_schema.columns or pg_attribute.
• Primary and foreign keys from pg_constraint, because foreign keys are the only reliable machine-readable statement of how tables relate.
• Comments from obj_description and col_description, which is why the COMMENT ON statements you never wrote suddenly matter.
• Approximate row counts from pg_class.reltuples, so it knows whether a table has 400 rows or 400 million.
• Distinct values for low-cardinality columns, so the model knows that status is one of trialing, active, past_due, canceled rather than inventing cancelled with two Ls.

That last one is underrated. Most wrong answers in practice are not join errors, they are filter errors on a value the model never saw.

Stage two: narrowing the schema

Real Postgres databases have hundreds of tables. You cannot put all of them in a prompt, and even if you could, more schema makes the model worse, not better. So the system retrieves a subset: it matches your question against table names, column names, and comments, and assembles a working set.

This is where the first silent failure lives. If your database contains users, auth.users, legacy_users, and users_v2, retrieval will pick one, and it may not pick the one your finance team considers canonical. The fix is not a better model. The fix is not exposing the dead tables to the query role in the first place.

Stage three: generating the SQL

The model writes Postgres-flavored SQL. Good output uses the dialect properly: date_trunc('month', ...) for period grouping, aggregate FILTER (WHERE ...) clauses instead of nested CASE expressions, DISTINCT ON for latest-row-per-group, generate_series to fill empty date buckets so a month with zero signups shows a zero instead of vanishing from the result.

Dialect competence is table stakes now. Join correctness is not.

Stage four: execution and presentation

The SQL runs against a connection, results come back, and you see a table. The question that decides whether this is a tool or a liability: do you also see the SQL? If the query is hidden, you have no way to audit the answer, and you should treat the number as a rumor.

The joins and filters models get wrong

These are the recurring failures. They are worth memorizing, because spotting them takes seconds once you know the shape.

Fan-out and double counting

You ask for total revenue by customer. The model joins orders to order_items to get product categories, then sums orders.total. Every order with three line items now contributes its total three times. The result looks like a real revenue number, just inflated.

The tell is a total that is suspiciously high, or a SUM on a column belonging to the parent table of a one-to-many join. The fix is aggregating in a CTE before joining, or summing the child-level amount instead.

LEFT JOIN silently becoming INNER JOIN

The model writes a LEFT JOIN to subscriptions so accounts without a subscription are included, then adds WHERE subscriptions.status = 'active' in the outer WHERE clause. Any row where the join produced NULLs is now filtered out. The LEFT JOIN is decorative. The condition belongs in the ON clause, or the filter needs OR subscriptions.id IS NULL.

NOT IN against a nullable column

WHERE account_id NOT IN (SELECT account_id FROM churned) returns zero rows the moment that subquery contains a single NULL, because x NOT IN (1, NULL) evaluates to NULL rather than true. Postgres is doing exactly what SQL requires. The result is an empty set that reads as "no such accounts exist." NOT EXISTS or an anti-join does not have this behavior.

The wrong grain of time

"Last month" is ambiguous. Calendar month or trailing 30 days? And in whose time zone? If your column is timestamp without time zone storing UTC, date_trunc('month', created_at) buckets by UTC, so a signup at 7pm on January 31 in Los Angeles lands in January for the model and February for your sales team. In Postgres the correct pattern for a local calendar is date_trunc('month', created_at AT TIME ZONE 'America/New_York'). Models rarely apply it unless the schema or your question tells them to.

Soft deletes, test accounts, and internal orgs

Almost every mature schema carries rows that should never appear in a business answer: deleted_at IS NOT NULL, is_test = true, internal organizations used by your own staff, refunded orders that still sit in the table. Unless those exclusions are encoded somewhere the model can read, they will not be applied, and your counts will run high forever in a consistent, believable way.

Money and units

Stripe-derived tables usually store amounts in the smallest currency unit. If amount is cents and the model reports it as dollars, you are off by two orders of magnitude. Mixed-currency tables are worse: summing amount across USD, EUR, and GBP rows produces a number with no meaning at all. If you work with payments data, the same discipline applies whether you are reading the API or the database, which we go deeper on in Stripe revenue analytics.

PostgreSQL natural language query safety belongs in the database

The common instinct is to write a strong instruction: "only generate SELECT statements, never modify data." That is a preference, not a control. Prompts are not a permission system, and a system that relies on one has no answer for the day a question is phrased strangely or a string ends up somewhere it should not.

Put the guardrails where they cannot be talked out of.

A dedicated read-only role

Create a role that exists only for this purpose, grant it the minimum, and set it read-only at the session level so even a well-formed write is rejected by the server.

CREATE ROLE analytics_ro LOGIN PASSWORD 'use-a-secret-manager';
GRANT CONNECT ON DATABASE app TO analytics_ro;
GRANT USAGE ON SCHEMA public TO analytics_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_ro;
ALTER ROLE analytics_ro SET default_transaction_read_only = on;
ALTER ROLE analytics_ro SET statement_timeout = '30s';
ALTER ROLE analytics_ro SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE analytics_ro CONNECTION LIMIT 5;

Two details people miss. First, GRANT SELECT ON ALL TABLES applies only to tables that exist right now; new tables created next week are not covered unless you also set ALTER DEFAULT PRIVILEGES. Decide deliberately whether you want new tables auto-exposed or not. Second, ALTER DEFAULT PRIVILEGES only affects objects created by the role that runs it, which is why the grants "mysteriously stop working" when a different migration user ships the next table.

If you are on Postgres 15 or later, note that PUBLIC no longer holds CREATE on the public schema by default, which removes one historical footgun for you.

Everything else worth doing

The pattern to internalize: assume the generated SQL is untrusted input, because it is. Then it does not matter how the query was produced.

Verification habits that make answers trustworthy

Safety keeps the database intact. Verification keeps the answers honest. These six habits cost very little and catch nearly everything.

Read the SQL, always. Not line by line. Scan for the table names, the join type, the WHERE clause, and the date range. Ninety percent of errors are visible in that scan.

Reconcile against one number you already know. Before you trust a new query, ask it something you can independently confirm: last month's total, a specific customer's count, yesterday's signups. If it matches your existing report, the join path is probably sound. If it does not, you have found either a bug or a definition disagreement, and both are worth knowing.

Check the grain. Ask yourself what one row represents. One customer? One subscription? One invoice line? Most bad analysis comes from a result set whose grain is not what the reader assumed.

Ask the same question two ways. "How many accounts churned in June" and "how many subscriptions moved to canceled in June" should produce related numbers with an explainable difference. If they are wildly apart, one of them is wrong.

Make the filters explicit. Ask the system to state the exact date range and exclusions it applied. "Between 2026-06-01 and 2026-06-30 UTC, excluding test accounts" is auditable. "Last month" is not.

Keep a library of blessed queries. Once a query has been reviewed and reconciled, save it and reuse it. Ad hoc generation is for exploring. Recurring numbers should run on SQL a human has approved.

Make your schema legible to the model

The single highest-leverage improvement is not a better model. It is writing down what your columns mean, in the database, where introspection will find it.

COMMENT ON COLUMN subscriptions.status IS
  'trialing, active, past_due, canceled. Only active and past_due count toward MRR.';
COMMENT ON COLUMN orders.amount IS
  'Integer, minor currency units (cents). Divide by 100 for display. See currency column.';
COMMENT ON TABLE legacy_users IS
  'DEPRECATED, frozen 2024. Do not use. Canonical table is accounts.';

Then go further and build a thin semantic layer out of views: v_active_subscriptions, v_paying_accounts, v_recognized_revenue. Each one encodes the exclusions and the join path your team already agreed on. Grant the read-only role access to the views and, where you can, not to the raw tables underneath. Now the model cannot get the definition of "active customer" wrong, because there is exactly one definition and it is compiled into the view.

This is the same discipline that makes CRM questions answerable. A pipeline number is only as good as the agreement on what counts as an opportunity, which is the argument behind cleaner Salesforce data and faster answers and behind answering HubSpot pipeline questions in plain English.

Asking PostgreSQL in plain English inside Skopx

Skopx connects to PostgreSQL, MySQL, and MongoDB directly, so you can ask a database question in the same chat where you ask about Stripe, HubSpot, Slack, or anything else in the nearly 1,000 integrations it supports. Answers cite their source, and Skopx takes actions only with your approval.

A concrete example. You connect the read-only replica role you created above, then type:

Using the analytics Postgres connection, show me every account that had at least one invoice paid in the last 90 days but zero logins in the last 30, exclude internal and test accounts, and show me the SQL you ran.

What comes back is a result table with the accounts and their last payment and last login dates, plus the SQL itself so you can check the join and the exclusions before you act on it. If the query touched a table you did not expect, you see that immediately rather than discovering it in a customer conversation.

The second half is what makes it operational rather than a novelty. Skopx workflows are built by describing them in chat, no drag-and-drop canvas involved:

Every Monday at 9am, run that at-risk accounts query against Postgres, and if it returns more than five rows, post the list to the #customer-success Slack channel with a one-line summary.

That becomes a scheduled workflow with a database step, an if/else condition, and a Slack action. Runs are inspectable step by step, so when a Monday result looks strange you can open the run and see exactly what the query returned. Workflows are deliberately constrained: acyclic, up to 20 steps, no custom code steps, and AI steps run on your own provider key. Triggers are manual, schedule with a 15 minute minimum, or webhook.

Skopx catches what falls between your tools, and a database question that needs Slack context to interpret is exactly that shape. If your recurring question involves both a Postgres number and what the team said about it, Slack analytics and signals covers the conversational half.

On cost, so there is no ambiguity: Skopx is paid from day one. Solo is $5 per month and Team is $16 per seat per month with no seat cap. There is no free tier. You bring your own AI provider key, whether Anthropic, OpenAI, Google or another, and Skopx does not mark up AI usage. Current details are on the pricing page.

When a natural language query is the wrong tool

Being honest about this matters more than winning the sale.

That last row is the honest sweet spot. Natural language querying is strongest for exploration and for alerting, and weakest as a replacement for a governed reporting layer. Use it to find the question worth asking, then promote the good queries into reviewed SQL.

A rollout that does not blow up

1. Create the read-only role on a replica. Never point this at your primary with a write-capable user, not even for a demo.
2. Restrict what it can see. Start with a narrow schema or a set of views. You can always widen. Narrow scope also makes retrieval more accurate, so answers improve as a side effect.
3. Write comments on the twenty columns that matter. Status enums, money columns, timestamp semantics, deprecated tables. This is an afternoon of work with an outsized effect.
4. Run a reconciliation week. Ask ten questions whose answers you already know. Log every mismatch and fix it in the schema, the views, or the comments rather than in the phrasing of your questions.
5. Promote the winners. Anything asked more than twice becomes a saved query or a scheduled workflow that posts the result where people already work.
6. Watch what actually runs. pg_stat_statements plus slow query logging tells you whether the thing is a helpful tool or a load problem in waiting.

Frequently asked questions

Is a PostgreSQL natural language query accurate enough to trust?

For straightforward questions against a well-documented schema, it is reliable. For questions spanning many joins, nullable columns, and business definitions that live in people's heads, it is not reliable without review. The practical rule: trust it after you have read the SQL and reconciled one known number. Trust it unreviewed only for exploration, never for a figure someone else will act on.

Can it modify or delete my data?

Only if you let it. Connect with a role that has SELECT and nothing else, set default_transaction_read_only = on, and the server rejects writes regardless of what SQL is generated. Do not rely on instructions telling the model to avoid writes. Rely on grants.

What about very large tables and query cost?

Set statement_timeout so a bad plan cannot run indefinitely, point the connection at a read replica so analytical scans do not compete with production traffic, and cap the connection limit for the role. It is also worth insisting on a LIMIT for exploratory questions. If a question genuinely needs a full scan of a billion-row table, that is a warehouse job, not a chat job.

Do I still need SQL skills?

Yes, and arguably more than before. The skill shifts from writing queries to reading them critically: spotting a fan-out, noticing a filter that nullifies a LEFT JOIN, questioning a date boundary. Someone on the team needs to be able to do that. What changes is that ten people can now ask questions instead of queuing behind that one person.

How is this different from a BI tool?

A BI tool is built to model data once and then serve governed dashboards and visualizations to many people. Natural language querying is built for the questions nobody modeled in advance. They complement each other. If what you need is a dashboard with filters, drill-downs, and charts, use a BI tool. Skopx answers questions, generates documents, sends alerts, and automates follow-up actions, and it does not build dashboards.

Can it join database results with data from other systems?

That is the more interesting use. A question like "which accounts with open support tickets also have a failed payment this week" spans your database, your helpdesk, and your billing provider. Skopx pulls from connected tools alongside the database in the same conversation. The integrations directory lists what is connectable.

The short version

Natural language over Postgres is not a party trick and it is not a replacement for knowing your data. It is a fast path from a question to a candidate answer, and the value depends almost entirely on two things you control: whether the database is safe by construction, and whether your schema tells the truth about itself.

Create the read-only role. Comment the columns that matter. Expose views instead of raw tables. Read the SQL. Reconcile one known number. Do those five things and the queue in front of your one SQL person disappears without the wrong numbers taking its place.