PostgreSQL - create an auto-increment column for non-primary key

Question

I am with PostgreSQL 9.5 X64 integrated with the open-source Parse Server. My table has the following structure.

objectId (text with fixed 10 characters),
item_id (integer),
item_name (text with various length)

The objectId is the primary key due to use of Parse Server. It is automatically generated by Parse Server. The item_id is not a primary key. I would like to have item_id automatically increment by 1 when a new record is created. How can this be achieved in Create Table?

Answer 1

Add a default value with a sequence:

CREATE SEQUENCE mytable_item_id_seq OWNED BY mytable. item_id;
ALTER TABLE mytable ALTER item_id SET DEFAULT nextval('mytable_item_id_seq');

To make that work, you have to exclude the item_id column from all INSERT statrments, because the default value is only used if no value is specified for the column.

Answer 2

You may try making the item_id column SERIAL. I don't know whether or not it's possible to alter the current item_id column to make it serial, so we might have to drop that column and then add it back, something like this:

ALTER TABLE yourTable DROP COLUMN item_id;
ALTER TABLE yourTable ADD COLUMN item_id SERIAL;

If there is data in the item_id column already, it may not make sense from a serial point of view, so hopefully there is no harm in deleting it.