org.postgresql.util.PSQLException: ERROR: the relation “test_id” does not exist. SQL state: 42883

Question

I can not retrieve the id from the "test" table after saving it in postgresql by pgadmin4:

Here the create script generated by pgadmi4 of the table "test":

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;

Here the code to insert the values to the "test" table thourgh JDBC:

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

Here the function to get the last SerialNumber of the affected table:

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

            // get the postgresql serial field value with this query
            String query = "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();
        }
}

I have tried to run isolately by pdgadmin console with the same results.

Here the results:

org.postgresql.util.PSQLException: ERROR: the relation "test_id" does not exist Position : 16 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at com.cgi.training.javase.school.dao.testDAO.getSerialNum(testDAO.java:45) at com.cgi.training.javase.school.dao.testDAO.main(testDAO.java:21)

Your sequence is named test_test_id_seq but you are passing the name test_id to the call of currval() — a_horse_with_no_name

Jairo Sánchez Jairo Sánchez · Accepted Answer · 2019-07-07T17:55:43

Response from @a_horse_with_no_name: Your sequence is named test_test_id_seq but you are passing the name test_id to the call of currval() – a_horse_with_no_name

org.postgresql.util.PSQLException: ERROR: the relation “test_id” does not exist. SQL state: 42883

1 Answers