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.klaver@aklaver.com