Spring boot and postgresql: weird error while executing import.sql file

Question

I have a spring boot application where I use Spring Data JPA with PostgreSQL as the database. I'm letting hibernate create the schema and that is working perfectly, but when I'm trying to populate the database with the import.sql file, an error shows up. This is the error:

CommandAcceptanceException: Error executing DDL "insert into users(user_id, username, email, password, created_at)" via JDBC Statement
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input.

This is my import.sql file:

insert into users(user_id, username, email, password, created_at)
values(1, 'alex', '[email protected]', 'pword', current_timestamp);

This is my application.properties file:

spring.datasource.url=jdbc:postgresql://localhost:5432/website
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQL95Dialect
spring.datasource.username=alexander
spring.datasource.password=
spring.jpa.show-sql=true
# drop n create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create-drop
# spring.sql.init.data-locations= classpath:/data.sql
# spring.datasource.initialization-mode=always
# spring.sql.init.mode=always
# spring.jpa.defer-datasource-initialization=true

The commented out part in application.properties are things that I've found in other threads that I've tried but with no change in result.

Every table gets created perfectly, and if I copy the contents of import.sql and inserts it into PostgreSQL via the terminal or pgadmin, then it works perfectly and the user gets inserted with 0 problems. So I really don't get what this "syntax error at end of input." could be.

PS. It's not an issue with the word current_timestamp either, if I swap it with for example ('2020-05-06 08:30') I still get the exact same error.

Have you tried putting the statement on a single line? It looks like it may be delimiting on new lines rather than semicolons. — Andy Wilkinson
– Andy Wilkinson, Commented Jun 17, 2021 at 7:04

Piotr Solarski · Accepted Answer · 2021-06-17 11:59:04Z

3

You have better solution for this problem than moving SQL insert into one line. Just add jpa property in application.properties which allows you to put SQL statements in multiple lines:

jpa:
  properties:
    hibernate.hbm2ddl.import_files_sql_extractor: org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor

Having SQL insert statement in two (or multiple) rows can improve readability

answered Jun 17, 2021 at 11:59

Piotr Solarski

3162 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring boot and postgresql: weird error while executing import.sql file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related