Update serial value to avoid duplicate key error (Postgres)

While working with Postgres, I met the following error.

duplicate key value violates unique constraint \"author_pk\"

Even though I had set SERIAL to Author.ID (SERIAL enables Postgres to handle the value of ID incrementally).

CREATE table authors (
    id SERIAL,
    name text,
    country text,
    CONSTRAINT author_pk PRIMARY KEY(id)
);

Why is auto increment not working?

There would be several situations to accidentally disable auto-increment, but in my case, it happened after inserting rows manually with an SQL query. So I tried to set the SERIAL value by setval() explicitly. But it ended up having the same ID every time migration runs, then having a duplicate key error.

INSERT INTO authors (id, name, country)
values
    (1, 'Kazuo Ishiguro', 'England'),
    (2, 'Haruki Murakami', 'Japan')
ON CONFLICT do nothing;
SELECT setval('authors_id_seq', 3, true);

Get the latest value of SERIAL

Using nextval() you can get the next SERIAL value. So if you use nextval() as argument of setval(), SERIAL value will stay always fresh and correct.

INSERT INTO authors (id, name, country)
values
    (1, 'Kazuo Ishiguro', 'England'),
    (2, 'Haruki Murakami', 'Japan')
ON CONFLICT do nothing;

SELECT setval('authors_id_seq', nextval('authors_id_seq'), false);
-- or
SELECT setval('authors_id_seq', nextval('authors_id_seq') - 1);