3

Using the dbeaver tool, I want to check a script for syntax errors without actually running it.

Purpose: For a long insert query (postgresql syntax, PostgreSQL 10.15) in the form of:

INSERT INTO schema1.table1
(t1_id, fk1_id, fk_2_id, col1, col2)
  VALUES
           (nextval('schema1.sq_table1'), fk1_id_1, fk1_id_2,col1_val_1, col1_val_2),
            -- etc, literally thousands of lines

I cannot run the prod-version of the query in a not-prod region, because the foreign keys are different.

So is there a way to just check the SQL's syntax without running it. It throws foreign key constraint errors in the not-prod region, so I think that verifies the syntax, but I'd like to be doubly sure.

As per comment below, I think for the case here (insert with foreign-key), a foreign-key-violation-error would verify the syntax, but there is still the general case: Is it possible to verify the syntax of the SQL query without actually running it? I would think that there are probably several cases that this would be useful.

5
  • It appears that if there is a foreign-key constraint violation, this must mean the syntax is ok. Still, checking syntax without running would be a good feature / operation for any database tool to have. Commented Aug 5, 2021 at 16:50
  • The keys are generated by nextval('schema1.sq_table1') so they get out of sync quickly. They represent the same thing, but they have different literal numbers.... I suppose this would be a giant topic for a meta-software engineering post! Commented Aug 5, 2021 at 20:23
  • 1
    As you said, it already verifies the syntax. You should accept yes for an answer! If what you really want to verify is that every FK are satisfied, nothing can do that. They might be satisfied at once instance, but not satisfied a microsecond later.
    – jjanes
    Commented Aug 5, 2021 at 20:47
  • I think for the case I sited, that does verify the syntax, but there is still the general case: Is it possible to verify the syntax of the SQL query without actually running it; there are probably several cases that this would be useful. Commented Aug 5, 2021 at 21:00
  • @Lennart, please promote your comment to an answer. If this happens, I will mark it correct. It works, and it also looks like the correct answer: "Explain execution plan"... Thanks Commented Aug 9, 2021 at 14:02

1 Answer 1

3

If you explain your query it is compiled but not executed. This means that it is checked for syntactic errors, but constraints are not validated against your data.

2
  • 1
    Expain query does not work
    – hellboy
    Commented Mar 7 at 7:25
  • 1
    @hellboy, what is not working? Commented Mar 7 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.