Use serial ID for multiple columns and return it from INSERT

All we need is an easy explanation of the problem, so here it is.

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in MSSQL but I’m now trying to support PostgreSQL and I can’t seem to get it right.

The SQL is similar to this:

insert into mytable (name, uniquecallid) 
values (@name, @uniquecallid); 

DO
$do$
BEGIN 
IF coalesce(@UniqueCallId,'') = '' THEN 
    SET @UniqueCallId = coalesce(@CallIdPad, '')
                     || cast(varchar(50), select currval('mytable_id_seq'));
    UPDATE mytable SET UniqueCallId = @UniqueCallId
    WHERE Id = select currval('mytable_id_seq');
END IF;
END
$do$

select currval('mytable_id_seq');

This errors with a “syntax error near select”.

What can I do to get this to return the latest value inserted?

I believe this is a Npgsql issue as when inspecting the SQL that it executes the parameters are replaced with the values but the DO block doesn’t have the values in place of parameters.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Syntax errors

Immediate causes for the error:

WHERE  id = select currval('mytable_id_seq');

And a missing semicolon at the end of the DO statement:

...
$do$;

Better query

On a closer look, this does not seem to be another case of UPSERT.
It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly retrieved value for a serial ID. Try this largely simplified statement:

INSERT INTO mytable (id, name, uniquecallid) 
SELECT t.id
     , t.name
     , CASE WHEN t.uniquecallid <> '' THEN t.uniquecallid
            ELSE COALESCE(t.callidpad, '') || t.id END 

FROM (
   SELECT nextval('mytable_id_seq') AS id
        , '@name'::text             AS name         -- provide values here, once
        , '@UniqueCallId'::text     AS uniquecallid
        , '@CallIdPad'::text        AS callidpad
   ) t
RETURNING id;

An explicit cast to text (or your actual undisclosed data type(s)) is needed.

This is equivalent to the presented code, just with legal syntax and much faster – unless there are triggers or non-standard default values you did not declare …

Simpler with currval()

On an even closer look, this can be simplified further:

INSERT INTO mytable (name, uniquecallid) 
SELECT '@name'::text
     , COALESACE( NULLIF('@UniqueCallId'::text, '')
                , COALESCE('@CallIdPad'::text, '') || currval('mytable_id_seq'))
RETURNING id;

Detailed explanation:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply