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.