68

I want to verify the syntax of files containing sql queries before they can be committed in my CVS project.

In order to do that, I have a commitinfo script, but I have trouble finding out if the sql commands are valid. psql does not seem to have a dryrun mode, and constructing my own postgresql-dialact tester from the grammar (that is in the source) seems like a long stretch.

The scripts may contain multiple queries, so an EXPLAIN cannot be wrapped around them.

Any hints?

Rob Audenaerde
  • 19,195
  • 10
  • 76
  • 121
  • i have related problem with SP on postgresql block not validated until its not called – triclosan Nov 25 '11 at 16:11
  • 7
    @triclosan: you may be interested in [`plpgsql lint`](https://github.com/okbob/plpgsql_lint) that addresses exactly this shortcomming. Pavel Stěhule is the main developer. See this [blog post](http://okbob.blogspot.com/2011/07/plpgsql-lint.html). – Erwin Brandstetter Nov 25 '11 at 16:16
  • 1
    I'm not very experienced with postgres so this is probably a bad workaround not worthy of a real answer, but I just add a garbage line at the end of the script that I know will cause an error. If the first error it hits is the garbage line, I can be reasonably confident the rest of the script is ok. Unlike a transaction it preserves sequence values, and for simple scripts it's quicker and easier than downloading another utility. – Ben Sutton Sep 08 '16 at 16:10

8 Answers8

61

I recently wrote up a utility to statically check the syntax of SQL for PostgreSQL. It leverages ecpg, the embedded SQL C preproccessor for postgres, to check the SQL syntax, so it uses the exact same parser that is built in to Postgres itself.

You can check it out on github: http://github.com/markdrago/pgsanity. You can give the README a skim to get a better idea of how it works and to get directions for how to install it. Here's a short example of how pgsanity can be used:

$ pgsanity good1.sql good2.sql bad.sql
bad.sql: line 1: ERROR: syntax error at or near "bogus_token"

$ find -name '*.sql' | xargs pgsanity
./sql/bad1.sql: line 59: ERROR: syntax error at or near ";"
./sql/bad2.sql: line 41: ERROR: syntax error at or near "insert"
./sql/bad3.sql: line 57: ERROR: syntax error at or near "update"
Mark Drago
  • 1,978
  • 14
  • 10
  • That looks helpful. I will check into this soon – Rob Audenaerde Nov 03 '12 at 14:47
  • 5
    Thanks for pgsanity! It's really handy. Is there any way to use pgsanity in systastic (https://github.com/scrooloose/syntastic)? It would be really awesome to run the check automatically when saving the file in vim. – while May 21 '13 at 08:15
  • @while I bet it wouldn't be hard to add it to syntastic. I've never used syntastic and I don't have the vim foo to add it on my own. But since pgsanity returns a 0 on success or a non-zero on failure I bet it would be relatively easy to add. – Mark Drago Jul 05 '13 at 11:57
  • 1
    Great tool @MarkDrago saved me after *hours* of frustrated debugging. :-) – nelsonic Mar 30 '19 at 16:30
  • Looks like this isn't being developed any more. Is that right? – LondonRob Mar 25 '21 at 10:49
  • The readme has updated 3 months ago, so someone is looking after it :) – Rob Audenaerde Apr 06 '23 at 07:36
46

Use this trick to validate PostgreSQL code syntax:

DO $SYNTAX_CHECK$ BEGIN RETURN;
    -- insert your SQL code here
END; $SYNTAX_CHECK$;

Function is_sql(sql text) (link is to my GitHub)

Ryan M
  • 18,333
  • 31
  • 67
  • 74
Rinat
  • 608
  • 5
  • 5
  • Will it be free of side effects if the query contains `commit;`? – samvel1024 Dec 23 '21 at 16:22
  • @samvel1024 yes, `commit` won't be run. You can verify with the following experiment: `CREATE TABLE foo(id int); DO $$ BEGIN RETURN; BEGIN; INSERT INTO foo VALUES (1); COMMIT; END; $$; SELECT * FROM foo;`. Even if you move the transaction's `BEGIN` outside of the `DO` block, the transaction still won't be committed. – Steven Kalt Jan 02 '22 at 15:07
  • Looks like this trick accepts plpgsql syntax which isn't valid SQL. For example `DO $$ BEGIN RETURN; IF true THEN select 1; END IF; END; $$;` works while `IF ... THEN ... END IF` isn't valid outside of plpgsql. – Steven Kalt Jan 02 '22 at 15:11
  • 1
    Hint for fellow noobs. If you get ```ERROR: syntax error at or near "END"``` then you are likely missing a semi-colon at the end of your SQL statement – Reddspark Apr 13 '22 at 08:33
  • When your code `SAVEPOINT my_savepoint; ...; ROLLBACK TO SAVEPOINT my_savepoint;` gets inside the `DO`, you will get an error. But there is a [workaround](https://stackoverflow.com/questions/2422205/postgresql-rolling-back-a-transaction-within-a-plpgsql-function/71944805#71944805) – Rinat Apr 20 '22 at 19:02
15

One way would be to put it into a transaction that you roll back at the end:

BEGIN;
<query>;
<query>;
<query>;
ROLLBACK;

Be aware that there are some effects that cannot be rolled back, like dblink calls, or anything written to the file system or incremented sequences.

I would advise cloning your database for testing purposes.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This can only be done with an active connection. I would prefer to have a static check. And will this not break if I have BEGIN statements in my sql? – Rob Audenaerde Nov 25 '11 at 16:16
  • @RobAu: Additional `BEGIN;` will be ignored. A `WARNING` will be issued. – Erwin Brandstetter Nov 25 '11 at 16:18
  • 1
    @RobAu: a static check will not work of dynamic queries. Well: not always. The only thing you can do is sandboxing and praying. – wildplasser Nov 25 '11 at 16:27
  • 1
    this is a really bad idea. there are tons of valid sql statements that will throw errors depending on the state of the db. also transactions work on CRUD but not schema changes which is also SQL – Sonic Soul Jun 19 '19 at 17:20
11

EXPLAIN (without ANALYZE) will parse the query and prepare an execution plan, without actually executing it.

https://www.postgresql.org/docs/current/static/sql-explain.html

karlgold
  • 7,970
  • 2
  • 29
  • 22
7

I'm usually use Mimer online SQL validator, the only thing is that it check SQL syntax for standard SQL :

  • SQL-92
  • SQL-99
  • SQL-03

and not specific for the PostgreSQL ... However if you write code following the standard you can use it and it work well ...

aleroot
  • 71,077
  • 30
  • 176
  • 213
  • The advantage of doing this is that you make it easier to switch databases. I love postgres, and it's been better in recent years, but for a long time its basic philosophy seemed to be "Standards? Where we're going, we don't need standards." – corsiKa Dec 19 '15 at 03:29
  • The disadvantage is that if you have table or column names that have uppercase letters and such, those identifiers need to be written in quotation marks, which I don't think is the convention in other databases. – OzzyTheGiant Jul 22 '20 at 17:56
6

A wonderful utility to verify SQL syntax: SQL Fiddle

Supports MySQL, Oracle, PostgreSQL, SQLite, MS SQL.

Anshul Tiwari
  • 643
  • 9
  • 16
1

You could just wrap it in SELECT 1 ( <your query> ) AS a WHERE 1 = 0;

It'll fail on validation but it won't actually execute. Here's an example query plan:

Result  (cost=0.00..0.01 rows=1 width=0)
  One-Time Filter: false
Jeff Wu
  • 2,428
  • 1
  • 21
  • 25
1

You can run queries iside postgresql function and raise exception in the end. All changes will be rolled back. For example:

CREATE OR REPLACE FUNCTION run_test(_sp character varying)
  RETURNS character varying AS
$BODY$
BEGIN
  EXECUTE 'SELECT ' || _sp;
  RAISE EXCEPTION '#OK';
EXCEPTION
  WHEN others THEN
    RETURN SQLERRM;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Another sollution - plpgsql_check extension (on github), the next incarnation of pgpsql_lint

shcherbak
  • 738
  • 8
  • 14
  • you can write a wrapper returning void and include all DML to setup environment, run particular functions and queries, then exit with 'RAISE EXCEPTION'. try google for some kinde of pgunit. They use such technik – shcherbak Mar 10 '17 at 10:12