Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?

Jdbc provides getGeneratedKeys function.

java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
<http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29>
if (generatedKeys.next()) {
primkey = generatedKeys.getInt(1);
}

https://www.xyzws.com/javafaq/how-to-retrieve-automatically-generated-keys-in-jdbc/173

Else, sql insert into returning may help You.

https://www.postgresql.org/docs/11/sql-insert.html

Cheers,
Agharta

Il dom 1 mar 2020, 11:26 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> ha scritto:

>
>
> ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld(at)yahoo(dot)com> napsal:
>
>> Hi,
>>
>> I hope I am posting on the right forum. I googled but I can't find any
>> solution pertaining to my problem.
>>
>> Could someone know what is the syntext for last_insert_id() in Postgresql
>> for me to insert into my sql execute query?
>>
>
> Postgres has not last_insert_id function. Maybe you think "lastval"
> function
>
> https://www.postgresql.org/docs/current/functions-sequence.html
>
> Regards
>
> Pavel
>
>
>> public int getTutorById() {
>> openConnection();
>> int tutor_id = 0;
>> try {
>> Statement stmt3 = connection.createStatement();
>> ResultSet rs = stmt3.executeQuery("SELECT
>> last_insert_id() from xtutor");
>> {
>> while (rs.next()) {
>> tutor_id = rs.getInt(1);
>>
>> }
>> }
>> } catch (SQLException e) {
>> e.printStackTrace();
>> }
>> return tutor_id;
>> }
>>
>> org.postgresql.util.PSQLException: ERROR: function last_insert_id() does
>> not exist
>> Hint: No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> Position: 8
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
>> at
>> org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
>> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
>> at
>> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
>> at
>> org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
>> at
>> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
>> at
>> org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
>> at daoMySql.tutorDAOImpl.getTutorById(tutorDAOImpl.java:202)
>> at daoMySql.tutorDAOImpl.getAlltutors(tutorDAOImpl.java:161)
>> at business.manager.getAlltutors(manager.java:99)
>>
>> The generated id is successfully generated by JDBC.
>>
>> The errors when I tested in out using PGAdmin4 is
>>
>> ERROR: function last_insert_id() does not exist
>> LINE 1: SELECT last_insert_id() from xtutor;
>> ^
>> HINT: No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> SQL state: 42883
>> Character: 8
>>
>> Postgresql 11, Windows 11
>>
>> Thanks.
>>
>>
>>
>>