On Friday, May 12, 2023, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2@xxxxxxxxxx> 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.
