PostgreSQL, reconfigure existing table, changing primary key to type=serial

Question

I have to reconfigure one table with existing data so need advice/example how to do that.

My favourite example table:

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text
);

INSERT INTO kalksad1 VALUES
(12, 2, 5, 'text index 12 doc 2 row 5'),
(26, 2, 1, 'text index 26 doc 2 row 1'),
(30, 2, 2, 'text index 30 doc 2 row 2'),
(32, 4, 1, 'text index 32 doc 4 row 1'),
(36, 1, 1, 'text index 36 doc 1 row 1'),
(37, 1, 2, 'text index 37 doc 1 row 2'),
(38, 5, 1, 'text index 38 doc 5 row 1'),
(39, 5, 2, 'text index 39 doc 5 row 2'),
(42, 2, 3, 'text index 42 doc 2 row 3'),
(43, 2, 4, 'text index 43 doc 2 row 4'),
(46, 3, 1, 'text index 46 doc 3 row 1'),
(47, 3, 2, 'text index 47 doc 3 row 2');

By simple words I need few steps.
1) Add one new column 'kalk_br' of 'int' type. (I know to do that),
2) Copy contents of all cells 'kalk_id' to 'kalk_br'. (I don't know to do that),
3) Change column type for 'kalk_id' from 'int' to 'serial'. (I don't know to do that since 'kalk_id' is PRIMARY KEY).

EDIT by wildplasser's advice: 4) set the current value for the serial to MAX(kalk_id)

Is this possible to do and how? If not, please any idea to solve this differently.

EDIT:
This is I came so far:

/*1. rename kalk_id to kalk_br. 2. create a serial column kalk_id. 3. copy contents from kalk_br to kalk_id.*/

ALTER TABLE kalksad1 RENAME COLUMN kalk_id TO kalk_br;
ALTER TABLE kalksad1 ADD COLUMN kalk_id serial;
UPDATE kalksad1 SET kalk_id = kalk_br;

Now remains:
1) Terminate 'kalk_br' to be primary key.
2) Set 'kalk_id' which is now serial to be primary key with highest value updated as current value.

How about you switch it around? 1. rename kalk_id to kalk_br. 2. create a serial column kalk_id. 3. copy contents from kalk_br to kalk_id. — Wolph, Dec 15 '13 at 11:31
That sounds OK, Can you provide some code example to do that on showed table? — Wine Too, Dec 15 '13 at 11:36
+add: `4) set the current value for the serial to MAX(kalk_id)` — wildplasser, Dec 15 '13 at 11:37
@user973238: how about you rad the docs a bit and come back after *trying*? — Denis de Bernardy, Dec 15 '13 at 11:40
http://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres?rq=1 — Ashalynd, Dec 15 '13 at 11:42
@Wolph, I came to "COPY", but now I see a problem... 'kalk_id' should stay to be primary key but of the serial type. — Wine Too, Dec 15 '13 at 12:02
@Denis, Sucessfully passed phases of my tryings are marked as 'I know to do that'. — Wine Too, Dec 15 '13 at 12:12
You can change the primary key after the operations. As for copying the data, a simple `UPDATE kalksad1 SET kalk_id = kalk_br;` should do the trick — Wolph, Dec 15 '13 at 12:21
That worked, thanks. Now I have all except proper primary key which is now on 'kalk_br' and of type int. 'kalk_id' is now serial but it is not primary key. I updated situation on my post. How to finish that procedure? — Wine Too, Dec 15 '13 at 12:33

wildplasser · Accepted Answer · 2013-12-15T14:43:42.240

CREATE SEQUENCE kalksad1_kalk_id_seq;

ALTER TABLE kalksad1
        ALTER COLUMN kalk_id SET DEFAULT nextval('kalksad1_kalk_id_seq' )
        ;

ALTER SEQUENCE kalksad1_kalk_id_seq OWNED BY kalksad1.kalk_id;

WITH mx AS (SELECT max(kalk_id) AS mx FROM kalksad1)
SELECT setval( 'kalksad1_kalk_id_seq' , mx.mx)
FROM mx
        ;

   -- Test it ...
INSERT INTO kalksad1(brkalk, brred, description) VALUES (42, 666, 'Tralala' );
SELECT * FROM kalksad1;

Result:

NOTICE:  table "kalksad1" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 12
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval 
--------
     47
(1 row)

INSERT 0 1
 kalk_id | brkalk | brred |        description        
---------+--------+-------+---------------------------
      12 |      2 |     5 | text index 12 doc 2 row 5
      26 |      2 |     1 | text index 26 doc 2 row 1
      30 |      2 |     2 | text index 30 doc 2 row 2
      32 |      4 |     1 | text index 32 doc 4 row 1
      36 |      1 |     1 | text index 36 doc 1 row 1
      37 |      1 |     2 | text index 37 doc 1 row 2
      38 |      5 |     1 | text index 38 doc 5 row 1
      39 |      5 |     2 | text index 39 doc 5 row 2
      42 |      2 |     3 | text index 42 doc 2 row 3
      43 |      2 |     4 | text index 43 doc 2 row 4
      46 |      3 |     1 | text index 46 doc 3 row 1
      47 |      3 |     2 | text index 47 doc 3 row 2
      48 |     42 |   666 | Tralala
(13 rows)

Thank you wildplasser. According my few testings that seem's do a job exactly as expected! — Wine Too, Dec 15 '13 at 12:56
Of course it works. I tests it on real data which is huge and procedure pass with no porblems at all. Thanks for updating an answer. — Wine Too, Dec 15 '13 at 17:15

PostgreSQL, reconfigure existing table, changing primary key to type=serial

1 Answers1

Linked

Related