Inserting row types with a serial primary key column in PostgreSQL

125 views Asked by At

I want a simple function that takes in all record columns and creates them if it does not exist. The table here is just a dummy, my point is more about how the id (SERIAL) column must be handled when inserting data using row types.

CREATE TABLE
  some_table (
    "id" SERIAL PRIMARY KEY,
    "some_value" VARCHAR NOT NULL
  );
declare
    r_some_table_rt some_table%rowtype;
begin
    select *
    into r_some_table_rt
    from some_table
    where some_column = 'Some value';

  if r_some_table_rt.id is null then
    -- Tried without assigning r_some_table_rt.id
    -- r_some_table_rt.id := default; -- Tried this
    -- r_some_table_rt.id := currval(pg_get_serial_sequence('some_table', 'id')); -- And this
    r_some_table_rt.some_column := 'Some other value';

    insert into some_table
    values (r_some_table_rt.*)
    returning id into r_some_table_rt.id;

  end if;

  return r_some_table_rt.id;
end;

PostgresError: null value in column "id" of relation "some_table" violates not-null constraint

I tried the following

  1. Using a normal insert works perfectly fine: insert into some_table (some_column) values ('Some value');.
  2. r_some_table.id := default; -> PostgresError: DEFAULT is not allowed in this context
  3. r_some_table.id := currval(pg_get_serial_sequence('some_table', 'id')); -> PostgresError: currval of sequence "some_table_id_seq" is not yet defined in this session
1

There are 1 answers

0
HotFix On

This worked for me:

r_some_table_rt.id := nextval(pg_get_serial_sequence('some_table', 'id'));