is it possible to modify identify columns start with value in PostgreSQL 13

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

I have a table rss_sub_source, and the id as a identify column. Now I want to modify the start with value(I forget to define the start with value when I added identify column). I have tried this way:

ALTER TABLE rss_sub_source 
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY 
        (START WITH 1233);

shows this column already as a identify column. Then I modify the SQL like that:

ALTER TABLE rss_sub_source 
    ALTER id ADD GENERATED ALWAYS 
        (START WITH 1233);

did not work shows SQL Error [42601]: ERROR: syntax error at or near "(" Position: 74. What should I do to make it work? This command still not work:

 ALTER TABLE rss_sub_source 
    ALTER id START WITH 1233;

this is my table create command:

-- Drop table

-- DROP TABLE public.rss_sub_source;

CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    updated_time bigserial NOT NULL,
    sub_status int2 NOT NULL DEFAULT 1,
    rss_type varchar(64) NOT NULL,
    standard_type varchar(32) NOT NULL DEFAULT 'RSS'::character varying,
    standard_version varchar(32) NOT NULL DEFAULT 'rss20'::character varying,
    cron varchar NOT NULL DEFAULT '0 */1 * * * *'::character varying,
    trigger_count int4 NOT NULL DEFAULT 0,
    next_trigger_time timestamp NULL,
    sub_name varchar(256) NOT NULL,
    last_trigger_time timestamptz NULL,
    tags _int4 NULL,
    source_url varchar(256) NULL,
    sub_type varchar NULL,
    intro varchar NULL,
    remark varchar NULL,
    title_hash varchar NULL,
    failed_count int4 NOT NULL DEFAULT 0,
    lang varchar NULL,
    frequency_month int4 NULL DEFAULT 0,
    reputation int4 NULL DEFAULT 0,
    rep_lastest_refresh_time int8 NULL DEFAULT 0,
    scrapy_take_time int4 NULL DEFAULT 0,
    follower int8 NULL,
    censor_status int4 NULL DEFAULT 1,
    etag varchar(64) NULL,
    last_modified varchar(32) NULL,
    editor_pick int4 NULL DEFAULT 0,
    fav_icon_url varchar(128) NULL,
    dynamic_interval int4 NOT NULL DEFAULT 0,
    local_icon_url varchar(256) NULL,
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

the table already have 1232 rows. I am using this command to modify the start with value:

ALTER TABLE rss_sub_source 
ALTER id SET START WITH 1233;

it execute success but seems not work. How to check the table primary key current start with value?When I am using this command to check the current seed value of id:

select CURRVAL(pg_get_serial_sequence('public.rss_sub_source', 'id'))

shows greater than 1200. But when I insert a new sql in my app, still get a small seed id.

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

You can use setval() to change the current value of the underlying sequence.

To get the sequence’s name use pg_get_serial_sequence()

select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), 1234);

or more dynamic:

select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), max(id))
from public.rss_sub_source;

Online demo

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

Leave a Reply