Keegan Jorgensen

Testing PostgreSQL statements for syntax errors without running them

2021-06-03 · 2 minute read
DO $TEST$ BEGIN RETURN;
    -- <Your code here>
END; $TEST$;

The easiest way to test most SQL queries is to run them. Feedback is immediate. It's easy to rapidly iterate on results until the query returns what you're looking for.

Sometimes you may only want to give your query a dry run. Maybe you're developing an insert statement that you'd rather not run every time to test. Maybe you're working on a parameterized query and don't want to think about test data. You should be able to test if your query is valid just as quickly as you test simple SELECT queries.

The simple trick is to surround your statement in a short circuited anonymous code block. This allows the query parser to read the full block of code as if it were to execute, without actually executing it. An immediate RETURN statement stops any of your code from executing.

Let's test it out:

DO $TEST$ BEGIN RETURN;
  INSERT INO users (id, email) VALUES ($1, $2);
END; $TEST$;

-- Query 1 ERROR: ERROR:  syntax error at or near "INO"
-- LINE 2:   INSERT INO users (id, email) VALUES ($1, $2);
--                  ^

Here we are able to check an insert statement for syntax errors. We didn't need to worry about side effects from testing our statement. We also didn't have to supply it with example parameters. Instead the parser helpfully pointed out our error.

Now that we know what's wrong, let's see what happens when our syntax is correct:

DO $TEST$ BEGIN RETURN;
  INSERT INTO accounts (id, email) VALUES ($1, $2);
END; $TEST$;

-- Query 1 OK: DO

The OK: DO result returned informs us that our statement executed without a hitch. What it doesn't know is that only the RETURN statement at the head executed. While the rest of our syntax was correct, none of it actually ran.

Full credit goes to Rinat Mukhatrov for sharing this technique on StackOverflow. He uses this technique in his is_sql function, one of many in his postgres-patterns-library.

You can see the revision history of this post on Github