JSqlParser: ParseException: Cannot parse statement: Encountered unexpected token: "exists" "EXISTS"

My code:

	@Test
	public void testJSQParser()
	{
		String sql = "alter table if exists public.table2 rename to table3;";
		Validation validation = new Validation(List.of(DatabaseType.POSTGRESQL), sql);
		List<ValidationError> errors = validation.validate();
		Assert.assertTrue(errors.toString(), errors.isEmpty());
	}

Result:

java.lang.AssertionError: [ValidationError [
statement=alter table if exists public.table2 rename to table3;
capability=parsing
errors=[ParseException: Cannot parse statement: Encountered unexpected token: "exists" "EXISTS"
    at line 1, column 16.

Was expecting one of:

    "ADD"
    "ALGORITHM"
    "ALTER"
    "CHANGE"
    "COMMENT"
    "DROP"
    "MODIFY"
    "RENAME"
]
]]

This statement is a valid PostgreSQL syntax.

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 26 (25 by maintainers)

Most upvoted comments

based on the unique grammar of each DB.

The idea of JSQLParser is to manage all possible SQL syntax of various RDBMS in one Grammar definition.

Unfortunately I see that this kind of use of this library is not common

It is not uncommon, I myself would also have taken a slightly different approach, e. g. defining a) a set of RDBMS which shall be supported and then b) find a way to flag each production for compatibility with a specific RDBMS. But that is only my gut feeling, the chosen approach is great for a lot of other objectives.

and that is why the code responsible for the validation functionality was not maintained.

Why not volunteer and just add the Validator Rules for Oracle and Postgresql? Or set a bounty for other developers? The frame work is there already, all it needs is some fine-tuning now.

Should I look for other solutions?

It is always good to know about options, but I myself came quite late for the party and stayed simply because I did not find anything even remotely as good as JSQLParser. Although I found a few short comings it was not too difficult to dive into the code and fill in what I was interested in myself.

Why not give it a try starting with the RENAME oldName TO newname statement and amend the Validator so it will accept Oracle and reject Postgresql.

manticore-projects on Sep 2, 2021