How do I insert an empty row, but have the serial update correctly?

Question

I would like to insert a blank record into a table and have its serial primary key value update. I would then like to get the new value and insert it into a temporary table. This will take place within a function using language plpgsql.

So far I have this:

CREATE TEMP TABLE _InsertedpostID ( -- to store inserted postid
        postid int
    );

    INSERT INTO post
    (
        postid, --serial which needs to be held in the temp table above
        title,
        location
    )
    VALUES(NULL);

    --- here I need to get the just inserted postid serial and put it into the _InsertedpostID table

The above does not insert anything (I grabbed the solution from a MySQL answer). It returns an error of:

[42601] ERROR: INSERT has more target columns than expressions

Removing the VALUES(NULL); part does not work either like it does in SQL Server. How can I therefore insert a blank record with only the serial updating?

Once a new record is generated with a new serial number, how do I output that back into the temp table?

@AdrianKlaver sadly doesnt work - still get an error of `[42601] ERROR: INSERT has more target columns than expressions` — volume one, Jan 16 '21 at 00:33
You sure, a test case here worked for me:`insert into ser_test (id) values (DEFAULT); select * from ser_test; id | fld_1 | fld_2 ----+-------+------- 1 | NULL | NULL` — Adrian Klaver, Jan 16 '21 at 00:51
The whole approach seems rather overcomplicated. A dummy row (which you may even delete late) and a temp table just to increment a sequence? What's wrong with simply calling `nextval()` to get the next value? — , Jan 16 '21 at 08:44

score 1 · Answer 1 · answered Jan 16 '21 at 05:19

As you will create a function I have created for you.

Please check and let me know.

CREATE TABLE post (
    postid serial, --post_postid_seq will be auto generated
    title text,
    "location" text
);

CREATE TEMP TABLE _InsertedpostID ( -- to store inserted postid
    postid int
);

CREATE OR REPLACE FUNCTION public.InsertAndReturnID()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
    id integer:=0;
begin
    insert into post(postid) values(default);
    id:=(select currval('post_postid_seq')::integer);
    insert into _InsertedpostID(postid) values(id);
end;
$function$
;

score 1 · Accepted Answer · answered Jan 16 '21 at 08:40

1

You don't really need PL/pgSQL for that. If post.postid really is a serial (an identity would be better), then the following will work:

create temp table _insertedpostid (
    postid int
);

with new_post as (
  insert into post (postid)
  values(default)
  returning postid
)
insert into _insertedpostid (postid)
select postid
from new_post;

However, if this is really inside a PL/pgSQL function, there is no need for a costly temp table:

....
declare
  l_postid integer;
begin
  insert into post (postid) values (default)
  returning postid
  into l_postid;
  
  --- work with l_postid
end;

If you only want to increment the column's sequence and you don't really need the new row (which seems likely, given the fact that you don't provide any column values at all), then why don't you simply call nextval()?

 select nextval(pg_get_serial_sequence('post', 'postid'));

In PL/pgSQL you can simply assign that to a variable without the need for a dummy row:

....
declare
  l_postid integer;
begin
  ...
  l_postid := nextval(pg_get_serial_sequence('post', 'postid'));
  ....
end;

answered Jan 16 '21 at 08:40

So the inserted row is intially blank (it creates a draft `post` with nothing yet filled in). It will then use the generated `postid` to maintain the draft post until it is published. The row would only get deleted if the user deletes their draft and never publishes. Why do you mean by `identity` would be better? The `postid` has a datatype of `serial` which I thought was the only way to have an autoincrementing id? – volume one Jan 16 '21 at 17:08
1

Modern Postgres versions support `identity` column as the [preferred alternative](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial) to `serial` – Jan 16 '21 at 17:19
Wow I didn't know that. Now I have to go through some 50 tables and alter them to use `identity` instead... – volume one Jan 16 '21 at 18:30
@volumeone: no, you don't really need to change them. You can still keep the serial. But you might want to consider identity for new tables/columns – Jan 16 '21 at 19:32
I would much rather use `identity` - its what I used on SQL Server prior to this migration exercise across to postgresql. Oddly, SQL Server 2018+ started discouraging identity and promoted sequences instead for auto incrementing identities! – volume one Jan 16 '21 at 21:51
`identity` columns use sequences in the background as well. – Jan 16 '21 at 21:55
Well yes but not as a separate object that needs to be maintained. I really don't like sequences or care for them. They keep selecting a new value regardless of whether that value was used or not. – volume one Jan 16 '21 at 23:20

How do I insert an empty row, but have the serial update correctly?

2 Answers2