21

I have a table Login. It has the fields rank, username and password.

I want the rank field value to be auto incremented with respect to addition of username and password.

How do I do this in PostgreSQL ?

simanacci
  • 2,197
  • 3
  • 26
  • 35
  • 2
    Btw, you have a small typing error in your question. It's increment not increament. If you change it, more people will find this question, plus the related questions will be more appropriate. – pyrocumulus Jun 24 '10 at 09:55
  • This is about an existing table, while the other question is more about new tables IMO. So it's not really a duplicate, unless you merge them. – DanMan Jan 19 '11 at 11:31
  • As of PostgreSQL 10, the recommended way is to use `INTEGER PRIMARY KEY generated always as identity` instead of `SERIAL`. – TarHalda Oct 11 '22 at 14:56

3 Answers3

49

You are looking for a column with datatype Serial. See this page (bottom) for more information about that datatype.

So for example, your table definition could look like this:

CREATE TABLE yourtable (
    rank SERIAL NOT NULL,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(50) NOT NULL
);
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
pyrocumulus
  • 9,072
  • 2
  • 43
  • 53
33

A Sequence can be created which will auto increment the value of rank column.

CREATE SEQUENCE rank_id_seq;

CREATE TABLE yourtable (
    rank INTEGER NOT NULL default nextval('rank_id_seq'),
    username VARCHAR(20) NOT NULL,
    password VARCHAR(50) NOT NULL
);

ALTER SEQUENCE rank_id_seq owned by yourtable.rank;
sjngm
  • 12,423
  • 14
  • 84
  • 114
Sanjay Kumar
  • 331
  • 2
  • 2
4
create table login (rank serial, username varchar(20), password varchar(20))

Serial datatype is what you want.

Timothy
  • 2,457
  • 19
  • 15