2

I made a mistake by the creation of my table. The primary key was incorrect. I delete the constraint and now I don't have a primary key in my table, only the field with the data. Now I want to set again this field as auto_increment primary key without losing my data. How I can do this? I tryed this:

ALTER TABLE name_table ADD COLUMN name_column serial primary key;

But with this I am losing my data and creating a new column, that I don't want

zgrizzly_7
  • 773
  • 2
  • 10
  • 22
  • have you tried this? http://dba.stackexchange.com/questions/8814/how-to-promote-an-existing-index-to-primary-key-in-postgresql – grahamrhay Oct 28 '15 at 08:45
  • http://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres and http://stackoverflow.com/questions/20593946/postgresql-reconfigure-existing-table-changing-primary-key-to-type-serial –  Oct 28 '15 at 08:47
  • I tryed this solution @a_horse_with_no_name, but not result – zgrizzly_7 Oct 28 '15 at 08:58
  • "*but no result*" is not a valid Postgres error message (you should add the **exact** commands you have tried - because I'm 100% that those questions _will_ solve your problem) –  Oct 28 '15 at 09:02

3 Answers3

0

try this 

ALTER TABLE table_name ADD CONSTRAINT some_name primary key (name_column);
hard coder
  • 5,449
  • 6
  • 36
  • 61
0

For my suggestion,

  1. backup your database first in sql or csv or xml or excel something restore-able.
  2. Then alter your table structure, column data type, from UI or command
  3. Then if data recorded on your table are lost or gone, restore your backup data only, (not the structure of table)

After that you have changed column data type and also get your required data. I hope it will work.

Htoo Maung Thait
  • 105
  • 2
  • 12
0

Hi guys I was trying several ways and I found this one and maybe also somebody later can use:

Create a sequenz: Sequenz is the way that Postgresql implement to generate auto_increment fields. Ones we have a auto_increment is also a primary key. Should not be like this, is not a rule, but in most of the cases a auto_increment field is a primary key. To create a sequenz is like this:

CREATE SEQUENCE exemplo_id_seq
    INCREMENT 1 --the increment upgrate will be made 1 + 1
    MINVALUE 1
    MAXVALUE 
    START 1 --the start counting is in 1
    CACHE 1;

After this is only to give this sequenz to the affected field using NEXTVAL, like this:

ALTER TABLE table_name ALTER COLUMN id SET DEFAULT NEXTVAL("exemplo_id_seq"::regclass);

Is working good without losing the data from old errors

zgrizzly_7
  • 773
  • 2
  • 10
  • 22
  • This is exactly the solution from the questions I linked to (e.g. http://stackoverflow.com/q/9490014/330315) where you claimed that it wouldn't work. –  Oct 28 '15 at 09:27
  • Hi @a_horse_with_no_name you are right! the problem was that I just read the first answer in the post, and the second answer was a little down is exactly my solution. But thx anyway. The first answer look a little to complicated for me :) – zgrizzly_7 Oct 28 '15 at 09:28