PostgreSQL 9.4 create table if not exists

Question

I created tables on heroku using the following DDL.

CREATE TABLE IF NOT EXISTS "Team"(
  "id" SERIAL,
  "name" varchar(50) NOT NULL,
  "description" varchar(255)
);


CREATE TABLE IF NOT EXISTS  "Member"(
  "id" SERIAL,
  "name" varchar(50) NOT NULL,
  "emp_number" integer NOT NULL,
  "position" varchar(100) NOT NULL,
  "team_id" integer references "Team"("id")
);

I got the following error:

    play.api.UnexpectedException: Unexpected exception[ProvisionException: Unable to provision, see the following errors:

1) Error injecting constructor, javax.persistence.PersistenceException: Unable to execute JPA schema generation create command [CREATE TABLE IF NOT EXISTS "Team"(]
  at play.db.jpa.DefaultJPAApi$JPAApiProvider.<init>(DefaultJPAApi.java:35)
  at play.db.jpa.DefaultJPAApi$JPAApiProvider.class(DefaultJPAApi.java:30)
  ...

1 error]
    at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:191) ~[com.typesafe.play.play_2.11-2.4.2.jar:2.4.2]
    at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:261) ~[com.typesafe.play.play_2.11-2.4.2.jar:2.4.2]
    at play.api.GlobalSettings$class.onError(GlobalSettings.scala:179) [com.typesafe.play.play_2.11-2.4.2.jar:2.4.2]
    ...
Caused by: com.google.inject.ProvisionException: Unable to provision, see the following errors:

1) Error injecting constructor, javax.persistence.PersistenceException: Unable to execute JPA schema generation create command [CREATE TABLE IF NOT EXISTS "Team"(]
  at play.db.jpa.DefaultJPAApi$JPAApiProvider.<init>(DefaultJPAApi.java:35)
  ...

1 error
    at com.google.inject.internal.InjectorImpl$2.get(InjectorImpl.java:1025) ~[com.google.inject.guice-4.0.jar:na]
    at com.google.inject.internal.InjectorImpl.getInstance(InjectorImpl.java:1051) ~[com.google.inject.guice-4.0.jar:na]
    ...
Caused by: javax.persistence.PersistenceException: Unable to execute JPA schema generation create command [CREATE TABLE IF NOT EXISTS "Team"(]
    at org.hibernate.jpa.internal.schemagen.GenerationTargetToDatabase.acceptCreateCommands(GenerationTargetToDatabase.java:64) ~[org.hibernate.hibernate-entitymanager-4.3.9.Final.jar:4.3.9.Final]
    ...
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850) ~[org.hibernate.hibernate-entitymanager-4.3.9.Final.jar:4.3.9.Final]
    ...
  Position: 35
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270) ~[org.postgresql.postgresql-9.4-1201-jdbc41.jar:9.4]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998) ~[org.postgresql.postgresql-9.4-1201-jdbc41.jar:9.4]
    ...

The error is quite obvious. The SQL contains errors. I am quite new to postgres sql. At least I know the current version used by heroku supports if not exists syntax but I am not sure where I went wrong.

Is anybody good at PostgreSQL here?

You should really run the query directly instead of going through a web framework. This gives you actually readable errors. — Jørgen R
– Jørgen R, Commented Feb 19, 2016 at 8:04
Unrelated, but: you should really avoid quoted identifiers. They are much more trouble in the long run then they are worth it. — user330315
– user330315, Commented Feb 19, 2016 at 9:45

Jørgen R · Accepted Answer · 2016-02-19 08:13:12Z

4

If you had run this query directly, you would have gotten the error:

ERROR: there is no unique constraint matching given keys for referenced table "Team"

This gives you a clue that there's something missing to identify the columns in the "Team" table uniquely. You have declared that the ids of the tables are serials, but forgot to add primary key constraints. Adding this, will let you execute the query:

CREATE TABLE IF NOT EXISTS "Team"(
    "id" SERIAL primary key,
    ...
);

CREATE TABLE IF NOT EXISTS  "Member"(
    "id" SERIAL primary key,
    ...
);

answered Feb 19, 2016 at 8:13

Jørgen R

10.8k7 gold badges45 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL 9.4 create table if not exists

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