1

I am not sure if my issue connecting to the Scala Play 2.5.x Framework or to PostgreSQL so I am going to describe my setup.

I am using the Play 2.5.6 with Scala and PostgreSQL 9.5.4-2 from the BigSQL Sandboxes. I use the Play Framework default evolution package to manage the DB versions.

I created a new database in BigSQL Sandbox's PGSQL and PGSQL created a default schema called public. I use this schema for development.

I would like to create a table with the following script (1.sql in DB evolution config):

# Initialize the database

# --- !Ups

CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    creation_date TIMESTAMP NOT NULL
);

# --- !Downs

DROP TABLE user;

Besides that I would like to read the table with a code like this:

val resultSet = statement.executeQuery("SELECT id, name, email FROM public.user WHERE id=" + id.toString)

I got an error if I would like to execute any of the mentioned code or even if I use the CREATE TABLE... code in pgadmin. The issue is with the user table name. If I prefix it with public (i.e. public.user) everything works fine.

My questions are:

  1. Is it normal to prefix the table name with the schema name every time? It seems to odd to me.
  2. How can I make the public schema a default option so I do not have to qualify the table name? (e.g. CREATE TABLE user (...); will not throw an error)

I tried the following:

  1. I set the search_path for my user: ALTER USER my_user SET search_path to public;
  2. I set the search_path for my database: ALTER database "my_database" SET search_path TO my_schema;
  3. search_path correctly shows this: "$user",public

I got the following errors:

  1. In Play: p.a.d.e.DefaultEvolutionsApi - ERROR: syntax error at or near "user"

  2. In pgadmin:

    ERROR: syntax error at or near "user"

    LINE 1: CREATE TABLE user (

    ********** Error **********

    ERROR: syntax error at or near "user"

    SQL state: 42601

    Character: 14

Gabor Meszaros
  • 1,335
  • 1
  • 15
  • 25

1 Answers1

2

This has nothing to do with the default schema. user is a reserved word.

You need to use double quotes to be able to create such a table:

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    creation_date TIMESTAMP NOT NULL
);

But I strongly recommend not doing that. Find a different name that does not require a quoted identifier.

  • That was the solution, thank you! I feel that the error message was not really helpful in this case. By the way, I made a short research to see why I had not found this on Google. I should have searched with the phase "create table user". That way I could found your other answer: http://stackoverflow.com/questions/22256124/cannot-create-a-database-table-named-user-in-postgresql :) I went with the "user_account" name. – Gabor Meszaros Sep 01 '16 at 12:47