How to Set Auto Increment Primary Key in Postgresql

How to set auto increment primary key in PostgreSQL?

Try this command:

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

Try it with the same DB-user as the one you have created the table.

Primary key not AUTO INCREMENT in the PostgreSQL

Change the data type to serial, which is Postgres's way of spelling auto_increment. If you have a not-NULL integer column with no default, then you will get an error when you attempt an insert.

If you assign a default, then the unique constraint (part of the primary key) will just create a duplicate key error on the second insert.

How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

(Updated - Thanks to the people who commented)

Modern Versions of PostgreSQL

Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL:

   ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

Older Versions of PostgreSQL

In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work. The following sequence of commands should do the trick:

  ALTER TABLE test1 ADD COLUMN id INTEGER;
  CREATE SEQUENCE test_id_seq OWNED BY test1.id;
  ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

Again, in recent versions of Postgres this is roughly equivalent to the single command above.

Change primary key to auto increment

I figure it out: just add an auto-increment default value to the playerID:

create sequence player_id_seq;
alter table player alter playerid set default nextval('player_id_seq');
Select setval('player_id_seq', 2000051 ); --set to the highest current value of playerID

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

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.

How to add auto increment in postgreSQL?

SERIAL will create an integer column for you so you don't need to specify it.

create table tblIK(
    upID SERIAL primary key,
    upName varchar(100) NOT NULL,
    upMin varchar(100) NOT NULL,
    upMax varchar(100) NOT NULL,
    upYEAR Date NOT NULL,
    upMi varchar(100)NOT NULL,
    upIK varchar(100)NOT NULL
)

SQL auto increment pgadmin 4

For Postgres you have to use SERIAL

CREATE TABLE Finance
(
    IDNumber SERIAL PRIMARY KEY,
    FinName varchar(50) NOT NULL
);

---

Related Topics