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

user_11033295 0
Viewed 153

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!

libreoffice-base sql-insert select postgresql sql
3 Answers

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;
user_106767160

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.

user_11440350

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

  1. 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
   );

  2. 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)
 );

  3. 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');
user_44736150
Related