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?
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.
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.
nextval(‘sequencename’) to achieve the same.