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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Karen Goh
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?

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.



Reply | Threaded
Open this post in threaded view
|

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

Pavel Stehule


ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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.



Reply | Threaded
Open this post in threaded view
|

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

agharta82@gmail.com
Jdbc provides getGeneratedKeys function.

java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
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 <[hidden email]> ha scritto:


ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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.



Reply | Threaded
Open this post in threaded view
|

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

Karen Goh
In reply to this post by Pavel Stehule
Hi Pavel,

Using this as reference and your link :

https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id

I tried :

select lastval('t_id') from table_tutor;

but it is not working.

Is there any tutorial out there that teaches the exact syntax ?

Thanks & regards,
Karen
On Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <[hidden email]> wrote:




ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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.



Reply | Threaded
Open this post in threaded view
|

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

John Fabiani
currval('id_seq')  where id_seq is the sequence you are using for the key (t_id).  That assumes you are using a sequence.
Johnf

On 3/3/20 7:50 AM, Karen Goh wrote:
Hi Pavel,

Using this as reference and your link :

https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id

I tried :

select lastval('t_id') from table_tutor;

but it is not working.

Is there any tutorial out there that teaches the exact syntax ?

Thanks & regards,
Karen
On Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule [hidden email] wrote:




ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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.




Reply | Threaded
Open this post in threaded view
|

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

Pavel Stehule
In reply to this post by Karen Goh


út 3. 3. 2020 v 16:50 odesílatel Karen Goh <[hidden email]> napsal:
Hi Pavel,

Using this as reference and your link :

https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id

I tried :

select lastval('t_id') from table_tutor;

but it is not working.

Is there any tutorial out there that teaches the exact syntax ?

lastval has not any parameter .. so you should to use SELECT lastval();


Thanks & regards,
Karen
On Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <[hidden email]> wrote:




ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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.



Reply | Threaded
Open this post in threaded view
|

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

Sándor Daku
In reply to this post by Karen Goh
On Tue, 3 Mar 2020 at 16:50, Karen Goh <[hidden email]> wrote:
Hi Pavel,

Using this as reference and your link :

https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id

I tried :

select lastval('t_id') from table_tutor;

but it is not working.

Is there any tutorial out there that teaches the exact syntax ?

Thanks & regards,
Karen
On Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <[hidden email]> wrote:




ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <[hidden email]> 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


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. 

 Hi,

Expanding a bit on John's answer, if you are using a serial or bigserial field Postgres makes a sequence for that field.
For example:
create table xtutor(
  t_id serial,
  ...
  ...

results a sequence named xtutor_t_id_seq(The schema is tablename_fieldname_seq)

And when you check the table structure with \d xtutor you'll see something like this:
Column     |            Type             | Collation | Nullable |                       Default
t_id            | integer                     |               | not null   | nextval('xtutor_t_id_seq'::regclass)

You can query the sequence as a table:

SELECT * FROM xtutor_t_id_seq;

or get the next value for a sequence(This advances the sequence as well):

SELECT nextval('xtutor_t_id_seq');

However...

Your example code looks like you are looking for the id of the most recently inserted tutor in which case a better solution would be replacing your select with this:
SELECT max(t_id) from xtutor;

Regards,
Sándor