在 PostgreSQL JDBC 中设置架构似乎不起作用 [英] Setting schema in PostgreSQL JDBC doesn't seem to work

问题描述

我用表user"创建了模式customer1"，我正在尝试使用 Connection.setSchema() 从 JDBC 连接它:

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"));
        }
    }
}

此代码打印:

search_path: customer1

然后它抛出带有消息的 PSQLException:

and then it throws PSQLException with message:

ERROR: column "name" does not exist

如果我在 SELECT 查询中限定用户"表:

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"));
    }
}

然后打印:

search_path: customer1
user name: name1

并且没有发生错误.我正在使用 JDBC 驱动程序 42.2.2 和 PostgreSQL 服务器 10.4.为什么设置架构不起作用?

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

推荐答案

user 是一个 内置函数(和一个关键字).所以你不能真正将它用作表名:

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)

而且因为它是一个函数，所以它没有列name.

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.

您可以始终使用架构限定表名，或者使用 双引号:select name from "user"; 或者只是找到一个不与内置函数冲突的表名.

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.

这篇关于在 PostgreSQL JDBC 中设置架构似乎不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！