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?

Answer 1

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,
    ...
);