5

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?

alextc
  • 3,206
  • 10
  • 63
  • 107

2 Answers2

6

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, this would've taken me ages to figure out. I inserted through dbeaver and ID was generated, through hibernate I received NOT NULL CONSTRAINT VIOLATION – nykon Dec 06 '21 at 08:24
4

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    Serial is just a shorthand when creating the table, afterwards you’d create a sequence and edit the default value to `nextval(‘sequencename’)` to achieve the same. – Sami Kuhmonen Jul 24 '18 at 06:03
  • @SamiKuhmonen If you're commenting with regards to how to handle the existing data fine, but in [modern versions](https://stackoverflow.com/questions/2944499/how-to-add-an-auto-incrementing-primary-key-to-an-existing-table-in-postgresql) of Postgres, the one-liner I gave should suffice for adding a serial column. – Tim Biegeleisen Jul 24 '18 at 06:04
  • Yes, it was for your before-edit comment, and works if the data needs to be kept. Otherwise your suggestion is correct. – Sami Kuhmonen Jul 24 '18 at 06:05