psqlexception：错误：关系“test_id”不存在。SQL状态：42883

Question

在pgadmin4将id保存在postgresql中后,我无法从“test”表中检索到id：

这里是表“test”的pgadmi4生成的create脚本：

CREATE TABLE school.test
(
    test_id bigint NOT NULL DEFAULT nextval('school.test_test_id_seq'::regclass),
    name character varying(10) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE school.test
    OWNER to postgres;

        Connection conn = pstgConn.dbConnection();
        String query = "INSERT INTO school.test(name) VALUES (?)";
        try (PreparedStatement pst = conn.prepareStatement(query)) {
            pst.setString(1, "anything");
            pst.executeUpdate();
            //Get Sequential number of the table
            getSerialNum(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

public void getSerialNum(Connection connection) {
        int serialNum = 0;
        try {
            Statement stmt = connection.createStatement();

            // get the postgresql serial field value with this query
            String query = "html" target="_blank">select currval('test_id')";

            ResultSet rs;

            rs = stmt.executeQuery(query);
            if (rs.next()) {
                serialNum = rs.getInt(1);
                System.out.println("serialNum = " + serialNum);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
}

org.postgresql.util.psqlexception：错误：关系“test_id”不存在位置 ；：16在org.postgresql.core.v3.queryexecutorimpl.receiveerrorresponse(queryexecutorimpl.java:2103)在org.postgresql.core.v3.queryexecutorimpl.processresponse(queryexecutorimpl.java:1836)在org.postgresql.core.v3.queryexecutorimpl.execute(queryexecutorimpl.java:257)在org.postgresql.jdbc2.abstractjdbc2statement.execute WithFlags(abstractJDBC2Statement.java:374)在org.postgresql.jdbc2.abstractJDBC2Statement.executeQuery(abstractJDBC2Statement.java:254)在com.cgi.training.javase.school.dao.testdao.getSerialNum(testdao.java:45)在com.cgi.training.javase.school.dao.testdao.main(testdao.java:21)在com.cgi.training.javase.school.dao.testdao.main

Answer 1

来自@a_horse_with_no_name的响应：您的序列名为test_test_id_seq，但您正在将名称test_id传递给currval()-a_horse_with_no_name的调用