Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j99oK-0008OC-9b for pgsql-sql@arkaria.postgresql.org; Tue, 03 Mar 2020 15:50:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j99oG-0008Rt-VZ for pgsql-sql@arkaria.postgresql.org; Tue, 03 Mar 2020 15:50:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j99oG-0008Rk-3V for pgsql-sql@lists.postgresql.org; Tue, 03 Mar 2020 15:50:16 +0000 Received: from sonic306-2.consmr.mail.bf2.yahoo.com ([74.6.132.41]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j99oC-0001CT-2F for pgsql-sql@lists.postgresql.org; Tue, 03 Mar 2020 15:50:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1583250610; bh=2xUU7/uVRTqbwJc8ggGcVbugP/VcyWpLPaMjacCNSoE=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=Nz03Nkhv1Ni1GiUAmMrinzxSOPuF8LU1vwbEbU3YVaktxpkCP7nn5HbuVo6pyuLM93XrZ+bwFibLpfVjcWScskG6fTAPDPZYfy1zLDgOjB01nSTgSXBroMGXglNr+29IUOhNJTHbwmS9qhGb1YTXZO83luJeBQn4YvpTzY+pbBnGIbqrR1WodB2oWzLo+IZGy+LmAbnWU3WSPPCDSVJV4zvKLmOJ2UEyKXlEWz1ecrtHp79F+6Dh+LeONSBApTP7bc1QgbXjcuLm/hi1IrcHBtSf2uoxej+sOwfV6yxS3B5cyC3HsY0ASA9R3GXb46g0BXLSJAolUuviFGeC8zcoQg== X-YMail-OSG: 53IqHacVM1ktU_khrk7Fk.os3SegM9pzPIAH_4wLQwzdpyDFvmPyzEGP_aGh7R9 H822jPwADjv7MEeu_fmDY.zekvmR.RATPDQG8XOypqIkhglca3hqq4WglyZ4F8K31eGMiWBX9ieA dVe41jv6TMrgOZctRD7bbKdjBpiPs8KoISw2tEXVTONJ.7B86Gufzx_NTxmyVdnjrj9rWqPqeN.n 5fvJ18P0KIMP3HLJHcKJFtba2it648Y7Imbx5epd2vAi7.v.zrDTjK6sEO2JWKaSR4WC8A_SD85b ejreUQ0JtxtPhBwLPWacsjgOpxOmC2uVPfYMb.ZjGDWDAx5JGLWkAKL2zcepYRtHdRGQ4Or1VT5U Mopb06UPsWkqXU9L.TbY.bHI.c7pRJpqs9pqqJsWXR0a25kdvT.U_Wq_4G4pUNQMSORHeg4qqn6E QGDNaIqDaI4icDe3MeA6NIdsXshLGzQb5fouTuXwTCUMPLRFcrxye.Ek9J8VfeqkZNqRDnnbp5Xx aKw7L0_f_VTcg2mpqT.3c4cfFBViuO7nCVo6ILbeCi0ns0z01oH49_DJViT66H_2PVg8pEF2xBeg 5UWesnEWBuhLq4XiOg3fgi0nXuqeScrki0AX7tEmXOFIiZ9xRjMPzlfA8Yang1Liv8_Oea7jeKJm Ffdt2USvYPWsCdAlzc1cd2CV.tz92SzZVsS.KsmipopK5kD5DLsY9dZPs4YVYwnJWVqXEXDQZrjY 6LzeT7TAaU9Jh7JEOhlosOI0uUoTlvIY3jkYXU.6bSOUQyUgC9nj0SvKvS8wVldJIfe3tWBCHQWM xaxQlMazujzfVmxXpb24qfZF0reskpbN_w_DCoVuXitYLhg_nOJFslnDz6wvOkF2SAFsqn_bsXMq yJlvY8EsjvJOQtQnGeprvz5e4mTlih1rUV2YuZu9HjqO6PBMXfZ9ss0BXqK5DuyHNq2VGhuQFZCx yIRxSJkPHi394YErMPRBG1IYqMSsfhjLe.dCTtvIwnsGhsY1vvxs4T6kgUq_88vRxVUpCw5nEjEG LnvEfMTUAFU00vY7qg1yEDN5HwDOA.WxLykzxJLBPddGR.ZPLDmLT5X29UOn0kBJlk7xn2kCd0zF 49I9uZpQQOKd6Z.xlaszgtYLeOUlZ1ihyuPgFu9j1KAcUnol4IIfU2bOOkGAgDkIvPcbfWE33c6D 8QFdoG4_FYmChxCmwV4khGc4EHEAcltrRedUwhDfPwoGNUC.qF867xFhMYWWLETr47ImXAk9i8QJ AaPAE2xCGzSjAsp5X8anD6ObPi8vymOgxc7oH8WqiuEK6_.DvyOirIoOe4uqAHeiLfVw3yxT3VPP hiWcUMDe7EzJ5c_5cT5b7wz4- Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.bf2.yahoo.com with HTTP; Tue, 3 Mar 2020 15:50:10 +0000 Date: Tue, 3 Mar 2020 15:50:08 +0000 (UTC) From: Karen Goh To: Pavel Stehule Cc: pgsql-sql@lists.postgresql.org Message-ID: <365718595.2569244.1583250608503@mail.yahoo.com> In-Reply-To: References: <1063179739.1806448.1583057868061.ref@mail.yahoo.com> <1063179739.1806448.1583057868061@mail.yahoo.com> Subject: Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2569243_1922496917.1583250608502" X-Mailer: WebService/1.1.15302 YMailNodin Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:73.0) Gecko/20100101 Firefox/73.0 Content-Length: 12089 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2569243_1922496917.1583250608502 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Pavel, Using this as reference and your link : https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgr= esql-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 wrote: =20 =20 =20 ne 1. 3. 2020 v=C2=A011:18 odes=C3=ADlatel Karen Goh = napsal: Hi, I hope I am posting on the right forum.=C2=A0 I googled but I can't find an= y solution pertaining to my problem. Could someone know what is the syntext for last_insert_id() in Postgresql f= or me to insert into my sql execute query? Postgres has not last_insert_id function. Maybe you think "lastval" functio= n https://www.postgresql.org/docs/current/functions-sequence.html Regards Pavel public int getTutorById() { =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 openConnection(); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 int tutor_id =3D 0; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 try { =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Statement stmt3 =3D connection.createStatement(); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 ResultSet rs =3D stmt3.executeQuery("SELECT last_insert_id() fro= m xtutor"); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 { =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 while (rs.next()) { =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 tutor_id= =3D rs.getInt(1);=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 } =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 } =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 } catch (SQLExcepti= on e) { =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 e.printStackTrace(); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 } =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return tutor_id; =C2=A0 =C2=A0 =C2=A0 =C2=A0 } org.postgresql.util.PSQLException: ERROR: function last_insert_id() does no= t exist =C2=A0 Hint: No function matches the given name and argument types. You mig= ht need to add explicit type casts. =C2=A0 Position: 8 =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.core.v3.QueryExecutorImpl.rec= eiveErrorResponse(QueryExecutorImpl.java:2440) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.core.v3.QueryExecutorImpl.pro= cessResults(QueryExecutorImpl.java:2183) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.core.v3.QueryExecutorImpl.exe= cute(QueryExecutorImpl.java:308) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.executeInter= nal(PgStatement.java:441) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.execute(PgSt= atement.java:365) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.executeWithF= lags(PgStatement.java:307) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.executeCache= dSql(PgStatement.java:293) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.executeWithF= lags(PgStatement.java:270) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at org.postgresql.jdbc.PgStatement.executeQuery= (PgStatement.java:224) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at daoMySql.tutorDAOImpl.getTutorById(tutorDAOI= mpl.java:202) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at daoMySql.tutorDAOImpl.getAlltutors(tutorDAOI= mpl.java:161) =C2=A0 =C2=A0 =C2=A0 =C2=A0 at business.manager.getAlltutors(manager.java:9= 9) The generated id is successfully generated by JDBC. The errors when I tested in out using PGAdmin4 is=20 ERROR:=C2=A0 function last_insert_id() does not exist LINE 1: SELECT last_insert_id() from xtutor; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^ HINT:=C2=A0 No function matches the given name and argument types. You migh= t need to add explicit type casts. SQL state: 42883 Character: 8 Postgresql 11, Windows 11 Thanks. =20 ------=_Part_2569243_1922496917.1583250608502 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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 l= astval('t_id') from table_tutor;

but it is not working.
<= br>Is there any tutorial out there that teaches the exact syntax ?

T= hanks & regards,
Karen


ne 1. 3. 2020 v 11:18 odes=C3=ADlatel Karen Goh <karenworld@yahoo.com&g= t; napsal:
Hi,

I hope I a= m posting on the right forum.  I googled but I can't find any solution= pertaining to my problem.

Could someo= ne know what is the syntext for last_insert_id() in Postgresql for me to in= sert into my sql execute query?

Postgres has not last_insert_id function. Maybe you = think "lastval" function


= Regards

Pavel


public int getTutorById() {
  &n= bsp;             openConnection();
                int tutor= _id =3D 0;
            &nbs= p;   try {
           =             Statement stmt3 =3D connection.c= reateStatement();
          &nbs= p;             ResultSet rs =3D stmt3.execute= Query("SELECT last_insert_id() from xtutor");
  &nbs= p;                     {<= br clear=3D"none">                &= nbsp;               while (rs.next()) {<= br clear=3D"none">                &= nbsp;                    =   tutor_id =3D rs.getInt(1);           =                     &nbs= p;                     &n= bsp;
              &n= bsp;                 }
                   =     }
           = ;     } catch (SQLException e) {
    =                     e.pri= ntStackTrace();
           =     }
           = ;     return tutor_id;
      &nb= sp; }

org.postgresql.util.PSQLExceptio= n: ERROR: function last_insert_id() does not exist
 = Hint: No function matches the given name and argument types. You might nee= d to add explicit type casts.
  Position: 8
        at org.postgresql.core.v3.QueryExecut= orImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
=         at org.postgresql.core.v3.QueryExecutorImpl.pro= cessResults(QueryExecutorImpl.java:2183)
    &n= bsp;   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecut= orImpl.java:308)
        at org.postg= resql.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.ex= ecuteCachedSql(PgStatement.java:293)
     =   at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.jav= a:270)
        at org.postgresql.jdbc= .PgStatement.executeQuery(PgStatement.java:224)
  &n= bsp;     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 PGA= dmin4 is

ERROR:  function last_i= nsert_id() does not exist
LINE 1: SELECT last_insert_id()= from xtutor;
            &= nbsp;  ^
HINT:  No function matches the given n= ame and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

Postgresql 11, Windows 11

Thanks.


<= br clear=3D"none">
= ------=_Part_2569243_1922496917.1583250608502--