2

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.

Improve this question
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.
    – JosephDoggie
    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!
    – JosephDoggie
    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
    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.
    – JosephDoggie
    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
    – JosephDoggie
    Aug 9, 2021 at 14:02

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

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