CODEWITHSUNDEEP
postgresqlsql-insertplpgsqlpostgresql-13
volume one
volume one

Reputation: 7563

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

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?

Upvotes: 2

Views: 1235

Answers (2)

user330315
user330315

Reputation:

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;

Upvotes: 2

Mowazzem Hosen
Mowazzem Hosen

Reputation: 507

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

Upvotes: 1

Related Questions