How to auto-increment in PostgreSQL?

Question

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 ?

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

score 48 · Answer 1 · edited Jun 24 '10 at 10:06

48

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
);

edited Jun 24 '10 at 10:06

Milen A. Radev

57,132
21
101
108

answered Jun 24 '10 at 09:47

pyrocumulus

8,604
2
38
53

@Milen A. Radev, thanks for adding that anchor to my link :) – pyrocumulus Jun 24 '10 at 10:12
1

The autoincrement works like this: `insert into yourtable (username, password) values ('moobars', 'boobars');` Then the database autoincrements the rank column. – Eric Leschinski Oct 17 '12 at 00:56
4

SERIAL is not a datatype, just an alias for an integer with a sequence as default (see Sanjay Kumar's asnwer) – leonbloy Jun 30 '13 at 21:56

score 31 · Accepted Answer · edited Dec 11 '11 at 17:50

31

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;

edited Dec 11 '11 at 17:50

sjngm

11,933
13
78
107

answered Jun 27 '10 at 19:42

Sanjay Kumar

311
2
2

score 4 · Answer 3 · answered Jun 24 '10 at 09:49

4

create table login (rank serial, username varchar(20), password varchar(20))

Serial datatype is what you want.

answered Jun 24 '10 at 09:49

Timothy

2,427
19
15

How to auto-increment in PostgreSQL?

3 Answers3

Linked