org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement “drop table if exists [*]user CASCADE “; expected “identifier”; SQL statement:

I’ve below data.sql file in my src/main/resources/data.sql file. I would like user tabel to be created from user.csv file.

DROP TABLE IF EXISTS `USER` CASCADE;
CREATE TABLE `user` AS SELECT * FROM CSVREAD('classpath:user.csv');

When the spring application starts it always errors out with below error –

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement “drop table if exists [*]user CASCADE “; expected “identifier”; SQL statement:

I’ve spent long time to figure out the error but couln’t. Any idea how can I fix this error?

Answer

Always check if you are able to run same query on database you are using through DBMS tool like pgadmin or toad. I assume you are using postgresql, so user keyword is reserved if u still. Need to create table with name user, it must be enclosed in double quotes, may be you can try with double quotes instead single quotes in Java you will need to escape this double quotes to get included into string text for this query text

DROP TABLE IF EXISTS "USER" CASCADE;
CREATE TABLE "user" AS SELECT * FROM CSVREAD('classpath:user.csv')