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

Question

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 1

There is a keyword USER in newer versions of H2 database, therefore You need to escape it correctly. You should use double quote " instead of single quote '.

Copy/paste from H2 documentation:

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes). The following tokens are keywords in H2:

..., USER, ...

Newer versions of H2 may have more keywords than older ones.

Answer 2

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')