PostgreSQL: INSERT into and get the new ID for usage in LO-BASE

Question

I'd like to add a line to a table:

CREATE TABLE actors (
    id_act serial NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    CONSTRAINT actors_pkey PRIMARY KEY (id_act)
); 
INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks');

Using dBeaver, this statement provides the new ID:

select CurrVal(pg_get_serial_sequence('actors', 'id_act'));

With LibreOffice-BASE, I have to add the name of the scheme and this results in ERROR: column "scheme_name.table_name" does not exist I've got the same error using: "scheme_name.table_name" "scheme_name"."table_name" "table_name"

How can I get the new ID for further usage (calculation, check, ...)? I don't mind to use CurrVal or RETURNING or something else. But I don't find the proper syntax.

Thank you!

score 1 · Answer 1 · answered May 24 '20 at 09:21

1

The simplest option is to use the RETURNING clause in your INSERT query:

INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks') 
RETURNING id_act;

answered May 24 '20 at 09:21

GMB

216,147
25
84
135

tried this but BASE returns an error: Type: com.sun.star.sdbc.SQLException Message: SQL-Code provided a result, but none was expected. Found a similar example [link] (https://www.postgresqltutorial.com/postgresql-insert/), last Chapter "Get the last insert id": **I need to get that "8" from there for storgae somewhere else, – StOMicha May 24 '20 at 10:41
@StOMicha: I don't know about BASE, but you probably need to change the method that executes the query to something that allows a return value. – GMB May 24 '20 at 10:48

score 0 · Answer 2 · answered May 24 '20 at 12:16

You can use an insert inside a CTE and then return the value:

WITH i AS (
      INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks') 
          RETURNING id_act
     )
SELECT i.*
FROM i;

The outer query is a SELECT, so your UI should be comfortable with it returning a value.

You can even continue the processing in this statement -- by adding more CTEs for instance -- so you don't need to actually fetch the value.

score 0 · Answer 3 · edited May 24 '20 at 21:53

There are three ways, which will provide you the new id's

Using SERIAL and PRIMARY KEY, postgres will automatically insert an unique value

   CREATE TABLE actors (
     id_act SERIAL PRIMARY KEY
     first_name text NOT NULL,
     last_name text NOT NULL
   );

If you have sequence, you can use this sequence while creating DDL and every time data gets inserted, new id will be generated

 CREATE TABLE actors (
   id_act integer NOT NULL DEFAULT nextval('sequence_name')
   first_name text NOT NULL,
   last_name text NOT NULL,
   CONSTRAINT actors_pkey PRIMARY KEY (id_act)
 );

If you have sequence, use the sequence in the insert query

  CREATE TABLE actors (
    id_act integer,
    first_name text NOT NULL,
    last_name text NOT NULL,
    CONSTRAINT actors_pkey PRIMARY KEY (id_act)
  ); 


INSERT INTO actors (id_act, first_name, last_name) VALUES (nextval('sequence_name'), 'Tom', 'Hanks');

PostgreSQL: INSERT into and get the new ID for usage in LO-BASE

3 Answers3