Re: pgsql code is not working.

On Friday, May 12, 2023, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2(at)kemper(dot)com> wrote:
>
>
>>
>>
>> SELECT setval(additional_details_id_seq,MAX_VAL + 1);
>>
>
>>
>> Raise notice ‘Value pkey: %’,pkey;
>>
>> Raise notice ‘Value max_val: %’,max_val;
>>
>> END;
>>
>> $$ LANGUAGE plpgsql;
>>
>>
>>
>>
>>
>> ERROR: invalid input syntax for type integer: "additional_details_id"
>>
>> CONTEXT: PL/pgSQL function inline_code_block line 14 at SQL statement
>>
>> SQL state: 22P02
>>
>>
>>
>>
>>
>> But when I change the data type of ‘MAX_VAL’ variable from INTEGER to
>> VRACHAR then got this result.
>>
>
> You get an error about the first argument to your function so you go and
> change the second one. That doesn't seem like a production debugging
> choice. You main issue there was the lack of single quotes, which you seem
> to have later fixed. But given you seem to understand the MAX_VAL is indeed
> a varchar, and 1 is an integer, the error that there is no addition
> operator between those shouldn't come as a surprise - how would you add
> those together?
>

Never mind that, the error in question is happening before the setval call
where your quoting typo exists in the example code. Changing to varchar
and raise notice works, though the original error message provided the
exact same information.

David J.