Java – Setting the schema in PostgreSQL JDBC doesn’t seem to work

I

created the schema “customer1” with table “user” and I’m trying to connect it from JDBC using Connection.setSchema():

String url = "jdbc:postgresql://localhost/project";
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "postgres");

try (Connection conn = DriverManager.getConnection(url, props)) {
    conn.setSchema("customer1");

try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW search_path")) {
        rs.next();
        System.out.println("search_path: " + rs.getString(1));
    }

try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT name FROM user LIMIT 1")) {
        if (rs.next()) {
            System.out.println("user name: " + rs.getString("name"));
        }
    }
}

This code prints:

search_path: customer1

It then throws PSQLException:, with the message

ERROR: column "name" does not exist

If I qualify the Users table in a SELECT query:

try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT name FROM customer1.user LIMIT 1")) {
    if (rs.next()) {
        System.out.println("user name: " + rs.getString("name"));
    }
}

Then print:

search_path: customer1
user name: name1

No errors occurred. I’m using JDBC driver 42.2.2 and PostgreSQL server 10.4. Why doesn’t setting up the schema work?

User is built-in function (and one keyword). So you can’t really use it as a table name:

psql (10.4)
Type "help" for help.

postgres=# select user;
   user
----------
 postgres
(1 row)

postgres=# select * from user;
   user
----------
 postgres
(1 row)

Because it is a function, it does not have the column name.

postgres=# select name from user;
ERROR:  column "name" does not exist
LINE 1: select name from user;
               ^
postgres=#

If you qualify a table, it is clear that you are not referencing a function, but a table.

You can always qualify table names with schema, or use double quotes : select name from "user"; Or simply find a table name that doesn’t conflict with the built-in functions.