0

I use data.sql and schema.sql files in order to create tables and populate them in a Spring Boot app. However, after first run, when I start the app again, it gives "already exist" error for table, constraints, table values.

I solved the table problem by adding "IF NOT EXISTS", but the others are not so easy. In this situation, should I use H2 instead of PostgreSQL or MySQL so that the database cleared after each run? I know, normally I would prefer Flyway, but for this project, I cannot use it. So, what should I do?

  • Possible duplicate of [*Postgres: Add constraint if it doesn't already exist*](https://stackoverflow.com/q/6801919/642706). – Basil Bourque Feb 24 '23 at 22:39
  • @BasilBourque No amigo, not duplicate. Please read the question and the answer you suggested, you will probably see the difference. But I will help you; I read that page before opening the question, but even if I solved some part of the problem, I need to solve the problem regarding to the INSERT clauses in my `data.sql`. So, what would you suggest? –  Feb 25 '23 at 07:43

1 Answers1

0

IF NOT EXISTS/IF EXISTS

Many commands do offer a IF NOT EXISTS clause.

You found that in the CREATE TABLE command.

Adding/dropping a column do too, in Postgres. See documentation.

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

Drop, then add

Some commands do not support IF NOT EXISTS. One of those is adding a constraint. This Answer suggests executing a DROP CONSTRAINT IF EXISTS before doing ADD CONSTRAINT. This drop-then-add approach may work in some other situations as well.

That other Question also has other Answers with various approaches you may want to consider.

But I expect that for more complicated scenarios, you may need to track whether SQL operations have already been performed.

Flyway for the win

Flyway, Liquibase, etc. really are the best solution for tracking which SQL scripts have been run and which need to be run.

Otherwise you will need to devise a way to track which scripts have been run. I would rather rely on a solution already built & tested.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Thanks a lot. I agree with you and normally I would prefer Flyway. But in have not that option and have to use data.sql. On the other hand, "Drop, then add" does also seem to be a good option as I can drop and re-insert the records. –  Feb 25 '23 at 07:48