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 1j9A2T-0000T7-Bg for pgsql-sql@arkaria.postgresql.org; Tue, 03 Mar 2020 16:04:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j9A2Q-0005sY-W2 for pgsql-sql@arkaria.postgresql.org; Tue, 03 Mar 2020 16:04:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j9A2Q-0005s4-H3 for pgsql-sql@lists.postgresql.org; Tue, 03 Mar 2020 16:04:54 +0000 Received: from mail-pf1-x444.google.com ([2607:f8b0:4864:20::444]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j9A2M-00030Z-Lh for pgsql-sql@lists.postgresql.org; Tue, 03 Mar 2020 16:04:53 +0000 Received: by mail-pf1-x444.google.com with SMTP id j9so1665620pfa.8 for ; Tue, 03 Mar 2020 08:04:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jfcomputer-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=3ekVSybi/XKTP99iMlbjuO9yevln8vaafVDD53tW248=; b=PJbGGOiiYoI0Owg14VbXcJ+pa8iuJKMiH9jvWc2f17Wsn82MPAQs6Gx5u39sttfqL/ hvU605OsPcZfTXNHyIHPP2m9X64ctn7w6p1ppcdRD0OJAwADpgaNdhcafqbT8T9NLGQf 2TKoAu5F3mTw+2aUslyX6LYYnxum8vIanD30SM2+iQA0Veih00swTfB0RPHQipkB5wds JAGUrOzJkJKogzOMINayTuCv0lyQKU2PlQtQKIv7P+G4KnhRdfmGYmk3T9tRLRXwprbW zM6AveFqC5F2i/YmnAkUaBr4u0I24VesjbSOH/CGiZKxysp3VFn7oXHf+ZPr7SCXrcxT KnBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=3ekVSybi/XKTP99iMlbjuO9yevln8vaafVDD53tW248=; b=JCtrdz9KlLP9jk4JnIdEbdsolS6rzoLf3Vn/gCCMpVeEaFtHbQ6V19AxGLoTDZLNsC DVUGeiC+MVA46dn6VjGTtPPkb3ij7zUk2R5uykY2k0DYgo28HeH04AxBhOsnaU1/S4hT ljrwFlQL7gcrQIHVUkEg8zVPfgIDgLDGON1KIvlTFbRhYNlOfSr+j2JlO7H5VDD6JKGR tDMHMFooGhpWAaQZNq4nHl39gPU03IOo7hclKV5Yd+O+vHymmbmHJ2wMiE1pvKXTJSSU Hw5TCkSnrfFf/D/6FpW2o3kxyhZaZrNMizJjLJnZt1yHtjl3AaZHmGEk2KpgQ1UoLiv6 EuFA== X-Gm-Message-State: ANhLgQ0eU7fM3R8CVKKQgvxFTMUthLf2/CdqmrimsIg2teAqGgRsiv+t /7KyR3J4DFRVnYH00XwjhqooVuXKRPk= X-Google-Smtp-Source: ADFU+vueo7YVyQG7COBYJPe70xjLuDDkQXPmEJG4m7bo80Ksa/CUoXIJ2++LYLQkaHLUNHzIgKO8VQ== X-Received: by 2002:a63:6841:: with SMTP id d62mr4574203pgc.86.1583251487322; Tue, 03 Mar 2020 08:04:47 -0800 (PST) Received: from [192.168.40.126] ([76.14.161.106]) by smtp.gmail.com with ESMTPSA id w2sm17073270pfb.138.2020.03.03.08.04.45 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 03 Mar 2020 08:04:45 -0800 (PST) Subject: Re: What is the right syntax for retrieving the last_insert_id() in Postgresql ? To: pgsql-sql@lists.postgresql.org References: <1063179739.1806448.1583057868061.ref@mail.yahoo.com> <1063179739.1806448.1583057868061@mail.yahoo.com> <365718595.2569244.1583250608503@mail.yahoo.com> From: john Message-ID: <546aa670-4ff5-01dd-7c37-0e69fdfe6c85@jfcomputer.com> Date: Tue, 3 Mar 2020 08:04:44 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: <365718595.2569244.1583250608503@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------E9B47148FFC29C5B4828B934" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------E9B47148FFC29C5B4828B934 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > wrote: > > > > > ne 1. 3. 2020 v 11:18 odesílatel Karen Goh > 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. > > > --------------E9B47148FFC29C5B4828B934 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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 <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.




--------------E9B47148FFC29C5B4828B934--