agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWhat is the right syntax for retrieving the last_insert_id() in Postgresql ?
7+ messages / 5 participants
[nested] [flat]
* What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-01 10:17 Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Karen Goh @ 2020-03-01 10:17 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-01 10:26 Pavel Stehule <pavel.stehule@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Pavel Stehule @ 2020-03-01 10:26 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-sql@lists.postgresql.org
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
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.
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-01 11:16 agharta agharta <agharta82@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: agharta agharta @ 2020-03-01 11:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Karen Goh <karenworld@yahoo.com>; pgsql-sql@lists.postgresql.org
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.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"
> 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.
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-03 15:50 Karen Goh <karenworld@yahoo.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 3 replies; 7+ messages in thread
From: Karen Goh @ 2020-03-03 15:50 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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-inse...
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
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-03 16:04 john <johnf@jfcomputer.com>
parent: Karen Goh <karenworld@yahoo.com>
2 siblings, 0 replies; 7+ messages in thread
From: john @ 2020-03-03 16:04 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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-inse...
>
> 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
> <mailto: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
>
> 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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-03 16:26 Pavel Stehule <pavel.stehule@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
2 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2020-03-03 16:26 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-sql@lists.postgresql.org
ú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-inse...
>
> 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 <
> 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
>
> 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.
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ?
@ 2020-03-03 16:36 Sándor Daku <daku.sandor@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
2 siblings, 0 replies; 7+ messages in thread
From: Sándor Daku @ 2020-03-03 16:36 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql@lists.postgresql.org
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-inse...
>
> 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
>
> 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.
>
>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-03-03 16:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-01 10:17 What is the right syntax for retrieving the last_insert_id() in Postgresql ? Karen Goh <karenworld@yahoo.com>
2020-03-01 10:26 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-03-01 11:16 ` agharta agharta <agharta82@gmail.com>
2020-03-03 15:50 ` Karen Goh <karenworld@yahoo.com>
2020-03-03 16:04 ` john <johnf@jfcomputer.com>
2020-03-03 16:26 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-03-03 16:36 ` Sándor Daku <daku.sandor@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox