Re: Auto-Increment in Postgres

Dear all,

I have an Integer column in Postgres database table. Let's say the
column has below values :

1
2
3
4
5
6
7
8
9
10

Now if i deleted some rows where id= 3 ,5 and 8 or it have these type
of data then

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8 .

I follow these steps

*alter table meta_test drop column metadataid;

alter table meta_test add metadataid serial;*

But this adds the column at the end but i want to it as primary key.

Please help how to achieve it.

Thanks & best Regards
Adarsh Sharma

On 11 February 2011 11:13, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
> Dear all,
>
>
> I have an Integer column in Postgres database table. Let's say the column
> has below values :
>
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
>
> Now if i deleted some rows where id= 3 ,5 and 8  or it have these type of
> data then
>
> The data look like as :
>
> 1
> 2
> 4
> 6
> 7
> 9
> 10
>
> I want to have it id's as
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> and next data is inserted right at 8 .
>
> I follow these steps
>
> alter table meta_test drop column metadataid;
>
> alter table meta_test add metadataid serial;
>
> But this adds the column at the end but i want to it as primary key.
>
> Please help how to achieve it.

If it's your primary key, changing the values every time you remove
anything isn't really treating it as a key so much as ranking them in
order of insertion.

Why do you want to reset the sequence? If it's a primary key, it
shouldn't matter.

But if you really insist, you can do this:

ALTER SEQUENCE meta_test_metadataid_seq RESTART WITH 1;

UPDATE TABLE meta_test set metadataid = nextval('meta_test_metadataid_seq');

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 02/11/2011 12:13 PM, Adarsh Sharma wrote:
> Dear all,
>
>
> I have an Integer column in Postgres database table. Let's say the
> column has below values :
>
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
>
> Now if i deleted some rows where id= 3 ,5 and 8 or it have these type
> of data then
>
> The data look like as :
>
> 1
> 2
> 4
> 6
> 7
> 9
> 10
>
> I want to have it id's as
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> and next data is inserted right at 8 .
>
> I follow these steps
>
> *alter table meta_test drop column metadataid;
>
> alter table meta_test add metadataid serial;*
>
> But this adds the column at the end but i want to it as primary key.
>
> Please help how to achieve it.
>
>
> Thanks & best Regards
> Adarsh Sharma
1. The SERIAL data type will not give you what you want. SERIAL will
increment by one for each INSERT regardless of the content of you table
(and, specifically, the value of you "metadataid" field). SERIAL does
not check for unused ids, if you need this you should probably write
trigger procedure to handle that.

2. SERIAL does not make the field unique, to do that use "SERIAL UNIQUE".

3. ALTER TABLE tablename ADD PRIMARY KEY metadataid ;

4. Once you added a serial with ALTER TABLE a sequence is created that
start counting on 1. The next insert will add a row with metadataid = 1.
If you have a UNIQUE or PRIMARY KEY constraint this will fail if there
already is a row with metadataid set to 1. SERIAL has no magic that
handle this.

Useful resources:
http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL
http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html
http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html

/Fredric

"But this adds the column at the end." - column order in the table
definition is unreliable; when you output you should specify the column
order yourself.

As to populating within a sequence that has missing values that is more
trouble than it is worth.

If you want to add a column and make it the primary key you need to indicate
such in your ALTER TABLE statement by adding "PRIMARY KEY" to the end of it:
"ALTER TABLE t ADD COLUMN c serial PRIMARY KEY"

If you really feel you need to do as you explain I would advise giving more
context as to what and why you are doing that so alternative solutions can
be presented.

David J

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Adarsh Sharma
Sent: Friday, February 11, 2011 6:14 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Auto-Increment in Postgres

Dear all,

I have an Integer column in Postgres database table. Let's say the column
has below values :

1
2
3
4
5
6
7
8
9
10

Now if i deleted some rows where id= 3 ,5 and 8 or it have these type of
data then

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8 .

I follow these steps

alter table meta_test drop column metadataid;

alter table meta_test add metadataid serial;

But this adds the column at the end but i want to it as primary key.

Please help how to achieve it.

Thanks & best Regards
Adarsh Sharma