Re: updating sequence value for column 'serial'

On 9/25/19 10:12 PM, Matthias Apitz wrote:
> El día miércoles, septiembre 25, 2019 a las 07:42:11a. m. -0700, Adrian Klaver escribió:
>
>>>>> sisis$# DECLARE
>>>>> sisis$# maxikatkey integer := ( select max(katkey) from titel_daten );
>>>>> sisis$# result integer := 1;
>>>>> sisis$# BEGIN
>>>>> sisis$# maxikatkey := maxikatkey +1;
>>>>> sisis$# RAISE NOTICE '%', maxikatkey ;
>>>>> sisis$# result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey) );
>>>>> sisis$# RAISE NOTICE '%', result ;
>>>>> sisis$# END $$;
>>>>> NOTICE: 330722
>>>>> NOTICE: 330723
>>>>> DO
>>>>>
>>>>> Is there any better way? Thanks
>>>>
>>>> I have not found a better way. I use ALTER SEQUENCE .. RESTART 330722
>>>> though:
>>>
>>> Yes, but I found no way to use a variable like 'maxikatkey' in the ALTER SEQUENCE ...
>>> it only excepts digits like 330722.
>>
>> DO $$
>> DECLARE
>> max_id int;
>> BEGIN
>> SELECT INTO max_id max(id) + 1 FROM seq_test;
>> RAISE NOTICE 'Max id is %', max_id;
>> EXECUTE 'ALTER SEQUENCE seq_test_id_seq RESTART ' || max_id::text;
>> END;
>> $$ LANGUAGE plpgsql;
>
> Hi Adrian,
>
> I adopted your code to the name of my table 'ig_target_ipfilter' and its
> SERIAL column 'id'; it does not work (and I don't know how it could
> works because in the 'ALTER SEQUENCE ...' stmt is somehow missing '... WITH value ...')
> or do I understand something wrong?):
>
> cat -n /home/apitzm/postgreSQL/test.sql
> 1 DO $$
> 2 DECLARE
> 3 max_id int;
> 4 BEGIN
> 5 SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
> 6 RAISE NOTICE 'Max id in % is %', 'ig_target_ipfilter', max_id;
> 7 EXECUTE 'ALTER SEQUENCE ig_target_ipfilter_id_seq RESTART ' || max_id::text;
> 8 END;
> 9 $$ LANGUAGE plpgsql;
>
> psql -Usisis -dsisis < /home/apitzm/postgreSQL/test.sql
> NOTICE: Max id in ig_target_ipfilter is <NULL>
> ERROR: query string argument of EXECUTE is null
> KONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE
>
> Please clarify. Thanks

I forgot about the possibility of NULL being returned by max_id in:

SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;

So:

SELECT INTO max_id COALESCE(max(id), 0) + 1 FROM ig_target_ipfilter ;

That will turn a NULL max(id) into 0.

>
> matthias
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com