Wrong SQL statements syntax in file data.sql in Spring Boot nad PostgreSQL

Question

I want to create a conditional query that will be created by the PostgreSQL database

IF ( NO EXISTS(SELECT * FROM users WHERE username = 'JonkiPro'))
BEGIN
  INSERT INTO UserEntity(id, username, email, password, enabled, registration_date, modified_date)
  VALUES(1, 'JonkiPro', '[email protected],', 'safsd', true, GetDate(), GetDate())
END

however, an error occurs during the compilation

    Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [db/init/data.sql]: IF ( NO EXISTS(SELECT * FROM users WHERE username = 'JonkiPro')); nested exception is org.postgresql.util.PSQLException: BŁĄD: błąd składni w lub blisko "IF"
      Pozycja: 1
        at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:137) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
 ...

    org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1633) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
            at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
            ... 99 common frames omitted
        Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [db/init/data.sql]: IF ( NO EXISTS(SELECT * FROM users WHERE username = 'JonkiPro')); nested exception is org.postgresql.util.PSQLException: BŁĄD: błąd składni w lub blisko "IF"
          Pozycja: 1
            at ...
            ... 114 common frames omitted
        Caused by: org.postgresql.util.PSQLException: ERROR: syntax error in or near "IF".
          Pozycja: 1
            at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
            at ...

I do not know why he does not understand the order IF. However, when I take the command between DO $$ ... $$ it rejects an exception

      Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [db/init/data.sql]]; nested exception is java.lang.ArrayIndexOutOfBoundsException
     ...
            ... 99 common frames omitted
        Caused by: org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [db/init/data.sql]]; nested exception is java.lang.ArrayIndexOutOfBoundsException
...

    org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:470) ~[spring-jdbc-4.3.10.RELEASE.jar:4.3.10.RELEASE]
                ... 127 common frames omitted

And my second problem is how to add a command to enter data into another table in the block BEGIN ... END

brain99 · Accepted Answer · 2017-09-29 23:44:34Z

0

See this question for an alternative way to conditionally create your user, as it appears you cannot use IF statements without first loading the PL/pgSQL extension. In your case this would result in a query like this:

INSERT INTO UserEntity(id, username, email, password, enabled, registration_date, modified_date)
SELECT
    1, 'JonkiPro', '[email protected],', 'safsd', true, GetDate(), GetDate()
WHERE NOT EXISTS (
    SELECT * FROM users WHERE username = 'JonkiPro'
);

answered Sep 29, 2017 at 23:44

brain99

8935 silver badges15 bronze badges

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

1 Comment

sdfsd Over a year ago

I have ERROR: relationship "userentity" does not exist. At "UserEntity" should be the name of the table?

Collectives™ on Stack Overflow

Wrong SQL statements syntax in file data.sql in Spring Boot nad PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related