java - Setting schema in PostgreSQL JDBC doesn't seem to work

Question

I created 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

and then it throws PSQLException with message:

ERROR: column "name" does not exist

If I qualify "user" table in 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 it prints:

search_path: customer1
user name: name1

and no error occur. I'm using JDBC driver 42.2.2 and PostgreSQL server 10.4. Why setting schema doesn't work?

Answer 1

user is a built-in function (and a 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)

And because it's a function it does not have a column name.

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

If you qualify the table, then it's clear that you are not referencing the function, but the table.

You can either always qualify the table name with the schema, or use double quotes: select name from "user"; or simply find a table name that does not collide with built-in functions.