Обсуждение: What is the right syntax for retrieving the last_insert_id() inPostgresql ?
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.
Re: What is the right syntax for retrieving the last_insert_id() inPostgresql ?
От
Pavel Stehule
Дата:
ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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
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.
Re: What is the right syntax for retrieving the last_insert_id() inPostgresql ?
От
agharta agharta
Дата:
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.
Cheers,
Agharta
Il dom 1 mar 2020, 11:26 Pavel Stehule <pavel.stehule@gmail.com> ha scritto:
ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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" functionRegardsPavel
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 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
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 <pavel.stehule@gmail.com> wrote:
ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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
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.
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
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,
KarenOn Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <pavel.stehule@gmail.com> wrote:ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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" functionRegardsPavel
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.
Re: What is the right syntax for retrieving the last_insert_id() inPostgresql ?
От
Pavel Stehule
Дата:
út 3. 3. 2020 v 16:50 odesílatel Karen Goh <karenworld@yahoo.com> 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,
KarenOn Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <pavel.stehule@gmail.com> wrote:ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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" functionRegardsPavel
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.
Re: What is the right syntax for retrieving the last_insert_id() inPostgresql ?
От
Sándor Daku
Дата:
On Tue, 3 Mar 2020 at 16:50, Karen Goh <karenworld@yahoo.com> 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,
KarenOn Sunday, March 1, 2020, 06:27:00 PM GMT+8, Pavel Stehule <pavel.stehule@gmail.com> wrote:ne 1. 3. 2020 v 11:18 odesílatel Karen Goh <karenworld@yahoo.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" functionRegardsPavel
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